Elasticsearch combine term and range query on nested key/value data

Question

I have ES documents structured in a flat data structure using the nested data type, as they accept arbitrary JSON that we don't control, and we need to avoid a mapping explosion. Here's an example document:

{
   "doc_flat":[
      {
         "key":"timestamp",
         "type":"date",
         "key_type":"timestamp.date",
         "value_date":[
           "2023-01-20T12:00:00Z"
         ]
      },
      {
         "key":"status",
         "type":"string",
         "key_type":"status.string",
         "value_string":[
            "warning"
         ]
      },
      ... more arbitrary fields ...
   ],
}

I've figured out how to query this nested data set to find matches on this arbitrary nested data, using a query such as:

{
    "query": {
        "nested": {
            "path": "doc_flat",
            "query": {
                "bool": {
                    "must": [
                        {"term": {"doc_flat.key": "status"}},
                        {"term": {"doc_flat.value_string": "warning"}}
                    ]
                }
            }
        }
    }
}

And I figured out how to find documents matching a particular date range:

{
    "query": {
        "nested": {
            "path": "doc_flat",
            "query": {
                "bool": {
                    "must": [
                        {"term": {"doc_flat.key": "timestamp"}},
                        {
                          "range": {
                            "doc_flat.value_date": {
                              "gte": "2023-01-20T00:00:00Z",
                              "lte": "2023-01-21T00:00:00Z"
                            }
                          }
                        }
                    ]
                }
            }
        }
    }
}

But I'm struggling to combine these two queries together, in order to search for documents that have a nested documents which match these two conditions:

• a doc_flat.key of status, and a doc_flat.value_string of warning
• a doc_flat.key of timestamp, and a doc_flat.value_date in a range

Obviously I can't just shove the second set of query filters into the same must array, because then no documents will match. I think I need to go "one level higher" in my query and wrap it in another bool query? But I can't get my head around how that would look.

Answer 1

You tried two nested inside Bool query?

{
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "doc_flat",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "doc_flat.key": "timestamp"
                    }
                  },
                  {
                    "range": {
                      "doc_flat.value_date": {
                        "gte": "2023-01-20T00:00:00Z",
                        "lte": "2023-01-21T00:00:00Z"
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ],
      "must": [
        {
          "nested": {
            "path": "doc_flat",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "doc_flat.key": "status"
                    }
                  },
                  {
                    "term": {
                      "doc_flat.value_string": "warning"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}