I'm trying to sort some products by child nodes. I have products, for each product has child node "stock" (contains productId (join field), stockId, stockQuantity, stockOnTheWay...) there can be multiple child nodes and I need to sum product stockQuantity from all stocks and sort by them. But as I learned, elasticsearch queries for sorting is difficult task.
I've tried to search in Google for similar problems and all I got from google was to use script and factor. But I doesn't know how to work with these solutions, in query like this.
mappings:
"product": {
"_routing": {
"required": true
},
"properties": {
"stockAgingMax": {
"type": "integer"
},
"transitDate": {
"type": "integer"
},
"productId": {
"type": "keyword"
},
"created": {
"type": "date"
},
"lotNum": {
"type": "keyword"
},
"categoryCode": {
"type": "keyword"
},
"vendorName": {
"fielddata": true,
"type": "text"
},
"categoryName": {
"type": "text"
},
"stocks": {
"type": "nested",
"properties": {
"stockOnHand": {
"type": "integer"
},
"stockQuantity": {
"type": "integer"
},
"stock": {
"type": "integer"
},
"stockOnTheWay": {
"type": "integer"
}
}
},
"vendorCode": {
"type": "keyword"
},
"myJoinField": {
"eager_global_ordinals": true,
"type": "join",
"relations": {
"productFullId": "stock"
}
},
"productFullId": {
"type": "keyword"
},
"stockAgingY": {
"type": "double"
},
"name": {
"search_analyzer": "searchTextAnalyzer",
"fielddata": true,
"analyzer": "nGramAnalyzer",
"type": "text"
},
"id": {
"type": "text",
"fields": {
"keyword": {
"ignore_above": 256,
"type": "keyword"
}
}
}
}
}
}
request:
"from": 0,
"query": {
"bool": {
"should": [
{
"has_child": {
"inner_hits": {},
"query": {
"match_all": {}
},
"type": "stock"
}
}
]
}
},
"size": 3
}
response:
{
"hits": [
{
"_index": "catalog_test",
"_type": "product",
"_id": "1051230_",
"_score": 1,
"_routing": "1051230_",
"_source": {
"id": "1051230_",
"myJoinField": "productFullId",
"productId": 1051230,
"lotNum": "",
"productFullId": "1051230_"
},
"inner_hits": {
"stock": {
"hits": {
"total": 1,
"max_score": 1,
"hits": [
{
"_index": "catalog_test",
"_type": "product",
"_id": "1051230__stock",
"_score": 1,
"_routing": "1051230_",
"_source": {
"stocks": [
{
"stockQuantity": 1,
"stockOnTheWay": 0,
"stockOnHand": 1,
"stock": 1030
},
{
"stockQuantity": 0,
"stockOnTheWay": 0,
"stockOnHand": 0,
"stock": 1031
}
],
"stockAgingY": 0,
"stockAgingMax": 60,
"transitDate": 0,
"created": "2019-08-02T17:40:20.1859615+03:00",
"id": "1051230__stock",
"myJoinField": {
"name": "stock",
"parent": "1051230_"
},
"productId": 1051230,
"lotNum": "",
"productFullId": "1051230_"
}
}
]
}
}
}
},
{
"_index": "catalog_test",
"_type": "product",
"_id": "1051231_",
"_score": 1,
"_routing": "1051231_",
"_source": {
"id": "1051231_",
"myJoinField": "productFullId",
"productId": 1051231,
"lotNum": "",
"productFullId": "1051231_"
},
"inner_hits": {
"stock": {
"hits": {
"total": 1,
"max_score": 1,
"hits": [
{
"_index": "catalog_test",
"_type": "product",
"_id": "1051231__stock",
"_score": 1,
"_routing": "1051231_",
"_source": {
"stocks": [
{
"stockQuantity": 0,
"stockOnTheWay": 119214,
"stockOnHand": 0,
"stock": 1030
},
{
"stockQuantity": 0,
"stockOnTheWay": 0,
"stockOnHand": 0,
"stock": 1031
}
],
"stockAgingY": 0,
"stockAgingMax": 0,
"transitDate": 119214,
"created": "2019-08-02T17:40:20.1859672+03:00",
"id": "1051231__stock",
"myJoinField": {
"name": "stock",
"parent": "1051231_"
},
"productId": 1051231,
"lotNum": "",
"productFullId": "1051231_"
}
}
]
}
}
}
},
{
"_index": "catalog_test",
"_type": "product",
"_id": "1053682_",
"_score": 1,
"_routing": "1053682_",
"_source": {
"id": "1053682_",
"myJoinField": "productFullId",
"productId": 1053682,
"lotNum": "",
"productFullId": "1053682_"
},
"inner_hits": {
"stock": {
"hits": {
"total": 1,
"max_score": 1,
"hits": [
{
"_index": "catalog_test",
"_type": "product",
"_id": "1053682__stock",
"_score": 1,
"_routing": "1053682_",
"_source": {
"stocks": [
{
"stockQuantity": 2,
"stockOnTheWay": 0,
"stockOnHand": 2,
"stock": 1030
},
{
"stockQuantity": 0,
"stockOnTheWay": 0,
"stockOnHand": 0,
"stock": 1031
}
],
"stockAgingY": 2,
"stockAgingMax": 0,
"transitDate": 0,
"created": "2019-08-02T17:40:20.1859778+03:00",
"id": "1053682__stock",
"myJoinField": {
"name": "stock",
"parent": "1053682_"
},
"productId": 1053682,
"lotNum": "",
"productFullId": "1053682_"
}
}
]
}
}
}
}
]
}
in this example I wanted to product with id 1053682 be first(not 3rd) because it has value 2 in stockQuantity param. But right now I have no idea how to do that.
elasticsearch version: 6.3.2