ES Range Query on Non-Nested and Query Terms on Nested Object

Question

I would like to filter by both a non nested field and a field in a nested object/list. The non-nested field is a date range.

Sample data structure is below

{
    "order_id": "83",
    "order_date": "01/05/2018 17:53:39",
    "trans":
    [
        {
            "item_id" : "1",
            "amount": 5000.0,
        },
        {
            "item_id" : "2",
            "amount": 5000.0
        },
        {
            "item_id" : "3",
            "amount": 5000.0,                        
        }
    ]
}

To filter by the item_id, this works with no issue:

{
    "size": "0",
    "query": {
        "nested": {
            "path": "trans",
            "query": {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "trans.item_id": {
                                    "value": "3"
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}

To filter by order_date , the query below works with no issue

{
    "query": {
        "bool": {
            "filter": [
                {
                    "range": {
                        "order_date": {
                            "gte": "2020-04-15",
                            "lte": "2020-04-15"
                        }
                    }
                }
            ]
        }
    }
}

Any hints on how i can combine the 2?

Answer 1

You can combine these query using bool

Use [filter] if don't want query to affect score otherwise you con drop filter

  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "trans",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "term": {
                          "trans.item_id": {
                            "value": "3"
                          }
                        }
                      }
                    ]
                  }
                }
              }
            },
            {
              "range": {
                "order_date": {
                  "gte": "2020-04-15",
                  "lte": "2020-04-15"
                }
              }
            }
          ]
        }
      }
    }
  }