ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

K.Samanth Kumar Reddy
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 [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

vineeth mohan-2
Hi Samanth ,



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


Thanks
          Vineeth


On Tue, Jul 8, 2014 at 4:23 PM, K.Samanth Kumar Reddy <[hidden email]> 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 [hidden email].
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.

--
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 [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

K.Samanth Kumar Reddy
In reply to this post by K.Samanth Kumar Reddy
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 [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

K.Samanth Kumar Reddy
In reply to this post by K.Samanth Kumar Reddy
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 [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

Raja Sekhar Bhamidipati
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 [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

petitout
This post has NOT been accepted by the mailing list yet.
Hi Raja,
have you resolved your problem ?
I have the exact same issue but I cannot find the solution.
Thank you very much
Reply | Threaded
Open this post in threaded view
|

Re: ELASTIC SEARCH - GROUP BY QUERY ON ARRAY

Tri H Nguyen
This post has NOT been accepted by the mailing list yet.
In reply to this post by Raja Sekhar Bhamidipati
If you change from "price" to "orders.price", it may help.

PS: I've not loaded and tested the data... just looking at the content.