Our use case is having a list of products and in a nested field have the last purchase date by customer number. When a customer searches for products we want to return all items that match their search term. The inner_hits should only include the record for that customer, if there is a record at all. So if a customer searches for corn they would get 4 results potentially but only 2 of them they may have purchased before. So 2 documents would have no inner hits, and the other 2 would have inner hits of 1 document only, that customers last purchase.
Most items will have hundreds of customer purchases loaded into the nested field so we do not want to return all of those records when we only care about the last purchase date of the customer that is searching.
The current query we are testing with
{
"_source": {
"includes": ["*"],
"excludes": ["c_purchases"]
},
"query": {
"bool": {
"must": [
{
"match": {
"i_description": "corn"
}
},
{
"nested" : {
"path" : "c_purchases",
"inner_hits": {
"_source": ["*"]
},
"query" : {
"match": {
"c_purchases.customernumber": "1111"
}
}
}
}
]
}
}
}
The issue with the above query is it only returns items the customer bought. So in the example it would only return the 2 with a nested record in c_purchases vs all 4 corn items.
Current Results
{
"hits": {
"total": {
"value": 2
},
"hits": [
{
"_source": {
"i_description": "corn 1",
"i_code": "111111"
},
"inner_hits": {
"c_purchases": {
"hits": {
"total": {
"value": 1,
},
"hits": [
{
"_source": {
"customernumber": "100",
"lastordered": "2018-01-30T00:00:00",
}
}
]
}
}
}
},
{
"_source": {
"i_description": "corn 2",
"i_code": "222222"
},
"inner_hits": {
"c_purchases": {
"hits": {
"total": {
"value": 1,
},
"hits": [
{
"_source": {
"customernumber": "100",
"lastordered": "2018-01-30T00:00:00",
}
}
]
}
}
}
}
]
}
}
Expected Results
{
"hits": {
"total": {
"value": 2
},
"hits": [
{
"_source": {
"i_description": "corn 1",
"i_code": "111111"
},
"inner_hits": {
"c_purchases": {
"hits": {
"total": {
"value": 1,
},
"hits": [
{
"_source": {
"customernumber": "100",
"lastordered": "2018-01-30T00:00:00",
}
}
]
}
}
}
},
{
"_source": {
"i_description": "corn 2",
"i_code": "222222"
},
"inner_hits": {
"c_purchases": {
"hits": {
"total": {
"value": 1,
},
"hits": [
{
"_source": {
"customernumber": "100",
"lastordered": "2018-01-30T00:00:00",
}
}
]
}
}
}
},
{
"_source": {
"i_description": "corn 3",
"i_code": "333333"
}
},
{
"_source": {
"i_description": "corn 4",
"i_code": "444444"
}
}
]
}
}
Thanks in advance!