# \[SQL\] Return type constraints are overly restrictive

**URL:** <https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185>\
**Category:** Elasticsearch\
**Created:** [August 20, 2018, 2:12pm UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185 "2018-08-20T14:12:50Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![paulcarey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulcarey/32/34624_2.png) [@paulcarey](https://discuss.elastic.co/u/paulcarey)\
**Post date:** [August 20, 2018, 2:12pm UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/1 "2018-08-20T14:12:51Z")

</div>

Hi

Executing the following

```
DELETE nested_numbers
POST /nested_numbers/_doc
{"numbers": [{"n": 1}, {"n": 2}]}
POST /_xpack/sql?format=json
{
  "query": "select * from nested_numbers"
}

```

will fail with an `sql_illegal_argument_exception` stating `"reason": "Arrays (returned by [numbers.n]) are not supported"`. But I'm not sure there's a good reason for this restriction (note that the format above is json, not txt).

If I take the result of the `sql` request, pass it to `/translate` and then `_search` I get the expected results.

```
GET nested_numbers/_search
# The GET body below is the response from
#
# POST /_xpack/sql/translate
# {
# "query": "select * from nested_numbers"
# }
{
  "size": 1000,
  "_source": false,
  "stored_fields": "_none_",
  "docvalue_fields": [
    "numbers.n"
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

```

The only good reason I can think of for this restriction is that it makes JDBC connectivity awkward, but the JDBC driver for PostgreSQL works ok with JSON. Would it be possible to allow SQL queries to return arrays and nested types?

Finally, the error message associated with this condition is type-dependent. The following errors with `# "reason": "Cannot extract value [numbers.n] from source"`.

```
DELETE nested_strings
POST /nested_strings/_doc
{"numbers": [{"n": "one"}, {"n": "two"}]}
POST /_xpack/sql?format=json
{
  "query": "select * from nested_strings"
}

```

Paul

---

<div class="post-metadata">

**Author:** ![paulcarey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulcarey/32/34624_2.png) [@paulcarey](https://discuss.elastic.co/u/paulcarey)\
**Post date:** [August 20, 2018, 11:44pm UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/2 "2018-08-20T23:44:28Z")

</div>

I've realised that as this restriction doesn't apply when using nested docs, this isn't actually an issue for me. However, some docs describing these error messages would be very helpful.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [August 21, 2018, 4:20am UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/3 "2018-08-21T04:20:08Z")

</div>

Hi @paulcarey,  
Glad to see you took ES-SQL for a spin.

The reason for not supporting arrays is, in principle, related to SQL way of dealing with values: rows and columns where each element in the matrix is a single value. When you have an array of values for a certain row and column, which one do you want to return? The first, third, fifth etc? Also, this being JSON (as the native format in which `_source` is stored in Elasticsearch - and JSON is by definition unordered) how do you define "first", "third" etc.

Also, SQL doesn't have the notion of arrays of values.

---

<div class="post-metadata">

**Author:** ![paulcarey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulcarey/32/34624_2.png) [@paulcarey](https://discuss.elastic.co/u/paulcarey)\
**Post date:** [August 21, 2018, 12:29pm UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/4 "2018-08-21T12:29:47Z")

</div>

Thanks for the response, but stating that SQL doesn't have the notion of arrays of values isn't really correct.

Obviously 'SQL' can be a bit ambiguous, depending on versions of specs etc., but here's an example of using Arrays of Structs with JDBC and PostgreSQL.

[https://www.enterprisedb.com/blog/using-java-manipulate-sql-structures-and-arrays](https://www.enterprisedb.com/blog/using-java-manipulate-sql-structures-and-arrays)

The Java tutorial has an example showing JDBC usage of Array of String

[https://docs.oracle.com/javase/tutorial/jdbc/basics/array.html](https://docs.oracle.com/javase/tutorial/jdbc/basics/array.html)

[SQL 2016](https://en.wikipedia.org/wiki/SQL:2016) formally defines JSON support and these Microsoft [docs](https://cloudblogs.microsoft.com/sqlserver/2016/01/05/json-in-sql-server-2016-part-1-of-4/) describe usage in SQL Server.

---

<div class="post-metadata">

**Author:** ![paulcarey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulcarey/32/34624_2.png) [@paulcarey](https://discuss.elastic.co/u/paulcarey)\
**Post date:** [August 24, 2018, 8:04am UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/5 "2018-08-24T08:04:58Z")

</div>

I just wanted to point out that wrapping primitive values in objects can serve as a workaround.

```auto
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    

```

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [August 27, 2018, 2:50pm UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/6 "2018-08-27T14:50:24Z")

</div>

Would you, please, create an issue for this, with this suggestion? Thanks.

---

<div class="post-metadata">

**Author:** ![paulcarey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paulcarey/32/34624_2.png) [@paulcarey](https://discuss.elastic.co/u/paulcarey)\
**Post date:** [August 28, 2018, 1:16pm UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/7 "2018-08-28T13:16:10Z")

</div>

Sure, I've created [https://github.com/elastic/elasticsearch/issues/33204](https://github.com/elastic/elasticsearch/issues/33204)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 25, 2018, 1:16pm UTC](https://discuss.elastic.co/t/sql-return-type-constraints-are-overly-restrictive/145185/8 "2018-09-25T13:16:10Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.