Checking if a field exists for any and/or all nested objects

Question

I took a look at ElasticSearch: search inside the array of objects and while it helps, I'm actually trying to determine if at least one has a field and if all nested objects have the field.

Pretending we have an index of all refrigerators with a superfluous document like:

{
  "_id": "whatever",
  "location": "North Building 1",
  "floor": 2,
  "tag": "refrigerator-1",
  "contents" : [
    {
      "item": "milk-carton",
      "expires": 1-1-2023 
    },
    {
      "item": "pyrex-container",
    }
  ]
}

How do I create an Elastic search query to;

Find any refrigerator that has at least 1 item that CAN expire ( "exists" : { "field" : "expires" } }
Find refrigerators that have no items that expire
Find refrigerators that where all items have an expire field

score 0 · Answer 1 · answered Nov 08 '22 at 09:28

If you want to do this in a single query , use named_queries

Query

{
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "_name": "At least one expires",
            "path": "contents",
            "query": {
              "exists": {
                "field": "contents.expires"
              }
            }
          }
        },
        {
          "bool": {
            "_name": "None expires",
            "must_not": [
              {
                "nested": {
                  "path": "contents",
                  "query": {
                    "exists": {
                      "field": "contents.expires"
                    }
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "_name": "All expires",
            "must": [
              {
                "nested": {
                  "path": "contents",
                  "query": {
                    "exists": {
                      "field": "contents.expires"
                    }
                  }
                }
              }
            ],
            "must_not": [
              {
                "nested": {
                  "path": "contents",
                  "query": {
                    "bool": {
                      "must_not": [
                        {
                          "exists": {
                            "field": "contents.expires"
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Result

"hits" : [
      {
        "_index" : "index70",
        "_type" : "_doc",
        "_id" : "Qt2PVoQB_m3FhzcGBasD",
        "_score" : 2.0,
        "_source" : {
          "location" : "North Building 1",
          "floor" : 3,
          "tag" : "refrigerator-3",
          "contents" : [
            {
              "item" : "milk-carton",
              "expires" : "2023-01-01"
            },
            {
              "item" : "pyrex-container",
              "expires" : "2023-01-01"
            }
          ]
        },
        "matched_queries" : [
          "At least one expires",
          "All expires"
        ]
      },
      {
        "_index" : "index70",
        "_type" : "_doc",
        "_id" : "QN2BVoQB_m3FhzcG9qsG",
        "_score" : 1.0,
        "_source" : {
          "location" : "North Building 1",
          "floor" : 2,
          "tag" : "refrigerator-1",
          "contents" : [
            {
              "item" : "milk-carton",
              "expires" : "2023-01-01"
            },
            {
              "item" : "pyrex-container"
            }
          ]
        },
        "matched_queries" : [
          "At least one expires"
        ]
      },
      {
        "_index" : "index70",
        "_type" : "_doc",
        "_id" : "Qd2HVoQB_m3FhzcGUauO",
        "_score" : 0.0,
        "_source" : {
          "location" : "North Building 1",
          "floor" : 3,
          "tag" : "refrigerator-2",
          "contents" : [
            {
              "item" : "milk-carton"
            },
            {
              "item" : "pyrex-container"
            }
          ]
        },
        "matched_queries" : [
          "None expires"
        ]
      }
    ]

Query is self explanatory. If you want use separate queries for three conditions, break above query. Each should clause will become a separate query

Checking if a field exists for any and/or all nested objects

1 Answers1