# Order by child-parent atributes on nested documents
**URL:** https://discuss.elastic.co/t/order-by-child-parent-atributes-on-nested-documents/45828
**Category:** Elasticsearch
**Created:** [March 30, 2016, 4:29pm UTC](https://discuss.elastic.co/t/order-by-child-parent-atributes-on-nested-documents/45828 "2016-03-30T16:29:50Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@Raphael](https://discuss.elastic.co/u/Raphael)
#### Post date: [March 30, 2016, 4:29pm UTC](https://discuss.elastic.co/t/order-by-child-parent-atributes-on-nested-documents/45828/1 "2016-03-30T16:29:50Z")
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"}
}
}
}
}
}
}
}
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 5, 2017, 11:04pm UTC](https://discuss.elastic.co/t/order-by-child-parent-atributes-on-nested-documents/45828/2 "2017-07-05T23:04:00Z")