CODEWITHSUNDEEP
elasticsearchelasticsearch-query
Arkady Spiridonov
Arkady Spiridonov

Reputation: 150

Search with exclusion (MUSTNOT) in children fields

I want to find all cities which are not contain term "big" in the name of the street.

Here is my items in index: 

{
  "name":"the big street",
  "city": "NY",
  "count": 2
},
{
  "name":"not big street",
  "city": "AM",
  "count": 43
},
{
  "name":"st42",
  "city": "NY",
  "count": 1687
},
{
  "name":"Anacostia Drive",
  "city": "WASH",
  "count": 1687
},

I need to find all cities where name MUST NOT contains (exclude) the term "big".

when I try to use term query with must_not 

{
  "collapse": {
    "field": "city"
  },
  "query": {
    "bool" : {
"must_not" : {
        "term" : {
          "name" : { "big" }
        }
      },
    }
 }
}

I found cities includes "NY", due item with "st42" - and it's wrong due match with "the big street" item.

How to resolve it?

Decision below it good, but query can be more complicated and I need highlight:

{
  "aggs": {
    "aggs_by_city": {
      "terms": {
        "field": "city.keyword",
        "size": 20
      },
      "aggs": {

        "filter_exclude_count": {
          "filter": { "match" : { "name" : "big"   }}
        },
        "filter_include_count": {
          "filter": { "match" : { "name" : "Anacostia"   }}
        },
        "selector": {
          "bucket_selector": {
            "buckets_path": {
              "exclude_cnt": "filter_exclude_count._count",
              "include_name": "filter_include_count._count"
            },
            "script": "params.exclude_cnt == 0 && params.include_name > 0"
          }
        }
      }
    }
  },
  "size": 0
}

Upvotes: 0

Views: 76

Answers (1)

Tuyen Luong
Tuyen Luong

Reputation: 1366

It seems that you want to "find all the cities which does not have any street which has "big" in street name"?

You can do it using aggregation, this solution does not use collapse:

{
  "aggs": {
    "aggs_by_city": {
      "terms": {
        "field": "city",
        "size": 10
      },
      "aggs": {
        "filter_exclude_count": {
          "filter": { "match" : { "street" : "big"   }}
        },
        "sellector": {
          "bucket_selector": {
            "buckets_path": {
              "exclude_cnt": "filter_exclude_count._count"
            },
            "script": "params.exclude_cnt == 0"
          }
        }
      }
    }
  },
  "size": 0
}
  1. Group all city
  2. Count all documents which street name contains "big"
  3. If the above count == 0 then select this aggs_by_city aggregation using bucket_selector

Upvotes: 1

Related Questions