CODEWITHSUNDEEP
elasticsearch
Simon Fredsted
Simon Fredsted

Reputation: 976

Combining missing and term query in nested document in Elasticsearch

I have these 3 documents, where fields is of type nested:

{
  "fields": [
    {"field_id": 23, "value": "John Doe"},
    {"field_id": 92, "value": null}
  ]
}

{
  "fields": [
    {"field_id": 23, "value": "Ada Lovelace"},
  ]
}

{
  "fields": [
    {"field_id": 23, "value": "Jack Daniels"},
    {"field_id": 92, "value": "[email protected]"}
  ]
}

I need to search for documents where:

(`field_id` = `92` AND `value` is `null`) OR (`field_id` `92` is missing.)

Combining a terms and missing query leads to only the document with the null value being returned:

...
"nested": {
  "path": "fields",
  "filter": {
    "bool": {
      "bool": {
        "must": [
          {
            "missing": {
              "field": "fields.value"
            }
          },
          {
            "terms": {
              "fields.field_id": [92]
            }
          }
        ]
      }
    }
  }
}
...

How can I do this?

Upvotes: 1

Views: 426

Answers (1)

Nishant
Nishant

Reputation: 7864

You already have query for one condition. Lets call this A. For second condition check for fields.field_id: 92 in nested documents. Lets say this is B. But your condition is fields.field_id: 92 should not exist. So to achieve this wrap B in must_not. i.e. B'

What is required is A OR B'

So the final query will be:

{
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "fields",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "fields.field_id": 92
                    }
                  }
                ],
                "must_not": [
                  {
                    "exists": {
                      "field": "fields.value"
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "nested": {
                  "path": "fields",
                  "query": {
                    "term": {
                      "fields.field_id": 92
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Upvotes: 1

Related Questions