Sql query on elastic 6.8 does not work as expected. Array of nested objects are flattened same as of type object

240 Views Asked by At

Thanks for the answer in advance.

I am running a query

SELECT key
FROM records_index
WHERE
  (product_nested_field.code = '1234' and product_nested_field.codeset = 'category1' OR product_nested_field.code = '444' and product_nested_field.codeset = 'category1')
  AND (role_name IN ('user', 'admin'))
GROUP BY records_uuid

In records_index I have record with two products

[
  {codeset: category1, code:444}, 
  {codeset: category2, code:1234}
]

The problem is that query does find a specified record. such behavior is expected for "type": "object" but why I am getting that result for product_nested_field of type nested?

when I translate SQL to JSON I am getting

        {
          "bool": {
            "must": [
              {
                "bool": {
                  "must": [
                    {
                      "nested": {
                        "query": {
                          "term": {
                            "product_nested_field.codeset": {
                              "value": "category1"
                            }
                          }
                        }
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "bool": {
                        "should": [
                          {
                            "nested": {
                              "query": {
                                "term": {
                                  "product_nested_field.code": {
                                    "value": "1234"
                                  }
                                }
                              }
                            }
                          },
                          {
                            "nested": {
                              "query": {
                                "term": {
                                  "product_nested_field.code": {
                                    "value": "444"
                                  }
                                }
                              }
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1.0
          }
        }

why elastic moves product_nested_field.codeset = 'category1' into separate nested query.

0

There are 0 best solutions below