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]

sql/core/src/main/java/org/opensearch/sql/analysis/ExpressionAnalyzer.java

Lines 322 to 333 in b244f2e

	// Fall back to old engine too if type is not supported semantically
	if (isTypeNotSupported(ref.type())) {
	throw new SyntaxCheckException(String.format(
	"Identifier [%s] of type [%s] is not supported yet", ident, ref.type()));
	}
	return ref;
	}
	// Array type is not supporte yet.
	private boolean isTypeNotSupported(ExprType type) {
	return "array".equalsIgnoreCase(type.typeName());
	}