Querying Nested Fields with OR and IN clause

[Stella_Sunshine (Sara)]

This is a snippet of my ES document:

        "actions": [
            {
              "action_type": "special_offer",
              "startdate": "2019-09-10 00:00:00",
              "enddate": "2019-12-19 23:59:59"
            },
           {
              "action_type": "clean_up",
              "startdate": null,
              "enddate": null
            }
          ]

I would like to have all products with an action_type IN ('special_offer', 'clean_up', 'sell_off') and where the enddate is greater than now() OR the enddate is null.

This is how my query looks like now:

"query": {
    "bool": {
      "must": [

        {
          "nested" : {
              "path": "article.actions",
              "query" :{
                  "bool" : {
                      "should" : [
                        { "match" : {"article.actions.action_type" : "sell_off"} },
                        { "match" : {"article.actions.action_type" : "special_offer"} },
                        { "match" : {"article.actions.action_type" : "clean_up"} }
                      ],
                      "must": [
                        {
                          "range": {
                            "article.actions.enddate": {
                              "gte": "2019-10-24 00:00:00"
                            }
                          }
                        }
                      ],
                      "minimum_should_match" : 1
                  }
              }
          }
        }
      ]
    }
}

I do not know how to add the OR condition (enddate is null).

Would be happy if someone can help me.

[nishant.saini]

Is the data type of field action_type is keyword or does it have a subfield of type keyword?

[nishant.saini]

Update your query as below:

{
  "query": {
    "nested": {
      "path": "article.actions",
      "query": {
        "bool": {
          "must": [
            {
              "terms": {
                "article.actions.action_type.keyword": [
                  "sell_off",
                  "special_offer",
                  "clean_up"
                ]
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "range": {
                      "article.actions.enddate": {
                        "gte": "now/d"
                      }
                    }
                  },
                  {
                    "bool": {
                      "must_not": [
                        {
                          "exists": {
                            "field": "article.actions.enddate"
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

For null check you have to use exists query and place it along with the range query inside a should block.

Also I have assumed that field action_type has a subfield of type keyword. This will help you build the IN type of query using terms query.

[Stella_Sunshine (Sara)]

Thank you so much for your solution. It works.

The datatype of action_type is keyword.

That's the mapping:

 "actions": {
          "type": "nested",
          "properties": {
            "action_type": {
              "type": "keyword"
            },
            "startdate": {
              "format": "yyyy-MM-dd HH:mm:ss",
              "type": "date"
            },
            "enddate": {
              "format": "yyyy-MM-dd HH:mm:ss",
              "type": "date"
            }
          }
        }

I had to remove the word keyword from the query. Otherwise it does not work.