Filter by field only if the field exists

I need to filter items by a certain field value, only if that field exists, otherwise it should include items which don't have that field value

Here's the example, a Todo app with assigned users and projects. I need to be able to get all items which are assigned to userA or userB and if any or the items are part of a project, only get the items from `projectX.

I'm using "must" to get items which are assigned to the users, this works as expected. But when I try to use "should" and "must" to get items where if projectId.keyword exists make sure that it equals projectX, but I also get items from other projects, i.e. projectY and projectZ, so the projectId.keyword constraint isn't working.

Here's my query.

{
  "query": {
    "bool": {
      "must": {
        "terms": {
          "assignedTo.keyword": [
            "userA",
            "userB"
          ]
        }
      },
      "should": [
        {
          "bool": {
            "must": [
              {
                "exists": {
                  "field": "projectId.keyword"
                }
              },
              {
                "term": {
                  "projectId.keyword": "projectX"
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "projectId.keyword"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Maybe there's a way to use must_not to make sure projectId.keyword doesn't equal something other than projectX?

1 Like

It looks good. What is the result you can see?

It could be great to provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible as you did so far.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

@all9lives

Within Elasticsearch query context if you are using should with must (clearly is your case), you should provide minimum_should_match parameter equal to 1 so that your should clause is evaluated as well.

https://www.elastic.co/guide/en/elasticsearch/reference/6.6/query-dsl-bool-query.html

Hope this helps out.

Thanks @mjunaidmuzammil "minimum_should_match": 1 worked for me.

Thanks for your help :smile: :pray:

1 Like

Great. Nice to know that it worked for you. :slightly_smiling_face:

Perhaps you can close the topic now.

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