Better document limitation on Array support.

[penghuo]

Is your feature request related to a problem? Please describe. Document current limitation on Array support. https://github.com/opensearch-project/sql/blob/main/opensearch/src/main/java/org/opensearch/sql/opensearch/data/utils/OpenSearchJsonContent.java#L132

[penghuo]

Similar issue. #235

[ps48]

@penghuo Adding one example for documentation. SQL plugin doesn't support arrays and array of objects. It returns just the first element for the both types.

Indexed Doc

PUT my-index-000001/_doc/1
{
  "message": "some arrays in this document...",
  "tags":  [ "opensearch", "wow" ], 
  "lists": [ 
    {
      "name": "prog_list",
      "description": "programming list"
    },
    {
      "name": "cool_list",
      "description": "cool stuff list"
    }
  ]
}

SQL Query, Response

select * from my-index-000001

{
  "schema": [
    {
      "name": "message",
      "type": "text"
    },
    {
      "name": "lists",
      "type": "object"
    },
    {
      "name": "tags",
      "type": "text"
    }
  ],
  "datarows": [
    [
      "some arrays in this document...",
      {
        "name": "prog_list",
        "description": "programming list"
      },
      "opensearch"
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

[dai-chen]

Just for reference: Presto/Trino supports array type by asking user to specific it in index mapping: https://trino.io/docs/current/connector/elasticsearch.html#array-types

[Yury-Fridlyand]

https://github.com/opensearch-project/sql/blob/b244f2e477476567ae70c139550abaa62316a251/core/src/main/java/org/opensearch/sql/analysis/ExpressionAnalyzer.java#L322-L333