elasticsearch filter on nested array

Question

lets say records have city field as an array of city names.

records ex:

  record 1:
    {
      cities : [
        {name: city1},
        {name : city2},
        {name : city3}
      ]
    }
    record 2:
    {
      cities : [
        {name: city2},
        {name : city3},
        {name : city4}
      ]
    }
    record 3:
    {
      cities : [
        {name: city3},
        {name : city4},
        {name : city5}
      ]
    }

requirement: My filter criteria is to fetch the records matches with city1 or city2 or city3 but since the record 1 matches all 3 it should come first and record 2 matches 2 so it should come 2nd and record 3 matches only one so it should come last.

mapping: { "Cities": { "type": "nested", "dynamic": "true", "properties": { "id": { "type": "long" }, "city": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 32766, "normalizer": "lowerasciinormalizer" } } } } } } — kuna
– kuna, Commented Sep 9, 2020 at 13:45
record: { "Cities": [ { "id": 1, "city": "Bangalore" }, { "id": 2, "city": "Hyderabad" }, { "id": 3, "city": "Delhi" } ] } — kuna
– kuna, Commented Sep 9, 2020 at 13:45

Amit · Accepted Answer · 2020-09-09 08:07:22Z

2

You don't have to use the nested data-type as you don't have the nested properties or complex object, its very simple and easy to achieve.

Working example

Index mapping

{
    "mappings": {
        "properties": {
            "cities": {
                "type": "text"
            }
        }
    }
}

Index sample docs

{
    "cities": [
        "tel-aviv", "bangalore", "sf"
    ]
}

{
    "cities": [
        "tel-aviv"
    ]
}

{
    "cities": [
        "sf"
    ]
}

Search query

{
    "query": {
        "bool": {
            "should": [
                {
                    "match": {
                        "cities": "tel-aviv"
                    }
                },
                {
                   "match": {
                        "cities": "bangalore"
                    }
                },
                 {
                   "match": {
                        "cities": "sf"
                    }
                }
            ] 
        }
    }
}

And search result with proper expected result and score

 "hits": [
            {
                "_index": "cities",
                "_type": "_doc",
                "_id": "1",
                "_score": 1.850198,
                "_source": {
                    "cities": [
                        "tel-aviv",
                        "bangalore",
                        "sf"
                    ]
                }
            },
            {
                "_index": "cities",
                "_type": "_doc",
                "_id": "2",
                "_score": 0.9983525,
                "_source": {
                    "cities": [
                        "tel-aviv"
                    ]
                }
            },
            {
                "_index": "cities",
                "_type": "_doc",
                "_id": "3",
                "_score": 0.6133945,
                "_source": {
                    "cities": [
                        "sf"
                    ]
                }
            }
        ]

answered Sep 9, 2020 at 8:07

Amit

32.5k7 gold badges68 silver badges101 bronze badges

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

15 Comments

kuna Over a year ago

The sample doc you took does not match my given doc. Cities : [ name : Bangalore, name : chennai , name : Hyderabad ] is my doc structure

Amit Over a year ago

@kuna can you provide the real doc in JSON format and provide your mapping, sample docs in proper JSON format, as I provided in my answer ?

Jaycreation Over a year ago

@OpsterElasticsearchNinja you are totally right, with new informations I can see that it's not a nested field issue

kuna Over a year ago

hi @OpsterElasticsearchNinja added comments in the question . please check

kuna Over a year ago

Hi , have you looked into this?

|

Amit · Accepted Answer · 2020-09-15 02:31:48Z

Adding another answer with nested bool queries:

Index Mapping:

{
    "mappings": {
        "properties":{
        "Cities": {
            "type": "nested",
            "dynamic": "true"
        }
    }}
}

Index Data:

{
  "Cities": [
    {
      "id": 1,
      "city": "Bangalore"
    },
    {
      "id": 2,
      "city": "Hyderabad"
    },
    {
      "id": 3,
      "city": "Delhi"
    }
  ]
}
{
  "Cities": [
    {
      "id": 1,
      "city": "Bangalore"
    },
    {
      "id": 2,
      "city": "abc"
    },
    {
      "id": 3,
      "city": "Def"
    }
  ]
}

Search Query:

{
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "Cities",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "Cities.city": "Bangalore"
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "Cities",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "Cities.city": "Hyderabad"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

Search Result:

 "hits": [
            {
                "_index": "nested-63806067",
                "_type": "_doc",
                "_id": "1",
                "_score": 3.297317,   <-- note this
                "_source": {
                    "Cities": [
                        {
                            "id": 1,
                            "city": "Bangalore"
                        },
                        {
                            "id": 2,
                            "city": "Hyderabad"
                        },
                        {
                            "id": 3,
                            "city": "Delhi"
                        }
                    ]
                }
            },
            {
                "_index": "nested-63806067",
                "_type": "_doc",
                "_id": "2",
                "_score": 1.6486585,   <-- note this
                "_source": {
                    "Cities": [
                        {
                            "id": 1,
                            "city": "Bangalore"
                        },
                        {
                            "id": 2,
                            "city": "abc"
                        },
                        {
                            "id": 3,
                            "city": "Def"
                        }
                    ]
                }
            }
        ]

Collectives™ on Stack Overflow

elasticsearch filter on nested array

2 Answers 2

Working example

15 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Working example

15 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related