ElasticSearch: How to filter an array of objects with multiple filters?

Question

{
  "id": 9,
  "resolutions": [
    {
      "divisionId": 8
    }
  ]
},
{
  "id": 123,
  "resolutions": [
    {
      "employeeId": 1,
      "divisionId": 5
    },
    {
      "divisionId": 7
    }
  ]
}

The index consists of document objects, and each document has an array resolutions with objects. A resolution can be given either to an employee, or a division. Employee will have both divisionId and employeeId, but division will only have divisionId. I need to filter for divisions only.

{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "should": [
                    {
                      "bool": {
                        "must": [
                          {
                            "nested": {
                              "path": "resolutions",
                              "query": {
                                "terms": {
                                  "resolutions.divisionId": [
                                    660
                                  ]
                                }
                              }
                            }
                          }
                        ],
                        "boost": 1
                      }
                    },
                    {
                      "bool": {
                        "must_not": [
                          {
                            "nested": {
                              "path": "resolutions",
                              "query": {
                                "exists": {
                                  "field": "resolutions.employeeId"
                                }
                              }
                            }
                          }
                        ],
                        "boost": 1
                      }
                    }
                  ],
                  "minimum_should_match": 2,
                  "boost": 1
                }
              }
            ],
            "boost": 1
          }
        }
      ],
      "boost": 1
    }
  }
}

The problem is that this query checks all objects of the resolution array. So if only one division is added to the array, I get the result back, but if I also add an employee, then I do not get it back.

How to fix this to return the result if at least one division exists in the array, regardless of what the other objects are?

jaspreet chahal · Accepted Answer · 2022-10-15 05:19:20Z

1

Your query was quite complicated.

Below will answer ÿour query

If at least one division exists in the array, regardless of what the other objects are?

Query

{
  "query": {
    "nested": {
      "path": "resolutions",
      "query": {
        "bool": {
          "must_not": [
            {
              "exists": {
                "field": "resolutions.employeeId"
              }
            }
          ],
          "filter": [
            {
              "exists": {
                "field": "resolutions.divisionId"
              }
            }
          ]
        }
      }
    }
  }
}

You can replace exists condition in filter with terms query if you want to filter on some values.

Use inner_hits if you want to find matching nested documents.

answered Oct 15, 2022 at 5:19

jaspreet chahal

9,1392 gold badges14 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

ElasticSearch: How to filter an array of objects with multiple filters?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related