Elastic match documents containing all values in array

Question

I have documents like the ones bellow in Elastic. Each document has some information and all mandatory permissions to access the document.

When I query, I would like to pass all user permissions and receive matched documents but am having difficulties with the query.

The documents:

{
    "id": 1,
    "permissions": ["a", "d"]
}
{
    "id": 2,
    "permissions": ["a"]
}
{
    "id": 3,
    "permissions": ["a", "b"]
}
{
    "id": 4,
    "permissions": ["a", "c"]
}

This is the closest I got:

{
  "query": {
    "bool": {
      "should": [
        {
          "match_phrase": {
            "tags.keyword": "a"
          }
        },
        {
          "match_phrase": {
            "tags.keyword": "b"
          }
        },
      ],
      "minimum_should_match": doc.tags.length
    }
  }
}
// Result are documents with id's 2 and 3.

I tried extending the "minimum_should_match" with the "script" but without success (apparently it does not support it):

"script" : {
    "script" : {
        "inline": "doc['permissions'].length",
        "lang": "painless"
     }
}

In an example above, with passed permission array ["a", "b", "c"], the output should be documents with id's 2, 3 and 4. ["a"] matches only document with id 2.

EDIT: Additional information A document has up to 5 permissions, as well as the users, but the set of permissions is quite big (500+), so I am searching for a generic query. The data can also be transformed.

I am using Elastic 7.6 Any help is appreciated!

Bhavya · Accepted Answer · 2020-09-27 09:01:51Z

0

There is no efficient way to achieve your expected result, but apart from the below search query, you can even get your result using scripting, please refer this SO answer to know that.

You can use Terms Query that returns documents that contain one or more exact terms in a provided field.

Adding a working example with index data(taken the same as that given in question), index mapping, search query, and search result.

Index Mapping:

{
  "mappings": {
    "properties": {
      "id": {
        "type": "integer"
      },
      "permissions": {
        "type": "text"
      }
    }
  }
}

Search Query:

 {
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "must": [
            {
              "terms": {
                "permissions": [
                  "a",
                  "b",
                  "c"
                ]
              }
            }
          ],
          "must_not": [
            {
              "terms": {
                "permissions": [
                  "d"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Search Result:

"hits": [
  {
    "_index": "stof_64081578",
    "_type": "_doc",
    "_id": "2",
    "_score": 1.0,
    "_source": {
      "id": 2,
      "permissions": [
        "a"
      ]
    }
  },
  {
    "_index": "stof_64081578",
    "_type": "_doc",
    "_id": "3",
    "_score": 1.0,
    "_source": {
      "id": 3,
      "permissions": [
        "a",
        "b"
      ]
    }
  },
  {
    "_index": "stof_64081578",
    "_type": "_doc",
    "_id": "4",
    "_score": 1.0,
    "_source": {
      "id": 4,
      "permissions": [
        "a",
        "c"
      ]
    }
  }
]

edited Sep 27, 2020 at 9:01

answered Sep 27, 2020 at 1:25

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

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

5 Comments

Jaka Konda Over a year ago

The first document is retrieved incorrectly. Permission "d" is required which user does not have (has a, b, c). The binding expression should be AND instead of OR, which terms does behind the scenes. Also saw I messed up the first example. Id's 2- 4 should be returned. Edited. P.S.: Some people need sleep ;).

Bhavya Over a year ago

@JakaKonda please go through my updated answer, and let me know if this resolved your issue ?

Bhavya Over a year ago

@JakaKonda did you get a chance to go through my answer, looking forward to get feedback from you :)

Jaka Konda Over a year ago

It works for a specific case so it won't work generalized. A user always has 5 permissions, with about 500 different values (they aren't permissions, but are used for easier understanding). And the document always has up to 5 permissions.

Bhavya Over a year ago

@JakaKonda as far as I know, there is no generic way to achieve your required results. Apart from the above search query you can even use script

Collectives™ on Stack Overflow

Elastic match documents containing all values in array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related