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.

Joe - GMapsBook.com · Accepted Answer · 2020-07-13T15:14:43.953

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 ] }
              }
            ]
          }
        }
      ]
    }
  }
}

In this solution, only the group/user rule set is a filter, though? How can I make both rule sets be a filter? — user984003, Jul 13 '20 at 14:43
The first `terms` query (`privacy:public`) is a filter itself. I'll edit the answer to explain. — Joe - GMapsBook.com, Jul 13 '20 at 14:51
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. — user984003, Jul 13 '20 at 15:09
Updated once again. The `filter` does need to be wrapped in a `bool`. — Joe - GMapsBook.com, Jul 13 '20 at 15:15

Elasticsearch: Filter on multiple fields with OR

1 Answers1