Elasticsearch: Filter on multiple fields with OR

Question

I have an index of documents. I want to filter for documents that are either public, or that are shared to the group by members of my group (users 1 and 3).

privacy = "public" OR (privacy = "group" AND user_id in (1,3))

I can do them separately, but how do I combine them with OR?

"filter" : [
            {"terms" : { "privacy" : ["public"]}},
        ]

"filter" : [
            {"terms" : { "privacy" : ["group"]}},
            {"terms" : { "user_id" : [1,3]}},
        ]

Documents:

{"id":1,"user_id":1, "privacy":"public","title":"Cooking for One",}
{"id":3,"user_id":1, "privacy":"group","title":"Three's Company"}
{"id":4,"user_id":2, "privacy":"public","title":"Four Ways to Diet"}
{"id":6,"user_id":2, "privacy":"group","title":"Six O'Clock News"}
{"id":7,"user_id":3, "privacy":"public","title":"Lucky Seven"}
{"id":9,"user_id":3, "privacy":"group","title":"Nine Animals to Draw"}

The right query will return documents 1,3,4,7,9, but not 6.

Jozef - Spatialized.io · Accepted Answer · 2020-07-13 15:14:43Z

1

The trick is to wrap both subqueries in a bool-should:

{
  "query": {
    "bool": {
      "should": [
        {
          "terms": { "privacy": [ "public" ] }
        },
        {
          "bool": {
            "filter": [
              {
                "terms": { "privacy": [ "group" ] }
              },
              {
                "terms": { "user_id": [ 1, 3 ] }
              }
            ]
          }
        }
      ]
    }
  }
}

FYI: note the difference between must and filter. TL;DR filter forgoes scoring.

EDIT:

{
  "terms":{
    "privacy":[
      "public"
    ]
  }
}

is roughly equivalent (except for the scoring part as discussed above) to

{
  "bool":{
    "filter":{
      "terms":{
        "privacy":[
          "public"
        ]
      }
    }
  }
}

which is fully equivalent to

{
  "bool":{
    "filter":[
      {
        "terms":{
          "privacy":[
            "public"
          ]
        }
      }
    ]
  }
}

It's just a matter of verbosity.

EDIT 2: the rewritten query including 2 filters

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "filter": [
              {
                "terms": { "privacy": [ "public" ] }
              }
            ]
          } 
        },
        {
          "bool": {
            "filter": [
              {
                "terms": { "privacy": [ "group" ] }
              },
              {
                "terms": { "user_id": [ 1, 3 ] }
              }
            ]
          }
        }
      ]
    }
  }
}

edited Jul 13, 2020 at 15:14

answered Jul 13, 2020 at 14:31

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user984003 Over a year ago

In this solution, only the group/user rule set is a filter, though? How can I make both rule sets be a filter?

Jozef - Spatialized.io Over a year ago

The first terms query (privacy:public) is a filter itself. I'll edit the answer to explain.

user984003 Over a year ago

Thanks for this. Do you mind instead showing the solution where both filter sets are actual filters? Would I have two "bool"s? Or just two "filter"s? I'm not looking to have them affect the match score. I also though that "should" didn't affect the match/filter, but only the score.

Jozef - Spatialized.io Over a year ago

Updated once again. The filter does need to be wrapped in a bool.

Collectives™ on Stack Overflow

Elasticsearch: Filter on multiple fields with OR

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related