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

[Abhilash_B]

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.

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:

{
  "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.

[Andrei_Stefan]

@Abhilash_B this is an old known limitation with ES EQL. It is explained in this issue, 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.

[Abhilash_B]

Thanks for the update @Andrei_Stefan. Appreciate this find