# \[SQL Translate API\] Wrong query generated when querying for nested fields

**URL:** <https://discuss.elastic.co/t/sql-translate-api-wrong-query-generated-when-querying-for-nested-fields/264083>\
**Category:** Elasticsearch\
**Created:** [February 12, 2021, 6:01am UTC](https://discuss.elastic.co/t/sql-translate-api-wrong-query-generated-when-querying-for-nested-fields/264083 "2021-02-12T06:01:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Abhilash\_B](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhilash_b/32/40270_2.png) [@Abhilash\_B](https://discuss.elastic.co/u/Abhilash_B)\
**Post date:** [February 12, 2021, 6:01am UTC](https://discuss.elastic.co/t/sql-translate-api-wrong-query-generated-when-querying-for-nested-fields/264083/1 "2021-02-12T06:01:16Z")

</div>

Hi folks,

I have an index with documents that are customer centric. Every document has customer ID, array of orders, within orders array we have an items array. Both orders and items fields are of type `nested`. There is an issue when I use the SQL translate API.

```auto
POST /_sql/translate
{
  "query": "Select count(*) FROM \"notusstagingaccount-customer\" where ((orders.items.product_attributes.DepartmentName = 'M Sptwr:Tops' AND orders.items.sale_price > 50) AND (orders.items.product_attributes.DepartmentName = 'M Sptwr:Tops' AND orders.items.quantity > 50))"
}

```

Resultant query:

```auto
{
  "size" : 0,
  "query" : {
    "bool" : {
      "must" : [
        {
          "bool" : {
            "must" : [
              {
                "nested" : {
                  "query" : {
                    "term" : {
                      "orders.items.product_attributes.DepartmentName" : {
                        "value" : "M Sptwr:Tops",
                        "boost" : 1.0
                      }
                    }
                  },
                  "path" : "orders.items",
                  "ignore_unmapped" : false,
                  "score_mode" : "none",
                  "boost" : 1.0
                }
              },
              {
                "nested" : {
                  "query" : {
                    "range" : {
                      "orders.items.sale_price" : {
                        "from" : 50,
                        "to" : null,
                        "include_lower" : false,
                        "include_upper" : false,
                        "boost" : 1.0
                      }
                    }
                  },
                  "path" : "orders.items",
                  "ignore_unmapped" : false,
                  "score_mode" : "none",
                  "boost" : 1.0
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        },
        {
          "bool" : {
            "must" : [
              {
                "nested" : {
                  "query" : {
                    "term" : {
                      "orders.items.product_attributes.DepartmentName" : {
                        "value" : "M Sptwr:Tops",
                        "boost" : 1.0
                      }
                    }
                  },
                  "path" : "orders.items",
                  "ignore_unmapped" : false,
                  "score_mode" : "none",
                  "boost" : 1.0
                }
              },
              {
                "nested" : {
                  "query" : {
                    "range" : {
                      "orders.items.quantity" : {
                        "from" : 50,
                        "to" : null,
                        "include_lower" : false,
                        "include_upper" : false,
                        "boost" : 1.0
                      }
                    }
                  },
                  "path" : "orders.items",
                  "ignore_unmapped" : false,
                  "score_mode" : "none",
                  "boost" : 1.0
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  },
  "_source" : false,
  "stored_fields" : "_none_",
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ],
  "track_total_hits" : 2147483647
}

```

The `nested` query appears within the boolean `must` clause where as the expection is multiple boolean must clauses within a `nested` query clause with `path` specified. Any help here is appreciated.

---

<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:** [February 15, 2021, 9:56am UTC](https://discuss.elastic.co/t/sql-translate-api-wrong-query-generated-when-querying-for-nested-fields/264083/2 "2021-02-15T09:56:39Z")

</div>

@Abhilash_B this is an old known limitation with ES EQL. It is explained in [this issue](https://github.com/elastic/elasticsearch/issues/33079), but I'll add a short explanation below, as well.

There are two ways a `nested` query can be written in ES (not ES EQL):

- a `nested` query with all the conditions inside it, pretty much the way you are expecting it from the query you provided as an example
- a `bool` query that has multiple, separate `nested` sub-queries in it, which is the way ES EQL works at the moment

ES EQL supports only one of these options and, as you can see, there is an issue in GH about the second option. At the moment, we have no near plans in adding this second option. If I remember well the discussions we had two years ago, there are endless combinations in which a query can be written with nested documents that could make the end result query dsl impossible to create. Maybe we'll reconsider this discussion and thing again about ways of supporting this second `nested` documents way of writing queries. The best for you is to monitor that GH issue for updates. Also, I don't think there is any workaround at this moment for the query you are looking for.

---

<div class="post-metadata">

**Author:** ![Abhilash\_B](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhilash_b/32/40270_2.png) [@Abhilash\_B](https://discuss.elastic.co/u/Abhilash_B)\
**Post date:** [February 16, 2021, 6:32am UTC](https://discuss.elastic.co/t/sql-translate-api-wrong-query-generated-when-querying-for-nested-fields/264083/3 "2021-02-16T06:32:24Z")

</div>

Thanks for the update @Andrei_Stefan. Appreciate this find

---

<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:** [March 16, 2021, 6:33am UTC](https://discuss.elastic.co/t/sql-translate-api-wrong-query-generated-when-querying-for-nested-fields/264083/4 "2021-03-16T06:33:17Z")

</div>

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