# Subset match query

**URL:** <https://discuss.elastic.co/t/subset-match-query/261847>\
**Category:** Elasticsearch\
**Created:** [January 21, 2021, 7:57pm UTC](https://discuss.elastic.co/t/subset-match-query/261847 "2021-01-21T19:57:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Josue-Salas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/josue-salas/32/82709_2.png) [@Josue-Salas](https://discuss.elastic.co/u/Josue-Salas)\
**Post date:** [January 21, 2021, 7:57pm UTC](https://discuss.elastic.co/t/subset-match-query/261847/1 "2021-01-21T19:57:47Z")

</div>

Hi everyone,

Thanks for reading.

Here's my problem. I have the following documents:

```auto
"course #1": {
    "requirements": [
        {
            "name": "AWS EC2 certification",
            "type": "certification"
        },
        {
            "name": "Python Advanced",
            "type": "certification"
        },
        {
            "name": "Critical Thinking",
            "type": "skill",
        }
}

"course #2": {
    "requirements": [
        {
            "name": "Ruby on Rails advanced",
            "type": "certification"
        },
        {
            "name": "Critical Thinking",
            "type": "skill",
        }
}

```

I want the users to find the courses where they match all the requirements, for example:

The user that has "Critical Thinking", "Ruby on Rails advanced" and "Python Advanced", should get only the course #2. In other words, I want to find the courses where the requirements are a subset of the user skills.

I saw posts related to this issue but are very old and I'm hoping to find a clean solution.

Thanks. I hope you have some inputs.

---

<div class="post-metadata">

**Author:** ![mayya](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mayya/32/83147_2.png) [@mayya](https://discuss.elastic.co/u/mayya)\
**Post date:** [January 21, 2021, 11:43pm UTC](https://discuss.elastic.co/t/subset-match-query/261847/2 "2021-01-21T23:43:08Z")

</div>

Start simple with terms and [terms\_set](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-set-query.html) query. After that you can add a nested layer on top.

Example, let's map `requirements` as simple keyword field:

```auto
PUT test_index
{
  "mappings": {
    "properties": {
      "requirements": {
        "type": "keyword"
      }
    }
  }
}

```

Indexing some docs:

```auto
POST test_index/_bulk
{ "index" : { "_id" : "course1" } }
{"requirements": ["A", "B", "C"]}
{ "index" : { "_id" : "course2" } }
{"requirements": ["D", "C"]}

```

The query below returns you only courses that satisfy all your requirements.

```auto
GET test_index/_search
{
 "query": {
   "terms_set" : {
     "requirements" : {
       "terms" : ["C", "D"],
       "minimum_should_match_script": {
          "source": "params.num_terms"
        }
     }
   }
 }
}

```

---

<div class="post-metadata">

**Author:** ![Josue-Salas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/josue-salas/32/82709_2.png) [@Josue-Salas](https://discuss.elastic.co/u/Josue-Salas)\
**Post date:** [January 22, 2021, 5:42pm UTC](https://discuss.elastic.co/t/subset-match-query/261847/3 "2021-01-22T17:42:03Z")

</div>

Hi Mayya, thanks for the quick response.  
The solution you posted is working fine, but when I tried to add a nested layer on top, it didn't work. So I was wondering if the terms\_set works with nested objects, I looked this post but it has no answers [Terms\_set within a nested query](https://discuss.elastic.co/t/terms-set-within-a-nested-query/218852), and what is happening to me is exactly that. When I used a nested query, there are no results in the response.

It looks like this:

```auto
{
        nested: {
          path: 'requirements',
          query: {
            terms_set: {
              'requirements.name': {
                terms: ["Python Advanced", "Critical Thinking"],
                minimum_should_match_script: {
                  source: "params.num_terms"
                },
              },
            }
          }
        }
}

```

And it works when I copy/paste the one you posted

---

<div class="post-metadata">

**Author:** ![mayya](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mayya/32/83147_2.png) [@mayya](https://discuss.elastic.co/u/mayya)\
**Post date:** [January 22, 2021, 10:03pm UTC](https://discuss.elastic.co/t/subset-match-query/261847/4 "2021-01-22T22:03:42Z")

</div>

I've realized that I have provided a wrong solution with `terms_set` query, as it would not returned subset, but rather a superset; that is you would not be able to find the courses where the requirements are a subset of the users skills, but rather find the courses where the user skills are the subset of course requirements.

Again, disregarding a nested part, to find a correct subset, you would need to writer a custom script that would compare if each course requirement is within user skills, something like this:

```auto
GET test_index/_search
{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "source": """
            for (int i = 0; i < doc['requirements'].length; i++) {
              boolean found = false;
              for(int j = 0; j < params.user_skills.length; j++) { 
                if (doc['requirements'][i] == params.user_skills[j]) {
                  found = true;
                  break;
                }
              }  
              if (found == false) return false;
            }
            return true;
            """,
            "params": {
              "user_skills": ["C", "D"]
            }
          }
        }
      }
    }
  }
}

```

You can't use this for nested objects, as each nested object is a separate document. Some possible solutions I can see are:

1. Don't use nested, and just use "requirements" as a keyword field with multiple values (the way I did in my example)
2. You can copy [nested values in a root/parent object](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html#nested-params) with `include_in_parent` and `include_in_root` options, and run queries on a corresponding root field.

May be there are some other clever ways to do it for nested objects, but nothing comes to my mind right now

---

<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:** [February 19, 2021, 10:03pm UTC](https://discuss.elastic.co/t/subset-match-query/261847/5 "2021-02-19T22:03:51Z")

</div>

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