Flattened Data Type Numeric Sorting

Hi Team

I am working on one of the projects where I need the different price combinations for each product and since we do not have control over the number of combinations and limitation of the number of fields(1000 fields per doc) and to avoid 'mapping explosion' issue, we planned to go with 'flattened' data type field.

The current mappings looks like this,

"demo" : {

"mappings" : {

  "properties" : {

    "category_ids" : {

      "type" : "long"

    },

    "my_material" : {

      "type" : "text",

      "fields" : {

        "keyword" : {

          "type" : "keyword",

          "ignore_above" : 256

        }

      }

    },

    "price_combination" : {

      "type" : "flattened"

    },

    "shipto_ids" : {

      "type" : "long"

    },

    "sku" : {

      "type" : "text",

      "fields" : {

        "keyword" : {

          "type" : "keyword",

          "ignore_above" : 256

        }

      }

    },

    "soldto_ids" : {

      "type" : "long"

    },

    "store_id" : {

      "type" : "long"

    }

  }

}

}

} ` and the single document with 'price_combination' looks like this,

"store_id" : 1, "sku" : "sku4", "category_ids" : [ 2, 5 ], "soldto_ids" : [ 2, 5 ], "shipto_ids" : [ 4, 9 ], "my_material" : ["a","b"],

"price_combination" : {

"price_soldto2_ship4" :1.000000,

"price_soldto2_ship9" :10.000000,

"price_soldto5_ship4" :20.000000,

"price_soldto5_ship9" :5.000000

}

} `

The main agenda is to do 'sort', 'aggregation' and 'range' queries on these price variations, as per the doc https://www.elastic.co/guide/en/elasticsearch/reference/current/flattened.html#flattened, for the aggregation it supports only simple 'term' clause but it should work with a sort operation

Now the problem is whenever I do a 'sort' operation on a specific price combination, it does not work, I get random results, and the query is

`GET demo/_search

{

"sort":[

{

"price_combination.price_soldto5_ship4":{

"order":"asc"

}

}

"query": {

 "bool": {

  "must":[

        {

          "term":{

          "soldto_ids":"2"

          }

        },

        {

        "term":{

          "shipto_ids":"9"

          }

        }

  ]

}

}

Can you please guide me on this,

is the flattened approach a better approach? will bet there be an any better approach than flattened data type?
if yes, why the sort operation is not working as expected?

Please let me know If I miss anything, your inputs are really appreciable, thanks in advance.

elastic / elasticsearch-php

Flattened Data Type Numeric Sorting #1264