Elastic Search Terms Query

Question

I have a requirement in the terms query for a nested field in elastic-search where the nested field values should match exactly with the number of values provided in the terms query. For example, consider the below query where we have terms query on the nested field named Types.

GET assets/_search

{
      "size": "10",
      "from": 0,
      "query": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "Types",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "term": {
                          "Types.Label.keyword": 
                            ["VOD, AOP"]
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
  }

Index Mapping

{
  "media-assets": {
    "mappings": {
      "dynamic": "true",
      "properties": {
        "Types": {
          "type": "nested",
          "properties": {
            "Label": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256,
                  "normalizer": "lowercase_normalizer"
                },
                "ngram": {
                  "type": "text",
                  "analyzer": "ngram_tokenizer_analyzer"
                }
              }
            }
          }
        }
      }
    }
  }
}

Sample Document:

{
  "_source": {
    "AssetId": 1657352,
    "MaterialId": "XBV01001",
    "AirDate": "1997-03-10T00:00:00Z",
    "Types": [
      {
        "Type": "AOP"
      },
      {
        "Type": "VOD"
      }
    ]
  }
}

The above query should return documents where the field Types has exactly 2 values i.e "VOD" & "AOP".If a document has these 2 values along with some other values in the field Types, then it should not return that document because the number of terms provided in the query should match with the number of values present in the Types field.

can you please share your index mapping and some sample index data ? — ESCoder
Hi @ESCoder, I have updated the post with a sample document and indexed mapping.Thanks! — arjun bollam

ESCoder ESCoder · Accepted Answer · 2021-05-21T16:59:14

You need to use script_score as defined in the search query below along with the function score query.

Adding a working example with index data, mapping, search query and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "Types": {
        "type": "nested"
      }
    }
  }
}

Index Data:

{
  "Types": [
    {
      "Label": "VOD"
    },
    {
      "Label": "AOP"
    },
    {
      "Label": "ABC"
    }
  ]
}
{
  "Types": [
    {
      "Label": "VOD"
    },
    {
      "Label": "AOP"
    }
  ]
}

Search Query:

{
  "query": {
    "function_score": {
      "query": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "Types",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "terms": {
                          "Types.Label.keyword": [
                            "VOD",
                            "AOP"
                          ]
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      },
      "functions": [
        {
          "script_score": {
            "script": {
              "source": "params._source.containsKey('Types') && params._source['Types'] != null && params._source.Types.size() == 2 ? 1 : 0"
            }
          }
        }
      ],
      "min_score": 1
    }
  }
}

Search Result:

"hits": [
      {
        "_index": "67639937",
        "_type": "_doc",
        "_id": "1",
        "_score": 1.0,
        "_source": {
          "Types": [
            {
              "Label": "VOD"
            },
            {
              "Label": "AOP"
            }
          ]
        }
      }
    ]

Elastic Search Terms Query

2 Answers