Filter result with array of possible values

Question

I have following mapping:

{
  cities: {
    mappings: {
      city: {
        properties: {
          id: {
            type: "long"
          },
          name: {
            type: "text",
            fields: {
              keyword: {
                type: "keyword",
                ignore_above: 256
              }
            }
          },
          population: {
            type: "long"
          },
        }
      }
    }
  }
}

and performing simple query:

{
  query: {
    bool: {
      must_not: { match: { name: 'New York' } },
    },
  },
  size: 2,
}

So in the results I get:

{
  "took": 5,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 10385,
    "max_score": 1,
    "hits": [
      {
        "_index": "cities",
        "_type": "city",
        "_id": "14",
        "_score": 1,
        "_source": {
          "name": "Berlin",
          "id": 14,
          "population": 7293
        }
      },
      {
        "_index": "cities",
        "_type": "city",
        "_id": "19",
        "_score": 1,
        "_source": {
          "name": "Paris",
          "id": 19,
          "population": 25018
        }
      }
    ]
  }
}

How can I limit this query to match documents where name is in specific array of values, i.e. ['Berlin', 'Bonn', 'Munchen'] ?

I'm looking for something simmilar to SQL statement like:

SELECT * FROM cities WHERE name != 'New York' and name IN ('Berlin', 'Bonn', 'Munchen');

hsz · Accepted Answer · 2017-12-01 11:33:06Z

1

You can use terms query so your query should look like below:

{
  query: {
    bool: {
      must: { terms: { 'name.keyword': ['Berlin', 'Bonn', 'Munchen'] } }
      must_not: { match: { name: 'New York' } },
    },
  },
  size: 2,
}

edited Dec 1, 2017 at 11:33

hsz

153k63 gold badges269 silver badges320 bronze badges

answered Dec 1, 2017 at 11:24

CodeNotFound

23.4k10 gold badges75 silver badges73 bronze badges

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

1 Comment

hsz Over a year ago

Combining it with @Lupanoide's 'name.keyword' does the work. Thanks!

Lupanoide · Accepted Answer · 2017-12-01 11:30:18Z

0

POST /cities/_search
{
  "query": {
    "bool" : {
      "must" : [
        { "match" : { "name" : "Berlin" } },
        { "match" : { "name" : "Munchen" } },
        { "match" : { "name" : "Bonn" } }
      ],
      "minimum_should_match" : 3
    }
  }
}

or

POST /cities/_search
{
  "query": {
    "bool" : {
      "must" : [
        { "term" : { "name.keyword" : "Berlin" } },
        { "term" : { "name.keyword" : "Munchen" } },
        { "term" : { "name.keyword" : "Bonn" } }
      ],
      "minimum_should_match" : 3
    }
  }
}

edited Dec 1, 2017 at 11:30

answered Dec 1, 2017 at 11:24

Lupanoide

3,25225 silver badges38 bronze badges

2 Comments

hsz Over a year ago

Splitting terms into separated objects is not a solution.

Lupanoide Over a year ago

the term query wants keyword datatype, this is the reason because the other answer doesn't work. The match query wants text datatype. Splitting term in query is only a different syntax, the query is the same

Collectives™ on Stack Overflow

Filter result with array of possible values

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related