Elastic search - group by query on array

Elastic Stack Elasticsearch
1

Hi,

I am working on elasticsearch for last 2 months. It is really providing
awesome searching capabilities, good json structure documents etc...
Currently I am stuck up with the problem on How to write group by query and
get the data.

Ex:- In this example company, prod_type are defined as 'not_analyzed'

Example Documents:

{"company":"ABC","orders":[{"order_no":"OL1", "prod_type" : "OLP",
"price":20}, {"order_no":"OL2", "prod_type" : "OLP", "price":50},
{"order_no":"OL3", "prod_type" : "GLP", "price":100} ]}

{"company":"XYZ","orders":[{"order_no":"OL10", "prod_type" : "GLP",
"price":50}, {"order_no":"OL20", "prod_type" : "OLP", "price":80},
{"order_no":"OL30", "prod_type" : "GLP", "price":100} ]}

My Requirement: I want the elasticsearch query to get the count, sum(price)
based on prod_type
SQL Comparision Qry: SELECT COUNT(*), SUM(PRICE) FROM TABLE_NAME GROUP BY
PROD_TYPE

Can anyone please help me this?

Please let me know if you need more information.

Thanks,
Samanth

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

Hi Samanth ,

First you will need to make that array into nested type. -

Then you need to do a 2 level agg with term aggregation at parent on field
prod_type and sum aggregation on price field.

Term aggregation -

Sum aggregation -

Thanks
Vineeth

On Tue, Jul 8, 2014 at 4:23 PM, K.Samanth Kumar Reddy <
samanthkumar.k@gmail.com> wrote:

Hi,

I am working on elasticsearch for last 2 months. It is really providing
awesome searching capabilities, good json structure documents etc...
Currently I am stuck up with the problem on How to write group by query
and get the data.

Ex:- In this example company, prod_type are defined as 'not_analyzed'

Example Documents:

{"company":"ABC","orders":[{"order_no":"OL1", "prod_type" : "OLP",
"price":20}, {"order_no":"OL2", "prod_type" : "OLP", "price":50},
{"order_no":"OL3", "prod_type" : "GLP", "price":100} ]}

{"company":"XYZ","orders":[{"order_no":"OL10", "prod_type" : "GLP",
"price":50}, {"order_no":"OL20", "prod_type" : "OLP", "price":80},
{"order_no":"OL30", "prod_type" : "GLP", "price":100} ]}

My Requirement: I want the elasticsearch query to get the count,
sum(price) based on prod_type
SQL Comparision Qry: SELECT COUNT(*), SUM(PRICE) FROM TABLE_NAME GROUP BY
PROD_TYPE

Can anyone please help me this?

Please let me know if you need more information.

Thanks,
Samanth

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/227ad9cc-0327-48de-9df9-bc0c159d9250%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAGdPd5n1PKoXYgg0Y9y-6K%3DBN3UX920DYV%2BEpv9wGgPZZOgxsg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

3

Hi Vineeth,

Thank you very much. I will try and let you know.

Thanks,
Samanth

On Tuesday, July 8, 2014 4:23:18 PM UTC+5:30, K.Samanth Kumar Reddy wrote:

Hi,

I am working on elasticsearch for last 2 months. It is really providing
awesome searching capabilities, good json structure documents etc...
Currently I am stuck up with the problem on How to write group by query
and get the data.

Ex:- In this example company, prod_type are defined as 'not_analyzed'

Example Documents:

{"company":"ABC","orders":[{"order_no":"OL1", "prod_type" : "OLP",
"price":20}, {"order_no":"OL2", "prod_type" : "OLP", "price":50},
{"order_no":"OL3", "prod_type" : "GLP", "price":100} ]}

{"company":"XYZ","orders":[{"order_no":"OL10", "prod_type" : "GLP",
"price":50}, {"order_no":"OL20", "prod_type" : "OLP", "price":80},
{"order_no":"OL30", "prod_type" : "GLP", "price":100} ]}

My Requirement: I want the elasticsearch query to get the count,
sum(price) based on prod_type
SQL Comparision Qry: SELECT COUNT(*), SUM(PRICE) FROM TABLE_NAME GROUP BY
PROD_TYPE

Can anyone please help me this?

Please let me know if you need more information.

Thanks,
Samanth

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/d02a3c0b-48f6-4be3-b4b9-a4f54c4f4fcd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

4

Thank you very much. Its working.

Thanks,
Samanth

On Tuesday, July 8, 2014 4:23:18 PM UTC+5:30, K.Samanth Kumar Reddy wrote:

Hi,

I am working on elasticsearch for last 2 months. It is really providing
awesome searching capabilities, good json structure documents etc...
Currently I am stuck up with the problem on How to write group by query
and get the data.

Ex:- In this example company, prod_type are defined as 'not_analyzed'

Example Documents:

{"company":"ABC","orders":[{"order_no":"OL1", "prod_type" : "OLP",
"price":20}, {"order_no":"OL2", "prod_type" : "OLP", "price":50},
{"order_no":"OL3", "prod_type" : "GLP", "price":100} ]}

{"company":"XYZ","orders":[{"order_no":"OL10", "prod_type" : "GLP",
"price":50}, {"order_no":"OL20", "prod_type" : "OLP", "price":80},
{"order_no":"OL30", "prod_type" : "GLP", "price":100} ]}

My Requirement: I want the elasticsearch query to get the count,
sum(price) based on prod_type
SQL Comparision Qry: SELECT COUNT(*), SUM(PRICE) FROM TABLE_NAME GROUP BY
PROD_TYPE

Can anyone please help me this?

Please let me know if you need more information.

Thanks,
Samanth

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/c1ca78ae-24ad-4afa-bbcb-9d17bb0f6fcf%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

5

Hi Samanth,

I have started working on elasticsearch recently. I'm trying to get some
result exactly like what you have tried. But the problem is I'm getting sum
value same for all buckets ( Total price of the document where the prod
type exists )

For your example the search json I wrote is

{
"aggs": {
"prod_type": {
"terms": {
"field": "orders.prod_type"
},
"aggs": {
"total_price": {
"sum": {
"field": "price"
}
}
}
}
}
}

The result I got is

"aggregations": {
"prod_type": {
"buckets": [
{
"key": "glp",
"doc_count": 2,
"total_price": {
"value": 400
}
},
{
"key": "olp",
"doc_count": 2,
"total_price": {
"value": 400
}
}
]
}
}

Could you please help me out in this?

Regards,
Raja

On Wednesday, July 9, 2014 9:21:11 AM UTC+5:30, K.Samanth Kumar Reddy wrote:

Thank you very much. Its working.

Thanks,
Samanth

On Tuesday, July 8, 2014 4:23:18 PM UTC+5:30, K.Samanth Kumar Reddy wrote:

Hi,

I am working on elasticsearch for last 2 months. It is really providing
awesome searching capabilities, good json structure documents etc...
Currently I am stuck up with the problem on How to write group by query
and get the data.

Ex:- In this example company, prod_type are defined as 'not_analyzed'

Example Documents:

{"company":"ABC","orders":[{"order_no":"OL1", "prod_type" : "OLP",
"price":20}, {"order_no":"OL2", "prod_type" : "OLP", "price":50},
{"order_no":"OL3", "prod_type" : "GLP", "price":100} ]}

{"company":"XYZ","orders":[{"order_no":"OL10", "prod_type" : "GLP",
"price":50}, {"order_no":"OL20", "prod_type" : "OLP", "price":80},
{"order_no":"OL30", "prod_type" : "GLP", "price":100} ]}

My Requirement: I want the elasticsearch query to get the count,
sum(price) based on prod_type
SQL Comparision Qry: SELECT COUNT(*), SUM(PRICE) FROM TABLE_NAME GROUP BY
PROD_TYPE

Can anyone please help me this?

Please let me know if you need more information.

Thanks,
Samanth

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/192cf64d-c704-4dc5-ac49-f896c10b3e26%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.