Query multi value field, to match all values passed in

Elastic Stack Elasticsearch
1

Hi All,

I have data in elasticsearch with the mapping for an index type entity that looks like the following.
The value for the field "codes" would be a list of comma separated strings

"mappings": {
    "entity": {
      "properties": {
        "id": {
          "type": "long"
        },
        "name": {
          "type": "string"
        },
        "description": {
          "type": "string"
        },
        "codes": {
          "type": "string"
        }
      }
    }
  }

The data returned by a search query for a entities would look like
hits: [
{
...
"_source": {
id: "23"
name: "John"
description: "some description"
codes: [
"10", "15", "16", "21", "34"
]
}
},
{
...
"_source": {
id: "43"
name: "Jane"
description: "another description"
codes: [
"10", "31", "34", "41", "56"
]
}
},
{
...
"_source": {
id: "43"
name: "Jim"
description: "another description"
codes: [
"10", "34", "55", "67", "89"
]
}
}
]

Now the problem I am facing is that, I need to query for this data, passing in a list of codes, as a parameter, so that each entity returned contains ALL the codes that are passed in.

Initially I was using the terms query, as it seemed like a compact way to query if there list of codes was long.

GET index/entity/_search
{
  "from" : 0,
  "size" : 10,
  "query" : {
    "filtered" : {
      "query" : {
        "bool" : {
        "must" : [        
           {
             "terms": {
               "codes":  [ "10", "34", "89"]
             }
           }
         ]
        }
      }
    }
  }
}

Unfortunately, this was incorrect, as the terms query will match all the entities which have at least one of the
provided codes in the list (John, Jane & Jim)

Then I moved to the following approach:

GET index/entity/_search
{
  "from" : 0,
  "size" : 10,
  "query" : {
    "filtered" : {
      "query" : {
        "bool" : {
          "must" : [ 
             {"term": {
                "codes": "10"
             }},
             {"term": {
                "codes": "34"
             }},
             {"term": {
                "codes": "89"
             }
          } ]
        }
      }
    }
  }
}

This works fine, and returns only entities with all 3 codes (Jim), but I am concerned that if I have to search for entities that contain say 20 codes, this would make the query quite long, and duplicate the "term": {"codes" : "xx"} clause multiple times.

Is there a more elegant solution for this? Preferably something like using the terms query, but to match all values in the list.

2

Hey,

indeed this the only way, however dont be afraid of a long query. If a long query gets you the results you want, all is good. Also the terms query is rewritten kind of the same queries that are generated by your code (only must instead of just).

Minor tweak: put the term query into the filter path instead of the must path of bool query, as you dont have a need for scoring here I'd guess. See https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html

--Alex