How to sort In Elasticsearch by parent and nested fields at the same time?

Question

I need to sort in Elasticsearch by parent and nested fields at the same time. My data are like this:

[
    {
        "id": "1",
        "rank": 8,
        "price": 12.45,
        "offers": [
            {
                "id": "777",
                "rank": 12,
                "price": 45.75
            }
        ]
    },
    {
        "id": "2",
        "rank": 35,
        "price": 5.95,
        "offers": null
    }
]

I need to sort the results on rank in such a manner that when the offers are not null I should take nested offers.rank value, otherwise I should take the parent rank value. I tried this script, but it did not work:

    "sort": [
        {
            "_script": {
                "script": {
                    "source": "doc['offers'].size()==0 ? doc['rank'].value : doc['offers.rank'].value",
                    "lang": "painless"
                },
                "type": "number",
                "order": "asc"
            }
        }
    ]

It did not work probably because offers.rank is from a nested offers object, which is not accessible. But I do not understand how to handle it - if I add a nested condition for the whole script, then my parent value doc['rank'].value will not be accessible anymore. It is possible to sort here by parent and nested fields at the same time?

If the nest field 'offers' contains multiple offers then which value to consider while sorting the documents? — Sahil Gupta
– Sahil Gupta, Commented Sep 17, 2020 at 15:40

Jozef - Spatialized.io · Accepted Answer · 2020-09-17 15:45:22Z

3

You're right in assuming that the parent rank would not be accessible. Now, you could either

create 2 separate sort 'objects', one for the parent, one for the nested offers, and then sum the results up using the sort mode or
iterate on the _source instead:

{
  "sort": [
    {
      "_script": {
        "script": {
          "source": """
          if (params._source.offers instanceof ArrayList
              && params._source.offers.length > 0) {
            return params._source['offers'][0].rank;
          }
          return params._source.rank
          """,
          "lang": "painless"
        },
        "type": "number",
        "order": "asc"
      }
    }
  ]
}

Note that since we're working with an 'offers' ArrayList here, you'll need some sort of mechanism to pick a rank. That's up to you -- I've simply accessed the first offer's rank and you may want to sort the array list & pick the highest one...

Here's a one-liner if that's what you prefer:

params._source.offers instanceof ArrayList && params._source.offers.length > 0 ? params._source['offers'][0].rank : params._source.rank

answered Sep 17, 2020 at 15:45

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

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

5 Comments

Mikhail Geyer Over a year ago

Thank you @joe. I tried the second solution and it works. Though at the beginning of the condition we need to add an extra condition params._source.offers != null otherwise the request fails with ...,"lang":"painless","caus ed_by":{"type":"null_pointer_exception","reason":null}. This is because offers can be null.

Mikhail Geyer Over a year ago

It is very good to know that the solution #2 technically exists and we can access parent and nested fields in one script! Though I found in elastic.co/guide/en/elasticsearch/reference/master/… that Accessing the _source field is much slower than using doc-values. So I need to test the performance of solution #2 to understand if it is fast enough for my task. If the performance will not be ok, I will try the solution #1.

Jozef - Spatialized.io Over a year ago

Sounds good. It all depends on the index size and how docs you're sorting (since the sorting action happens after query resolution.)

Mikhail Geyer Over a year ago

can params._source['offers'][0].rank be written in the form of params._source.offers[0].rank? Or the latter form is not correct and only the first one should be used?

Jozef - Spatialized.io Over a year ago

The _source is of type SourceLookup which implements HashMap so both are acceptable -- javadoc.io/doc/org.elasticsearch/elasticsearch/5.0.0/org/…

Collectives™ on Stack Overflow

How to sort In Elasticsearch by parent and nested fields at the same time?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related