Can support nested type?

[penghuiy]

PUT /obj
{
  "mappings": {
    "_doc": {
      "properties": {
        "name": {
          "type": "text"
        },
        "car": {
          "type": "nested",
          "properties": {
            "make": {
              "type": "text"
            }
          }
        }
      }
    }
  },
  "settings":{
      "index":{
          "number_of_shards":1,
          "number_of_replicas":1
      }
  }
}

PUT /obj/_doc/1
{
  "name" : "Zach",
  "car" : [
    {
      "make" : "Saturn",
      "model" : "SL"
    },
    {
      "make" : "Subaru",
      "model" : "Imprezza"
    }
  ]
}

PUT /obj/_doc/2
{
  "name" : "Rach",
  "car" : [
    {
      "make" : "Naturn",
      "model" : "NL"
    },
    {
      "make" : "Wubaru",
      "model" : "Laprezza"
    },
    {
      "make" : "Siri",
      "model" : "Paprezza"
    }
  ]
}

I want to get the all elements of array. like

select  * from obj.car

[dai-chen]

Thanks for reporting the issue. I'm looking into this.

[dai-chen]

In your example, the query and result is expected to be like:

POST _opendistro/_sql?format=jdbc
{
  "query": "SELECT * FROM obj o, o.car m"
}
{
  "schema": [
    {
      "name": "car.model",
      "type": "text"
    },
    {
      "name": "name",
      "type": "text"
    },
    {
      "name": "car.make",
      "type": "text"
    }
  ],
  "total": 5,
  "datarows": [
    [
      "SL",
      "Zach",
      "Saturn"
    ],
    [
      "Imprezza",
      "Zach",
      "Subaru"
    ],
    [
      "NL",
      "Rach",
      "Naturn"
    ],
    [
      "Laprezza",
      "Rach",
      "Wubaru"
    ],
    [
      "Paprezza",
      "Rach",
      "Siri"
    ]
  ],
  "size": 5,
  "status": 200
}

[penghuiy]

Ok, it works fine. Thank you. And I expect a simpler query like my example , because we want to privide it to thirdparty

[dai-chen]

I'm fixing this in PR: https://github.com/opendistro-for-elasticsearch/sql/pull/98. This kind of nested field query works in most cases. But the meaning of SELECT * was not clear. Will merge to master after approved.

Btw, we'd like to follow the syntax in my example because it is standard. Please refer to https://ci.apache.org/projects/asterixdb/sqlpp/manual.html#Unnest_clauses. Comma join here is equivalent to unnest nested field.

[dai-chen]

https://github.com/opendistro-for-elasticsearch/sql/pull/98#issuecomment-510974475

@penghuiy I'm ready to merge. Please reference to the test query in the comment above. Thanks!