ElasticSearch nested sort with filter

Question

I've documents that contain a list of prices for specific keys, for example as the following

document1

 {
        "name":"doc1",
        "cheapestPrices": [{
                "key": "10000_BB",
                "value": 50
            }, {
                "key": "10000_LO",
                "value": 10
            }, {
                "key": "10000",
                "value": 10
            }, {
                "key": "",
                "value": 10
            }
        ]
    }

document2

{
    "name":"doc2",
    "cheapestPrices": [{
            "key": "10000_BB",
            "value": 15
        }, {
            "key": "10000_LO",
            "value": 30
        }, {
            "key": "10000",
            "value": 15
        }, {
            "key": "",
            "value": 15
        }
    ]
}

Now I send a query and I want to sort by given keys and the order should be from lowest to highest. I created this query:

{
    "size": 10000,
    "sort": [
        {
            "cheapestPrices.value": {
                "mode": "min",
                "nested": {
                    "filter": {
                        "bool": {
                            "should": [
                                {
                                    "term": {
                                        "cheapestPrices.key": {
                                            "value": "10000_BB"
                                        }
                                    }
                                }
                            ]
                        }
                    },
                    "path": "cheapestPrices"
                },
                "order": "asc"
            }
        }
    ]
}

Expecting that I would get doc2 (value 15 for that key) first and then doc1 (value 50 for that key)... but the result are doc1 and then doc2 and the sort score is exactly the same.

Result:

{
    "took": 10,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": null,
        "hits": [{
                "_index": "test_sortbyprice",
                "_type": "_doc",
                "_id": "doc1",
                "_score": null,
                "_source": {
                    "cheapestPrices": [{
                            "key": "10000_BB",
                            "value": 50
                        }, {
                            "key": "10000_LO",
                            "value": 10
                        }, {
                            "key": "10000",
                            "value": 10
                        }, {
                            "key": "",
                            "value": 10
                        }
                    ],
                    "name": "doc1"
                },
                "sort": [
                    9223372036854775807
                ]
            }, {
                "_index": "test_sortbyprice",
                "_type": "_doc",
                "_id": "doc2",
                "_score": null,
                "_source": {
                    "cheapestPrices": [{
                            "key": "10000_BB",
                            "value": 15
                        }, {
                            "key": "10000_LO",
                            "value": 30
                        }, {
                            "key": "10000",
                            "value": 15
                        }, {
                            "key": "",
                            "value": 15
                        }
                    ],
                    "name": "doc2"
                },
                "sort": [
                    9223372036854775807
                ]
            }
        ]
    }
}

The mapping is as follow:

{
    "properties": {
        "cheapestPrices": {
            "type": "nested",
            "properties": {
                "value": {
                    "type": "integer"
                },
                "key": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "ignore_above": 256,
                            "type": "keyword"
                        }
                    }
                }
            }
        },
        "name": {
            "type": "text",
            "fields": {
                "keyword": {
                    "ignore_above": 256,
                    "type": "keyword"
                }
            }
        }
    }
}

Jozef - Spatialized.io · Accepted Answer · 2020-11-30 21:00:37Z

2

TL;DR

Change the term query to target the field cheapestPrices.key.keyword instead of cheapestPrices.key.

The sort query does not match any documents due to using term (an exact match) on a field that's been lowercased thanks to the standard analyzer which was applied by default on a text field w/ no extra analyzers. This means it's never going to equal 10000_BB (uppercase). But luckily you have the .keyword available which ensures no value modifications.

The sort scores are the same (I assume 9223372036854775807 a.k.a. Long.MAX_VALUE) because that's the default ES behavior. It's not really that far fetched when you think about it: if the sort query does not match anything, it'll assign the highest possible value.

If your order were desc, it'd have returned -Long.MAX_VALUE

answered Nov 30, 2020 at 21:00

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

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

1 Comment

BvdVen Over a year ago

yes, that did the trick. I found out just before you answered, but thank you anyway.

Collectives™ on Stack Overflow

ElasticSearch nested sort with filter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related