ElasticSearch sort by field in NestedObject At First Index Of Array

Question

I am trying to sort a field inside the first object of an array in the following docs each docs has an array i want to retrieve the docs sorted by they first objects by there city name lets name that in the following result I want to have first the third documents because the name of the city its start by "L" ('london') then the second "M" ('Moscow') then the third "N" ('NYC')

the structure is a record that:

has an array
the array contains an object (called 'address')
the object has a field (called 'city')

i want to sort the docs by the first address.cities

    get hello/_mapping 
    {
      "hello": {
        "mappings": {
          "jack": {
            "properties": {
              "houses": {
                "type": "nested",
                "properties": {
                  "address": {
                    "properties": {
                      "city": {
                        "type": "text",
                        "fields": {
                          "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }

Thos are the document that i indexed

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 3,
    "max_score": 1,
    "hits": [
      {
        "_index": "hello",
        "_type": "jack",
        "_id": "2",
        "_score": 1,
        "_source": {
          "houses": [
            {
              "address": {
                "city": "moscow"
              }
            },
            {
              "address": {
                "city": "belgrade"
              }
            },
            {
              "address": {
                "city": "Sacramento"
              }
            }
          ]
        }
      },
      {
        "_index": "hello",
        "_type": "jack",
        "_id": "1",
        "_score": 1,
        "_source": {
          "houses": [
            {
              "address": {
                "city": "NYC"
              }
            },
            {
              "address": {
                "city": "PARIS"
              }
            },
            {
              "address": {
                "city": "TLV"
              }
            }
          ]
        }
      },
      {
        "_index": "hello",
        "_type": "jack",
        "_id": "3",
        "_score": 1,
        "_source": {
          "houses": [
            {
              "address": {
                "city": "London"
              }
            }
          ]
        }
      }
    ]
  }
}

LeBigCat · Accepted Answer · 2019-03-07 16:58:36Z

1

Try this (of course, add some test inside the script if field could be empty. Note it could be pretty slow, because elastic wont have this value indexed. Add a main address field would be faster (and really faster) for sure and would be the good way to do it.

{
      "sort" : {
        "_script" : {
            "script" : "params._source.houses[0].address.city",
            "type" : "string",
            "order" : "asc"
        }
    }
}

You have to use _source instead of doc[yourfield] because you dont know in witch order elastic store your array.

EDIT: test if field exist

{
  "query": {
    "nested": {
      "path": "houses",
      "query": {
        "bool": {
          "must": [
            {
              "exists": {
                "field": "houses.address"
              }
            }
          ]
        }
      }
    }
  },
  "sort": {
    "_script": {
      "script" : "params._source.houses[0].address.city",
      "type": "string",
      "order": "asc"
    }
  }
}

edited Mar 7, 2019 at 16:58

answered Mar 7, 2019 at 16:37

LeBigCat

1,7801 gold badge14 silver badges17 bronze badges

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

3 Comments

Nathan Over a year ago

he return a null pointer because some record has no value in [0] index

Nathan Over a year ago

hi thank a lot it works when the collection is small but in my production env that there are 5M records he returns a timeout { "statusCode": 504, "error": "Gateway Timeout", "message": "Client request timeout" }

LeBigCat Over a year ago

Yeah it s normal. Like i said elastic doesnot index field he doesnot need, so in fact the script is looking for all your record. You have to add a new field in your type (the main_adress = adress[0] you want) and request this new indexed field. Elastic will reponse almost immediatly then.

Collectives™ on Stack Overflow

ElasticSearch sort by field in NestedObject At First Index Of Array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related