How can I query a nested document and group by a child and a parent data, with nested documents:
I'm querying this way to obtain the sum per brand and year, but I want to get
the sum by year and brand, all I try returns wrong results, empty or the sum of all reversed-nested documents:
curl -XPOST 'localhost:9200/i_part/part2/_search?pretty' -d '
{
"query": {
"bool": {
"should": [
{ "match": { "p_category":"MFGR#11"}},
{
"filtered": {
"filter": {
"nested": {
"inner_hits": {},
"path": "lineorder",
"query": {
"filtered": {
"filter": {
"bool": {
"must": [
{"match": {"lineorder.supplier.s_region":"AMERICA"}}
]
}
}
}
}
}
}
}
}
]
}
},"aggs": {
"group_by_brand": {
"terms": {
"field": "p_brand1"
},
"aggs": {
"lineorder": {
"nested": {
"path": "lineorder"
},
"aggs": {
"only_loc": {
"filter": {
"bool": {
"must": [
{"match": {"lineorder.supplier.s_region":"AMERICA"}}
]
}
},
"aggs": {
"group_by_year": {
"terms": {
"field": "lineorder.orderdate.d_year"
},
"aggs": {
"sum_revenue": {
"sum": {
"field": "lineorder.lo_revenue"
}}}
}
}
}
}
}
}
}
}, "size":0
}'
My mapping is something like this, there's a part whith some properties
and inside a array of lineorders (nested) and inside each lineorder
there are customer, supplier and orderdate (not nested)(one to one):
curl -XPUT 'localhost:9200/i_part' -d '
{
"mappings": {
"part2": {
"properties": {
"p_name": {"type":"string", "index":"not_analyzed" },
"p_category": {"type":"string", "index":"not_analyzed" },
"p_brand1": {"type":"string", "index":"not_analyzed" },
"lineorder": {
"type": "nested",
"properties": {
"lo_quantity": {"type":"integer"},
"lo_discount": {"type":"integer"},
"lo_revenue": {"type":"integer"},
"lo_shippriority": {"type": "string", "index": "not_analyzed"},
"lo_shipmode": {"type": "string", "index": "not_analyzed"},
"customer"{
"properties":{
"c_name": {"type": "string", "index": "not_analyzed"}
}
}
"supplier"{
"properties":{
"s_name": {"type": "string", "index": "not_analyzed"}
"s_region": {"type": "string", "index": "not_analyzed"}
}
}
"orderdate"{
"properties":{
"d_date": {"type": "integer"}
"d_year": {"type": "integer"}
}
}
}
}
}
}
}