# Querying Nested Fields with OR and IN clause

**URL:** <https://discuss.elastic.co/t/querying-nested-fields-with-or-and-in-clause/205483>\
**Category:** Elasticsearch\
**Created:** [October 28, 2019, 2:10pm UTC](https://discuss.elastic.co/t/querying-nested-fields-with-or-and-in-clause/205483 "2019-10-28T14:10:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Stella\_Sunshine](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stella_sunshine/32/55593_2.png) [@Stella\_Sunshine](https://discuss.elastic.co/u/Stella_Sunshine)\
**Post date:** [October 28, 2019, 2:10pm UTC](https://discuss.elastic.co/t/querying-nested-fields-with-or-and-in-clause/205483/1 "2019-10-28T14:10:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nishant.saini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishant.saini/32/41489_2.png) [@nishant.saini](https://discuss.elastic.co/u/nishant.saini)\
**Post date:** [October 29, 2019, 3:16am UTC](https://discuss.elastic.co/t/querying-nested-fields-with-or-and-in-clause/205483/2 "2019-10-29T03:16:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nishant.saini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishant.saini/32/41489_2.png) [@nishant.saini](https://discuss.elastic.co/u/nishant.saini)\
**Post date:** [October 29, 2019, 3:24am UTC](https://discuss.elastic.co/t/querying-nested-fields-with-or-and-in-clause/205483/3 "2019-10-29T03:24:27Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-exists-query.html#find-docs-null-values) 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](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html).

---

<div class="post-metadata">

**Author:** ![Stella\_Sunshine](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stella_sunshine/32/55593_2.png) [@Stella\_Sunshine](https://discuss.elastic.co/u/Stella_Sunshine)\
**Post date:** [October 29, 2019, 10:22am UTC](https://discuss.elastic.co/t/querying-nested-fields-with-or-and-in-clause/205483/4 "2019-10-29T10:22:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 26, 2019, 10:22am UTC](https://discuss.elastic.co/t/querying-nested-fields-with-or-and-in-clause/205483/5 "2019-11-26T10:22:22Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.