Lack of support for nested documents with more than one level of nesting

[zhongnansu]

For both the one level nested and two level nested query, the _explain API returns the same DSL.

• Mapping

{
  "employees_nested_2" : {
    "mappings" : {
      "properties" : {
        "comments" : {
          "type" : "nested",
          "properties" : {
            "date" : {
              "type" : "date"
            },
            "likes" : {
              "type" : "long"
            },
            "message" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        },
        "id" : {
          "type" : "long"
        },
        "name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "projects" : {
          "type" : "nested",
          "properties" : {
            "address" : {
              "type" : "nested",
              "properties" : {
                "city" : {
                  "type" : "text",
                  "fields" : {
                    "keyword" : {
                      "type" : "keyword",
                      "ignore_above" : 256
                    }
                  }
                },
                "state" : {
                  "type" : "text",
                  "fields" : {
                    "keyword" : {
                      "type" : "keyword",
                      "ignore_above" : 256
                    }
                  }
                }
              }
            },
            "name" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "started_year" : {
              "type" : "long"
            }
          }
        },
        "title" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }
}

• SQL query

POST _opendistro/_sql/?
{
"query" : "SELECT * FROM employees_nested_2 e, e.projects p"
}


POST _opendistro/_sql/?
{
"query" : "SELECT * FROM employees_nested_2 e, e.projects p, p.address as a"
}

• DSL

{
  "from": 0,
  "size": 200,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "must": [
              {
                "nested": {
                  "query": {
                    "match_all": {
                      "boost": 1
                    }
                  },
                  "path": "projects",
                  "ignore_unmapped": false,
                  "score_mode": "none",
                  "boost": 1,
                  "inner_hits": {
                    "ignore_unmapped": false,
                    "from": 0,
                    "size": 3,
                    "version": false,
                    "seq_no_primary_term": false,
                    "explain": false,
                    "track_scores": false,
                    "_source": {
                      "includes": [
                        "projects.*"
                      ],
                      "excludes": []
                    }
                  }
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [],
    "excludes": []
  }
}

• Results: (format=jdbc)

{
  "schema": [
    {
      "name": "title",
      "type": "text"
    },
    {
      "name": "projects.started_year",
      "type": "long"
    },
    {
      "name": "id",
      "type": "long"
    },
    {
      "name": "projects.name",
      "type": "text"
    },
    {
      "name": "name",
      "type": "text"
    }
        projects.address.city , state
  ],
  "total": 5,
  "datarows": [
    [
      "Software Eng 2",
      1998,
      6,
      "AWS Redshift security",
      "Jane Smith"
    ],
# omitting other results for brevity.
  ],
  "size": 5,
  "status": 200
}

From above projects.address.city and projects.address.state are missing.

Observation

• Currently, there is support for nested field (one-level nesting), but no support for deeply nested field(more than one-level nesting).
• Not only for this exact case SELECT *, for any other type of query, deeply nested field support is also missing.
• JDBC format output is wrong for query with deeply nested field.