# \[SQL\] Queries against nested datatypes may be mis-translated

**URL:** <https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180>\
**Category:** Elasticsearch\
**Created:** [August 20, 2018, 1:57pm UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180 "2018-08-20T13:57:27Z")\
**Posts on this page:** 9\
**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, 1:57pm UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/1 "2018-08-20T13:57:27Z")

</div>

Hi

Taking the example from [Nested datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) and adapting it to avoid using a reserved keyword (`group`), we have the following setup.

```
DELETE my_index

PUT my_index
{
  "mappings": {
    "_doc": {
      "properties": {
        "user": {
          "type": "nested" 
        }
      }
    }
  }
}

PUT my_index/_doc/1
{
  "groupName" : "fans",
  "user" : [
    {
      "first" : "John",
      "last" : "Smith"
    },
    {
      "first" : "Alice",
      "last" : "White"
    }
  ]
}

```

Issuing the following query returns no results, as expected.

```
GET my_index/_search
{
  "query": {
    "nested": {
      "path": "user",
      "query": {
        "bool": {
          "must": [
            { "match": { "user.first": "Alice" }},
            { "match": { "user.last": "Smith" }} 
          ]
        }
      }
    }
  }
}

```

However, issuing a syntactically very similar SQL query does return a result.

```
POST _xpack/sql?format=txt
{
  "query": "select groupName from my_index where user.first = 'Alice' and user.last = 'Smith'"
}

```

This happens because the above is `/translate`d to a query with outer type `bool` rather than `nested`. I'm having a hard time convincing myself that ES is doing the right thing here, and I'd argue that the intent of the above is mistranslated, and that the query above should try to find a nested doc matching both predicates.

Paul

---

<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, 5:37am UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/2 "2018-08-21T05:37:21Z")

</div>

Hi @paulcarey,

This is an interesting scenario, but I don't know if we can do any better in this case. Having a `bool` with two `nested` statements in it (like it is behaving now), or a root `nested` query that has a `bool` in it with two `term` statements, how could these be differentiated at SQL query level?  
No matter how you do it, the `WHERE` part has to have the form `user.first='Alice' AND user.last='Smith'`.

---

<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, 8:58am UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/3 "2018-08-21T08:58:03Z")

</div>

Agreed, and on reflection I think ES is doing the right thing here. I think there are three possible queries:

Retrieve documents where:

- for a given parent doc, any combination of nested docs matches all of the nested doc predicates
  - currently implemented using `and`
  - `select * from my_index where user.first = 'Alice' and user.last = 'Smith'`

- the parent doc matches any of the nested doc predicates
  - currently implemented using `or`
  - `select * from my_index where user.first = 'Alice' or user.last = 'bar'`

- for a given parent doc, a single nested doc matches all of the nested doc predicates
  - not currently supported
  - as a suggested syntax, it could potentially be supported with a correlated subquery
    - `select * from my_index where _id in (select _id from my_index.users where user.first = 'Alice' and user.last = 'Smith')`

  - alternatively, it could be supported with a function or syntax, but this likely wouldn't play nicely with aggregations
    - `select * from my_index where single_nested_doc(user.first = 'Alice' and user.last = 'Smith')`

Incidentally, in exploring this issue I've encountered an issue where changing the projection changes the results.

Original query

```
POST _xpack/sql?format=txt
{
  "query": "select groupName from my_index where user.first = 'Alice' or user.last = '???'"
}

```

Original result

```
 groupName   
 ---------------
 fans           

```

Modified query with additional field of `user.first`

```
  POST _xpack/sql?format=txt
  {
    "query": "select groupName, user.first from my_index where user.first = 'Alice' or user.last = '???'"
  }                  

```

Result of modified query

```
     groupName | user.first   
  ---------------+---------------

```

This is surely a bug as the same single doc should be returned in these results.

---

<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, 10:59am UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/4 "2018-08-21T10:59:06Z")

</div>

One more wrinkle / bug.

When selecting all fields, a row is output for each nested doc.

```
POST _xpack/sql?format=txt
{   
  "query": "select groupName, user.first, user.last from my_index"
}   

groupName | user.first | user.last   
---------------+---------------+---------------
fans |Alice |White              
fans |John |Smith              

```

But if we add a `where` clause that's satisifed by a combination of the nested docs, then only a single row for a single nested doc is returned.

```
POST _xpack/sql?format=txt
{   
  "query": "select groupName, user.first, user.last from my_index where user.first = 'Alice' and user.last = 'Smith'"
}   

groupName | user.first | user.last   
---------------+---------------+---------------
fans |John |Smith              

```

This doesn't seem right, particularly as one of the predicates which must have been satisfied referred to 'Alice'.

---

<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 22, 2018, 4:18am UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/5 "2018-08-22T04:18:27Z")

</div>

@paulcarey would you mind creating an issue in [github](https://github.com/elastic/elasticsearch/issues) for an enhancement regarding nested queries?  
This probably won't be on our top priority list, but it gives us an idea for future improvements that might be considered.

Thanks.

---

<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 23, 2018, 8:48am UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/6 "2018-08-23T08:48:34Z")

</div>

Sorry for the delay @paulcarey. I looked at these issues and the first one (with different projection the document disappears from the results) it's indeed a bug. The idea is that both `nested` queries that get created are for the same `path` and both use `inner_hits`. If one of the `inner_hits` doesn't return anything, the overall result is nothing, because they clash. It's a bit more complicated, but I hope now it's just a bit more clear.

The idea is to name each `inner_hits` statement so that the result can differentiate between them. And at the moment, ES-SQL doesn't do this.

And in the second scenario you mentioned it's basically the same root cause: clash between the two `inner_hits` and only one wins. I've created [https://github.com/elastic/elasticsearch/issues/33079](https://github.com/elastic/elasticsearch/issues/33079) and [https://github.com/elastic/elasticsearch/issues/33080](https://github.com/elastic/elasticsearch/issues/33080) to cover these issues.

Thank you for reporting them.

---

<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, 5:03am UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/7 "2018-08-24T05:03:29Z")

</div>

Great, many thanks for digging into these.

---

<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:26pm UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/8 "2018-08-28T13:26:35Z")

</div>

Regarding [33079](https://github.com/elastic/elasticsearch/issues/33079), I was wondering if [JsonPath](https://github.com/json-path/JsonPath) or some variant of it had been considered as a way to define complex queries? For example, the Alice White query I mentioned above could be satisfied with:

```
$.user[?(@.first == 'Alice' && @.last == 'White')]

```

This can be tested on [jsonpath.herokuapp.com](http://jsonpath.herokuapp.com/?path=%24.user%5B?(@.first%20==%20%27Alice%27%20%26%26%20@.last%20==%20%27White%27)%5D).

---

<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:26pm UTC](https://discuss.elastic.co/t/sql-queries-against-nested-datatypes-may-be-mis-translated/145180/9 "2018-09-25T13:26:39Z")

</div>

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