Elasticsearch Query Help - Multiple Nested AND/OR

Question

I am struggling with elasticsearch filters. I have a company_office type that looks like this:

{
  "company_office_id": 1,
  "is_headquarters": true,
  "company": {
    "name": "Some Company Inc"
  },
  "attribute_values": [
      {
        "attribute_id": 1,
        "attribute_value": "attribute 1 value",
      },
      {
        "attribute_id": 2,
        "attribute_value": "ABC",
      },
      {
        "attribute_id": 3,
        "attribute_value": "DEF",
      },
      {
        "attribute_id": 3,
        "attribute_value": "HIJ",
      }
  ]
}

Let's assume that attribute_value is not_analyzed - so I can match on it exactly.

Now I want to filter on a combination of multiple attribute_id and value fields. Something like this in SQL:

SELECT *
FROM CompanyOffice c
JOIN Attributes a --omitting the ON here, just assume the join is valid
WHERE 
c.is_headquarters = true AND
(
  (a.attribute_id=2 AND a.attribute_value IN ('ABC')) OR
  (a.attribute_id=3 AND a.attribute_value IN ('DEF','HIJ')) 
)

So I need to filter on specific fields + multiple combinations of id/value.

Here is the query I tried:

{
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : [


            { "term": {"is_headquarters": true } },
            {"bool": { 
                      "must":[
                        {"term": {"attribute_values.attribute_id": 1}},
                        {"bool": { "should": [{"term": {"attribute_values.attribute_value": "HIJ"}}]}}
                      ]
                     }}          
          ] 
        } 
      } 
    } 
  } 
}

This query is returning results even the company_office does not have any id/value pairing of 1/'HIJ'. My thinking here is that because this bool filter is sitting inside of the parent must section, then all items must be true:

            {"bool": { 
                      "must":[
                        {"term": {"attribute_values.attribute_id": 1}},
                        {"bool": { "should": [{"term": {"attribute_values.attribute_value": "HIJ"}}]}}
                      ]
                     }}

Why would this query return results given the data sample provided at the beginning of the question? Is there a different way to write the filter and accomplish what I am trying to do?

Thanks so much for any help!

TautrimasPajarskas · Accepted Answer · 2015-09-26 08:49:40Z

2

If you want to query deeper objects without flattening their structure, you need to set

"type": "nested"

on "attribute_values" property.

Then refer how to write nested queries in documentation, and you should correctly retrieve the whole document. Use inner hits to retrieve matched attribute_values.

By default, Elasticsearch does not nest properties when indexing. All subfields get's squashed into separate subfields without ability to query them by their actual structure. You will not see this effect, because original document is returned.

Apart from that, your queries are a bit off. In the last "should" statement, you have only 1 term filter so it's effectively a "must" part, but they will have to be rewritten to nested format.

edited Sep 26, 2015 at 8:49

answered Sep 26, 2015 at 8:22

TautrimasPajarskas

2,8061 gold badge35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MattW Over a year ago

Ok awesome. I can't try this for a few more days, once I do I will come back and comment on the answer. Thanks for the help!!

MattW Over a year ago

Added the nested mapping ended up doing the trick. Thanks again!

Collectives™ on Stack Overflow

Elasticsearch Query Help - Multiple Nested AND/OR

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related