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, Sep 09 '20 at 13:45
record: { "Cities": [ { "id": 1, "city": "Bangalore" }, { "id": 2, "city": "Hyderabad" }, { "id": 3, "city": "Delhi" } ] } — kuna, Sep 09 '20 at 13:45

score 2 · Accepted Answer · answered Sep 09 '20 at 08:07

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 09 '20 at 08:07

Amit

30,756
6
57
88

The sample doc you took does not match my given doc. Cities : [ name : Bangalore, name : chennai , name : Hyderabad ] is my doc structure – kuna Sep 09 '20 at 08:13
@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 ? – Amit Sep 09 '20 at 08:15
1

@OpsterElasticsearchNinja you are totally right, with new informations I can see that it's not a nested field issue – Jaycreation Sep 09 '20 at 08:50
hi @OpsterElasticsearchNinja added comments in the question . please check – kuna Sep 09 '20 at 13:43
Hi , have you looked into this? – kuna Sep 10 '20 at 00:41
@kuna will look into in sometime – Amit Sep 10 '20 at 03:20
Ok. Waiting @opster :) – kuna Sep 10 '20 at 10:31
@kuna so I am looking at your edit and sample you provided in your question is different than what is mentioned in question comment, could you please provide the proper JSON of your actual document? – Amit Sep 10 '20 at 12:09
I can't post my actual record. I posted my record sample. How to achieve my usecase is my query. Record sample is in comments @opster – kuna Sep 11 '20 at 06:37
any luck ?? @opster – kuna Sep 13 '20 at 07:08
@kuna you are unnecessary using nested data types which are costly and sorry but unless you explains your use case in detail its difficult to provide the optimised solution and as mentioned earlier pls provide the valid JSON, i don't need your actual records but sample records which matches your mapping and works so that its easy to work on your data, I already spent some time to give my initial answer without much info and still I don't feel I have all required info :( – Amit Sep 13 '20 at 09:08
Have you gone through the record mentioned in the comments. That is exactly my record structure. Please look into it and let me know @opster – kuna Sep 14 '20 at 10:04
hi @opster please have a look . updated with my actual record – kuna Sep 15 '20 at 09:35
hi @opster , please look into this https://stackoverflow.com/questions/63900682/need-elastic-search-query-for-the-below-required-scenario – kuna Sep 15 '20 at 11:16
hi @opster can you please answer this..https://stackoverflow.com/questions/63907775/get-relevant-data-on-elastic-search – kuna Sep 17 '20 at 15:19

score 0 · Answer 2 · answered Sep 15 '20 at 02:31

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"
                        }
                    ]
                }
            }
        ]

elasticsearch filter on nested array

2 Answers2

Working example