SQL array handling is inconsistent

[paulcarey]

The following applies to Elasticsearch 6.3.

Selecting array fields is not handled consistently and should be revised.

If a document contains an array of primitive (e.g. string, number), an attempt to project that field will fail with an error like Arrays (returned by [values]) are not supported. This behaviour is unnecessarily restrictive. There may be a misconception over SQL or JDBC array support being non-standard.

Wikipedia states that SQL support for arrays was standardized in 1999. JDBC support for Array has existed since JDK 1.2 (released 1998).

For reference, here's an example of using Arrays of Structs with JDBC and PostgreSQL. The Java tutorial has an example showing JDBC usage of Array of String.

A workaround for the array limitation in ES-SQL exists by wrapping the primitive values in an object containing just that value. When this is done then a select can be used to 'explode' each element in the array into its own row. While useful, this should arguably not happen by default.

This exploding behaviour is also supported in Hive, Spark SQL and some other data stores where it must be explicitly triggered by invoking lateral view explode.

If ES-SQL supported arrays, then:

• the workaround to wrap primitives in objects would not be needed
• clients could choose via lateral view explode or similar syntax whether to receive a single row, or a row per array element

The current situation feels a bit ill-defined.

Showing the current limitation

DELETE array_test_2

POST array_test_2/_doc
{
  "name": "foo",
  "values": [1, 2, 3]
}


POST _xpack/sql?format=txt
{
  "query": "select name, values from array_test_2"
}


{
  "error": {
    "root_cause": [
      {
        "type": "sql_illegal_argument_exception",
        "reason": "Arrays (returned by [values]) are not supported"
      }
    ],
    "type": "sql_illegal_argument_exception",
    "reason": "Arrays (returned by [values]) are not supported"
  },
  "status": 500
}

Workaround for the array limitation by wrapping primitives in an object

DELETE array_test_1

PUT array_test_1
{
  "mappings": {
    "_doc": {
      "properties": {
        "name": {
          "type": "text"
        },
        "values": {
          "type": "nested",
          "properties": {
            "v": {
              "type": "long"
            }
          }
        }
      }
    }
  }
}

POST array_test_1/_doc
{
  "name": "foo",
  "values": [{"v": 1}, {"v": 2}, {"v": 3}]
}

POST _xpack/sql?format=txt
{
  "query": "select name, values.v as v from array_test_1"
}


     name      |       v       
---------------+---------------
foo            |3              
foo            |2              
foo            |1              

[elasticmachine]

Pinging @elastic/es-search-aggs

[costin]

Thanks for the detailed (and formatted) issue - it's really appreciated.

The root issue with arrays comes from ES - the mapping doesn't indicate whether a field is an array (has multiple values) or not so without reading all the data, ES-SQL cannot know whether a field is a single or multi value.

As you pointed, one way to handle that is through explicit statements though each (including LATERAL VIEW EXPLORE) comes with its own semantics that require some thinking through.

Further more, arrays and struct have their own set of problems; I'm aware of the standard and JDBC support however de-facto, arrays are not widely available (see mysql and msserver) and not all consumers (a large part as far as I could tell) support them.
The fact that it's a composite value breaks the flat table structure and challenges it - are the arrays enforced to have the same type, length? Are offsets supported (for display or querying)?
One could argue that without introspection of an array, one can just treat it as a blob.

Getting the semantics right (and the implications of supporting such a type) requires a significant amount of time so it will take a while for us to get to it and sort it out.

[paulcarey]

Many thanks for the throughtful response.

While exploring ES-SQL support for nested documents, I came across a few other issues:

• inability to express queries where multiple predicates should apply to the same nested doc - es-discuss
• inconsistency betweeen selections and projections - es-discuss

I think the dot notation used to query and project nested docs, while elegant, is simply not expressive enough. I'm sure there are a number of ways of addressing this. For example, https://github.com/json-path/JsonPath would allow multiple predicates to be applied to the same nested doc.

But given that an implicit mapping exists from a given document to its relational equivalent, perhaps the most predictable approach to supporting arbitrarily complex SQL is to require that such queries are expressed using 'normalized' queries.

For example, this document (that I referred to in this post)

{
  "groupName" : "fans",
  "user" : [
    {
      "first" : "John",
      "last" :  "Smith"
    },
    {
      "first" : "Alice",
      "last" :  "White"
    }
  ]
}

has the following implicit schema

create table groups (
  group_id integer primary key,
  group_name varchar(20)
);

create table users (
  user_id integer primary key,
  group_id integer,
  first_name varchar(20),
  last_name varchar(20)
);

A query allowing multiple predicates to be expressed against a single nested doc would be trivially expressed as follows

select g.group_name from groups g
natural join users u
where u.first_name = 'alice'
and u.last_name = 'white';

This would be translated into the following Query DSL.

{                                                                                                                                                                                                                                        
  "query": {
    "nested": {
      "path": "user",
      "query": {
        "bool": {
          "must": [
            { "match": { "user.first": "alice" }},
            { "match": { "user.last":  "white" }}
          ]
        }
      }
    }
  }
}

There are multiple advantages to this approach:

• It uses standard SQL
  • it's well understand and should throw up no surprises
  • it doesn't require inventing any new syntax, or adopting anything other than regular SQL
• It addresses the concerns you raised above regarding arrays, structs and client support

What's notably missing from this approach relative to ES-SQL today is a way to specify the index against which the query should run, currently supported with from <tablename>. But this could be easily resolved by specifying the target database in the JDBC connection string, or as an extra param alongside query when submitting SQL queries over HTTP.

I'll add a follow-on comment with a few more examples that appear tricky to express with ES-SQL, but where the translation from 'normalized' SQL to Query DSL seems fairly mechanical.

[paulcarey]

Sample queries referred to in the comment above.

DELETE countries

PUT countries
{
  "mappings": {
    "_doc": {
      "properties": {
        "cities": {
          "type": "nested",
          "properties": {
            "districts": {
              "type": "nested",
              "properties": {
                "name": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "schools": {
                  "type": "nested",
                  "properties": {
                    "name": {
                      "type": "text",
                      "fields": {
                        "keyword": {
                          "type": "keyword",
                          "ignore_above": 256
                        }
                      }
                    },
                    "students": {
                      "type": "long"
                    }
                  }
                }
              }
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      }
    }
  }
}

POST countries/_doc
{
  "name": "ireland",
    "cities": [
    {
      "name": "dublin",
      "districts": [
        {
          "name": "dun laoghaire",
          "schools": [
            {
              "name": "tao nan school",
              "students": 101
            },
            {
              "name": "colaiste eoin",
              "students": 202
            }
          ]
        },
        {
          "name": "howth",
          "schools": [
            {
              "name": "colaiste eoin",
              "students": 30
            }
          ]
        }
      ]
    },
    {
      "name": "cork",
      "districts": [
        {
          "name": "ballincollig",
          "schools": [
            {
              "name": "scoil san treasa",
              "students": 50
            }
          ]
        }
      ]
    }
  ]
}

# select d.districtName from districts d
# natural join schools s
# where s.schoolName = 'colaiste eoin'
# and s.students = 202;

# No way to require that the predicates refer to the same school
POST _xpack/sql?format=txt
{
  "query": "select countries.cities.districts.name from countries where countries.cities.districts.schools.name = 'colaiste eoin' and countries.cities.districts.schools.students = 202"
}


GET countries/_search
{
  "query": {
    "nested": {
      "path": "cities.districts",
      "query": {
        "nested": {
          "path": "cities.districts.schools",
          "query": {
            "bool": {
              "must": [
                {"match" : {"cities.districts.schools.name": "colaiste eoin"}},
                {"match" : {"cities.districts.schools.students": 202}}        
              ]
            }
          },
          "inner_hits": {}
        }
      },
      "inner_hits": {}
    }
  }
}


# select d.districtName, s.students from districts d
# natural join schools s
# where s.schoolName = 'colaiste eoin'
# and d.districtName = 'howth';

# Throws an NPE (possibly fixed in https://github.com/elastic/elasticsearch/issues/33170)
POST _xpack/sql?format=txt
{
    "query": "select countries.cities.districts.name, countries.cities.districts.schools.students from countries where countries.cities.districts.schools.name = 'colaiste eoin' and countries.cities.districts.name = 'howth'"
}



GET countries/_search
{
  "query": {
    "nested": {
      "path": "cities.districts",
      "query": {
        "bool": {
          "must": [
            {"match_phrase" : {"cities.districts.name": "howth"}},
            {
              "nested": {
                "path": "cities.districts.schools",
                "query": {
                  "match": {
                    "cities.districts.schools.name": "colaiste eoin"
                  }
                },
                "inner_hits": {}
              }
            }
          ]
        }
      },
      "inner_hits": {}
    }
  }
}


# select c.cityName from cities c
# natural join districts d
# natural join schools s
# where s.schoolName = 'colaiste eoin'
# and s.students = 30;

# Returns cork and dublin rather than just dublin, but possibly this is due to a bug in the way that inner_hits is generated
POST _xpack/sql?format=txt
{
    "query": "select countries.cities.name from countries where countries.cities.districts.schools.name = 'colaiste eoin'"
}


GET countries/_search
{
  "query": {
    "nested": {
      "path": "cities",
      "query": {
        "nested": {
          "path": "cities.districts",
          "query": {
            "nested": {
              "path": "cities.districts.schools",
              "query": {
                "bool": {
                  "must": [
                    {
                      "match": 
                        {"cities.districts.schools.name": "colaiste eoin"}
                    },
                    {
                      "match": 
                        {"cities.districts.schools.students": 30}
                    }
                  ]
                }
              },
              "inner_hits": {}
            }
          },
          "inner_hits": {}
        }
      },
      "inner_hits": {}
    }
  }
}

Incidentally, I've tested all the SQL above using PostgreSQL 9.6 on http://sqlfiddle.com/ using the following setup.

create table countries (
  country_id integer primary key,
  countryName varchar(20)
);

create table cities (
  city_id integer primary key,
  country_id integer,
  cityName varchar(20)
);

create table districts (
  district_id integer primary key,
  city_id integer,
  districtName varchar(20)
);

create table schools (
  school_id integer primary key,
  district_id integer,
  schoolName varchar(20),
  students integer
);

insert into countries values ( 1, 'ireland');
insert into cities    values ( 1, 1, 'dublin');
insert into cities    values ( 2, 2, 'cork');
insert into districts values ( 1, 1, 'dun laoghaire');
insert into districts values ( 2, 1, 'howth');
insert into districts values ( 3, 2, 'ballincollig');
insert into schools   values ( 1, 1, 'tao nan school', 101);
insert into schools   values ( 2, 1, 'colaiste eoin', 202);
insert into schools   values ( 3, 3, 'scoil san treasa', 50);
insert into schools   values ( 4, 2, 'colaiste eoin', 30);

[costin]

@paulcarey, thank you for providing valuable input and being generous with your ideas - it is appreciated!

I agree with you that mapping nested docs (or parent/join) relationship, which is a 1:N, as two separate tables brings a number of benefits since the association between parent/nested becomes obvious and a lot of concepts fall into place.
The concern with this approach though, is that only a small subset of JOIN functionality is used and just on a particular set of data, which would be misleading and unexpected to a large number of users.
It might be also interpreted as if nested/children docs exist by themselves (are a separate table) when in fact they aren't.
This is further accentuated by the fact that ES currently lacks the ability to easily paginate or return nested docs (inner_hits for example requires one to specify how many docs to return instead of allowing all docs (which vary in size across docs) to be returned). This complicates the implementation and in some cases, what SQL can effectively do.

Unfortunately at this stage, due to lack of time mainly, I don't have a clear answer on how we'll move forward with nested docs. Note this is a separate issue from arrays - it does conceptually stems from the same abstraction (mapping one-to-many relationships) however the data type/container is significantly different.

Back to arrays, a simple solution for them is introducing either a FLATTEN or ARRAY function - the former as a way to unwind an array (need to sort out the table structure), the latter to return a field as an array without changing the table structure (as in number of columns/rows).

[paulcarey]

Apologies for taking the discussion a little off-topic.

I take your point about not wanting to surprise users. Representing nested object as fully-fledged tables would indeed be misleading, but perhaps this could be mitigated with syntax e.g. by fully qualifying table names (select ... from countries.cities.districts d) while still allowing natural joins against such tables.

I'm new to ES so don't really have a view on the difficulty of effectively retrieving nested docs via inner_hits, or of supporting this via the search & scroll API.

Anyway, I think I'm done with suggestions 😄 But I will watch the development of ES-SQL with interest as I'll almost certainly be adopting this in my company. Our users will need to query on nested documents, and in the near time I think this will be done with a visual query builder. I note that Kibana doesn't support such query building for nested documents, (possibly because it's difficult in the general case) but I think we have enough information about our document structure to make this feasible.

The proposal for FLATTEN and ARRAY functions sounds sensible. Many thanks.

[paulcarey]

Hi, I was wondering if there's been any progress on this issue? Thanks.