Price sort not working in elasticsearch 7.9.3 version

Elastic Stack Elasticsearch
1

Hi everyone,

Currently I am using the Elasticsearch 7.9.3 version. the issue is price sort works wrong after upgrading the old version. Price data's are mixed with double , integer. Please let me know what happened in the new version or suggest the new mapping type for price or amount. 4 and 4 raw is price sort
{"user_products":{"mappings":{"properties":{"1":{"type":"text","fielddata":true},"2":{"type":"text","fielddata":true},"3":{"type":"text","fielddata":true},"4":{"type":"scaled_float","scaling_factor":100},"5":{"type":"double"},"6":{"type":"text","fielddata":true},"7":{"type":"text","fielddata":true},"8":{"type":"text","fielddata":true},"9":{"type":"text","fielddata":true},"10":{"type":"text","fielddata":true},"11":{"type":"text","fielddata":true},"12":{"type":"text","fielddata":true},"13":{"type":"text","fielddata":true},"14":{"type":"scaled_float","scaling_factor":100},"15":{"type":"text","fielddata":true},"16":{"type":"text","fielddata":true},"10_raw":{"type":"keyword"},"11_raw":{"type":"keyword"},"12_raw":{"type":"keyword"},"13_raw":{"type":"keyword"},"15_raw":{"type":"keyword"},"16_raw":{"type":"keyword"},"1_raw":{"type":"keyword"},"2_raw":{"type":"keyword"},"3_raw":{"type":"keyword"},"6_raw":{"type":"keyword"},"7_raw":{"type":"keyword"},"8_raw":{"type":"keyword"},"9_raw":{"type":"keyword"},"catalog":{"type":"long"},"condition":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"currency":{"type":"long"},"feed_import_name":{"type":"text","fielddata":true},"feed_import_name_raw":{"type":"keyword"},"import_feed_name":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"import_feed_name_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"import_id":{"type":"long"},"imports":{"properties":{"1":{"properties":{"1":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"1_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}},"2":{"properties":{"1":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"15":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"47":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"15_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"1_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"47_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}},"3":{"properties":{"1":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"1_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}},"summary":{"properties":{"1":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"1_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}}}},"part_number":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"product_details_key":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"summary":{"properties":{"1":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"15":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"47":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"10_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"15_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"1_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"2_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"3_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"47_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"4_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"5_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"6_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"7_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"9_raw":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}},"user_id":{"type":"long"}}}}}

Price data is output

image

2

Which version did you migrate from? What was the mapping in this version?

What is the query you are running that is giving unexpected results? It looks like you might be sorting on 4_raw, which would sort the strings as strings )character by character from the start of the string)instead of numbers, e.g. 168 < 186.1 < 34.

3

Hi @Christian_Dahlqvist ,

Thanks for your quick reply

Previous version Elasticsearch 5.9.3 . Currently using Elasticsearch 7.9.3

    curl -X GET "localhost:9200/user_products/_search?pretty" -H 'Content-Type: application/json' -d'
    {
        "sort": [{
            "summary.4_raw.keyword": {"order": "desc"}
        }],
        "query":{
            "match": { "summary.1_raw": "581286-B21" }
        }
    }
    '
4

As you are sorting on a keyword field it uses string sorting semantics and the behaviour you are seeing is correct and expected.

5

I need to sort correct by price basics

6

Then sort on the numeric field (‘4’).

1 Like
7

Thanks for the update.

8

Numeric field ('4') not working. Please look the output.

My Query

<pre>Array
(
    [index] => user_products
    [type] => products
    [body] => Array
        (
            [query] => Array
                (
                    [bool] => Array
                        (
                            [must] => Array
                                (
                                    [0] => Array
                                        (
                                            [bool] => Array
                                                (
                                                    [should] => Array
                                                        (
                                                            [0] => Array
                                                                (
                                                                    [match_phrase] => Array
                                                                        (
                                                                            [user_id] => 1
                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                    [1] => Array
                                        (
                                            [bool] => Array
                                                (
                                                    [must] => Array
                                                        (
                                                            [0] => Array
                                                                (
                                                                    [match_phrase] => Array
                                                                        (
                                                                            [summary.1] => 581286-b21
                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                    [2] => Array
                                        (
                                            [exists] => Array
                                                (
                                                    [field] => imports
                                                )

                                        )

                                )

                        )

                )

            [track_scores] => 1
            [sort] => Array
                (
                    [summary.4.keyword] => Array
                        (
                            [order] => asc
                        )

                )

            [track_total_hits] => 1
            [size] => 100
            [from] => 0
        )

)

Asc Sort
image

Desc Sort
image

9

Probably because you are sorting on the keyword field not on the numeric field.

If you can't make it work, could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

1 Like
10

If any migrating the index from one version to another version. I tried many sort example but it didn't work in my case. Please help to out this

11

Hi @dadoonet,

Please let me know.the below code is support in new version of Elasticsearch.

Mapping

curl -XPUT 'http://localhost:9200/user_products?include_type_name=true' -H 'Content-Type: application/json' -d '{"mappings": {"products": {"properties" : {"4" : {"type" : "double"}}}}}'

Search Query

curl -X GET "localhost:9200/user_products/_search?pretty" -H 'Content-Type: application/json' -d'
    {
        "sort": [{
            "summary.4_raw.keyword": {"order": "desc"}
        }],
        "query":{
            "match": { "summary.1_raw": "581286-B21" }
        }
    }
'
12

Please. Please. Please.

Format your code. Indent it.
Create a FULL script. Make it as SIMPLE as possible. There's no need to have here so many fields. I guess that one field is enough. Add Json data. Add your search query.

That's what I meant by:

If you click on the link, you'll see an example of what I'm expecting.

And please format your code. Again, in the same link, you have an example of a well formatted question.

Thanks

13

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.