# Elasticsearch - count users query **URL:** \ **Category:** Elasticsearch\ **Created:** [June 27, 2013, 3:21pm UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564 "2013-06-27T15:21:14Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![oreno](https://avatars.discourse-cdn.com/v4/letter/o/b4bc9f/32.png) [@oreno](https://discuss.elastic.co/u/oreno)\ **Post date:** [June 27, 2013, 3:21pm UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564/1 "2013-06-27T15:21:14Z")
Hi All, I have a problem that I hope someone can help me with: I have the next mapping for my user index -\> docs(users) containing events in which the user bought different products. each event(date) can contain several products. I would like to retrieve the count of users that their last purchase of TV was TOSHIBA. for that I am willing to have a 'TV' as a product by itself. So in case a have the next events: user1|2013-05-18|TV,SAMSUNG user1|2013-05-19|TV,TOSHIBA user1|2013-05-20|PC user2|2013-05-18|TV,TOSHIBA user2|2013-05-19|TV,SAMSUNG user2|2013-05-20|PC user1 should be counted because its last bought TV was TOSHIBA but user2 should not. Does anyone have an idea how to approach this query? I tried several times but I'm new to elasticsearch so no success yet. Thanks in advanced, ``` { ``` "user" : { "properties" : { "name": {"type" : "string"}, "events" : { "type" : "nested", "properties" : { "event\_time" : {"type" : "Date"}, "products" : { "properties" : { "product" : {"type" : "string"} } } } } } } } ---
**Author:** ![radu\_gheorghe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/radu_gheorghe/32/556_2.png) [@radu\_gheorghe](https://discuss.elastic.co/u/radu_gheorghe)\ **Post date:** [June 28, 2013, 12:40pm UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564/2 "2013-06-28T12:40:57Z")
Hello, I don't see a clean way of doing this. But you can pay some prices, either at query or at index time, to obtain this. For example, at query time, you can try to get a list of unique users by doing a terms facet[http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/)on the user field. Then, you can do a multi search [http://www.elasticsearch.org/guide/reference/api/multi-search/](http://www.elasticsearch.org/guide/reference/api/multi-search/) for all the users to see what was the last thing they bought. Finally, you can count on the application side which of those was a Toshiba TV. Very slow and doesn't scale, but you can do anything. On the other hand, if you have a predefined list of such queries, you can a separate index (eg: last\_item\_bought) for each query. For example, with this one, you can have an index where, each time a use buys something, you update the document with their name as the ID, and the item they bought. It shouldn't hurt indexing performance too much. When you want to search, you just do a filter on "tv" and "toshiba", with size=0 and see the number of hits. Best regards, Radu On Thu, Jun 27, 2013 at 6:21 PM, oreno [oreno@exelate.com](mailto:oreno@exelate.com) wrote: > Hi All, > I have a problem that I hope someone can help me with: > > I have the next mapping for my user index -\> docs(users) containing events > in which the user bought different products. each event(date) can contain > several products. I would like to retrieve the count of users that their > last purchase of TV was TOSHIBA. for that I am willing to have a 'TV' as a > product by itself. > > So in case a have the next events: > > user1|2013-05-18|TV,SAMSUNG > user1|2013-05-19|TV,TOSHIBA > user1|2013-05-20|PC > user2|2013-05-18|TV,TOSHIBA > user2|2013-05-19|TV,SAMSUNG > user2|2013-05-20|PC > user1 should be counted because its last bought TV was TOSHIBA but user2 > should not. > > Does anyone have an idea how to approach this query? > > I tried several times but I'm new to elasticsearch so no success yet. > > Thanks in advanced, > > ``` > { > > ``` > > "user" : { > "properties" : { > "name": {"type" : "string"}, > "events" : { > "type" : "nested", > "properties" : { > "event\_time" : {"type" : "Date"}, > "products" : { "properties" : { "product" : {"type" : "string"} } } > } > } > } > } > } > > -- > View this message in context: > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > -- > 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](mailto:elasticsearch+unsubscribe@googlegroups.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene -- 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](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![oreno](https://avatars.discourse-cdn.com/v4/letter/o/b4bc9f/32.png) [@oreno](https://discuss.elastic.co/u/oreno)\ **Post date:** [June 28, 2013, 9:34pm UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564/3 "2013-06-28T21:34:25Z")
Hi Radu, thank you for taking the time to assist me with this problem. Since the search time is highly important, my first choice would have been to use the second option that you mentioned, and use a separate index for each product such as TV. unfortunately, there is another restriction, that makes the problem even more complex. I need to be able to set a date range for the query. For example, if again I am looking for the user count of TOSHIBA buyers at 18 - 20 of May, I should get count=1 (as before).But if my range is 18 -21 of May (as appear below), I should get count = 0, because at that range, there is no user that his last TV was TOSHIBA. So as you can see, creating an user\_tv\_index that gets overridden on each TV bought is a problem, because I will still need the info for old actions. Do you think that there is still a good way of retrieving this information without slowing the search time? Thanks in advanced. I highly appreciate your help. Oren user1|2013-05-18|TV,SAMSUNG user1|2013-05-19|TV,TOSHIBA user1|2013-05-20|PC user2|2013-05-18|TV,TOSHIBA user2|2013-05-19|TV,SAMSUNG user2|2013-05-20|PC _user1|2013-05-21|TV,SAMSUNG_ On Fri, Jun 28, 2013 at 3:41 PM, Radu Gheorghe-2 [via Elasticsearch Users] \< [ml-node+s115913n4037241h6@n3.nabble.com](mailto:ml-node+s115913n4037241h6@n3.nabble.com)\> wrote: > Hello, > > I don't see a clean way of doing this. But you can pay some prices, either > at query or at index time, to obtain this. > > For example, at query time, you can try to get a list of unique users by > doing a terms facet[http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/)on the user field. Then, you can do a multi > search [http://www.elasticsearch.org/guide/reference/api/multi-search/](http://www.elasticsearch.org/guide/reference/api/multi-search/)for all the users to see what was the last thing they bought. Finally, you > can count on the application side which of those was a Toshiba TV. Very > slow and doesn't scale, but you can do anything. > > On the other hand, if you have a predefined list of such queries, you can > a separate index (eg: last\_item\_bought) for each query. For example, with > this one, you can have an index where, each time a use buys something, you > update the document with their name as the ID, and the item they bought. It > shouldn't hurt indexing performance too much. When you want to search, you > just do a filter on "tv" and "toshiba", with size=0 and see the number of > hits. > > Best regards, > Radu > > On Thu, Jun 27, 2013 at 6:21 PM, oreno \<[hidden email][http://user/SendEmail.jtp?type=node&node=4037241&i=0](http://user/SendEmail.jtp?type=node&node=4037241&i=0) > > > wrote: > > > Hi All, > > I have a problem that I hope someone can help me with: > > > > I have the next mapping for my user index -\> docs(users) containing events > > in which the user bought different products. each event(date) can contain > > several products. I would like to retrieve the count of users that their > > last purchase of TV was TOSHIBA. for that I am willing to have a 'TV' as a > > product by itself. > > > > So in case a have the next events: > > > > user1|2013-05-18|TV,SAMSUNG > > user1|2013-05-19|TV,TOSHIBA > > user1|2013-05-20|PC > > user2|2013-05-18|TV,TOSHIBA > > user2|2013-05-19|TV,SAMSUNG > > user2|2013-05-20|PC > > user1 should be counted because its last bought TV was TOSHIBA but user2 > > should not. > > > > Does anyone have an idea how to approach this query? > > > > I tried several times but I'm new to elasticsearch so no success yet. > > > > Thanks in advanced, > > > > ``` > > { > > > > ``` > > > > "user" : { > > "properties" : { > > "name": {"type" : "string"}, > > "events" : { > > "type" : "nested", > > "properties" : { > > "event\_time" : {"type" : "Date"}, > > "products" : { "properties" : { "product" : {"type" : "string"} } > > } > > } > > } > > } > > } > > } > > > > -- > > View this message in context: > > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html) > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > > > -- > > 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][http://user/SendEmail.jtp?type=node&node=4037241&i=1](http://user/SendEmail.jtp?type=node&node=4037241&i=1) > > . > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > -- > [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene > > -- > 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][http://user/SendEmail.jtp?type=node&node=4037241&i=2](http://user/SendEmail.jtp?type=node&node=4037241&i=2) > . > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > * * * > > If you reply to this email, your message will be added to the discussion > below: > > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037241.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037241.html) > To unsubscribe from elasticsearch - count users query, click here[http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe\_by\_code&node=4037172&code=b3Jlbm9AZXhlbGF0ZS5jb218NDAzNzE3Mnw4ODk1Mjk2Nzg=](http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4037172&code=b3Jlbm9AZXhlbGF0ZS5jb218NDAzNzE3Mnw4ODk1Mjk2Nzg=) > . > NAML[http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro\_viewer&id=instant\_html!nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify\_subscribers!nabble%3Aemail.naml-instant\_emails!nabble%3Aemail.naml-send\_instant\_email!nabble%3Aemail.naml](http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml) -- _Oren Orgad_ _eXelate_ software engineer _p:_ +972- 72-2221329 | _m:_ +972- 52-3401051 | _e:_ [oreno@exelate.com](mailto:oreno@exelate.com) ---
**Author:** ![radu\_gheorghe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/radu_gheorghe/32/556_2.png) [@radu\_gheorghe](https://discuss.elastic.co/u/radu_gheorghe)\ **Post date:** [June 30, 2013, 1:52pm UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564/4 "2013-06-30T13:52:48Z")
Hi Oren, Unfortunately, I don't see a way of indexing your documents so you can get what you want in a fast way. But maybe someone else can find a way... Best regards, Radu On Sat, Jun 29, 2013 at 12:34 AM, oreno [oreno@exelate.com](mailto:oreno@exelate.com) wrote: > Hi Radu, > thank you for taking the time to assist me with this problem. > Since the search time is highly important, my first choice would have been > to use the second option that you mentioned, and > use a separate index for each product such as TV. > > unfortunately, there is another restriction, that makes the problem even > more complex. > I need to be able to set a date range for the query. > For example, if again I am looking for the user count of TOSHIBA buyers at > 18 - 20 of May, I should get count=1 (as before).But if my range is 18 -21 > of May (as appear below), I should get count = 0, because at that range, > there is no user that his last TV was TOSHIBA. > > So as you can see, creating an user\_tv\_index that gets overridden on each > TV bought is a problem, because I will still need the info for old actions. > > Do you think that there is still a good way of retrieving this information > without slowing the search time? > > Thanks in advanced. I highly appreciate your help. > > Oren > > user1|2013-05-18|TV,SAMSUNG > user1|2013-05-19|TV,TOSHIBA > user1|2013-05-20|PC > user2|2013-05-18|TV,TOSHIBA > user2|2013-05-19|TV,SAMSUNG > user2|2013-05-20|PC > _user1|2013-05-21|TV,SAMSUNG_ > > On Fri, Jun 28, 2013 at 3:41 PM, Radu Gheorghe-2 [via Elasticsearch Users] > \<[hidden email] [http://user/SendEmail.jtp?type=node&node=4037265&i=0](http://user/SendEmail.jtp?type=node&node=4037265&i=0)\>wrote: > > > Hello, > > > > I don't see a clean way of doing this. But you can pay some prices, > > either at query or at index time, to obtain this. > > > > For example, at query time, you can try to get a list of unique users by > > doing a terms facet[http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/)on the user field. Then, you can do a multi > > search [http://www.elasticsearch.org/guide/reference/api/multi-search/](http://www.elasticsearch.org/guide/reference/api/multi-search/)for all the users to see what was the last thing they bought. Finally, you > > can count on the application side which of those was a Toshiba TV. Very > > slow and doesn't scale, but you can do anything. > > > > On the other hand, if you have a predefined list of such queries, you can > > a separate index (eg: last\_item\_bought) for each query. For example, with > > this one, you can have an index where, each time a use buys something, you > > update the document with their name as the ID, and the item they bought. It > > shouldn't hurt indexing performance too much. When you want to search, you > > just do a filter on "tv" and "toshiba", with size=0 and see the number of > > hits. > > > > Best regards, > > Radu > > > > On Thu, Jun 27, 2013 at 6:21 PM, oreno \<[hidden email][http://user/SendEmail.jtp?type=node&node=4037241&i=0](http://user/SendEmail.jtp?type=node&node=4037241&i=0) > > > > > wrote: > > > > > Hi All, > > > I have a problem that I hope someone can help me with: > > > > > > I have the next mapping for my user index -\> docs(users) containing > > > events > > > in which the user bought different products. each event(date) can contain > > > several products. I would like to retrieve the count of users that their > > > last purchase of TV was TOSHIBA. for that I am willing to have a 'TV' as > > > a > > > product by itself. > > > > > > So in case a have the next events: > > > > > > user1|2013-05-18|TV,SAMSUNG > > > user1|2013-05-19|TV,TOSHIBA > > > user1|2013-05-20|PC > > > user2|2013-05-18|TV,TOSHIBA > > > user2|2013-05-19|TV,SAMSUNG > > > user2|2013-05-20|PC > > > user1 should be counted because its last bought TV was TOSHIBA but user2 > > > should not. > > > > > > Does anyone have an idea how to approach this query? > > > > > > I tried several times but I'm new to elasticsearch so no success yet. > > > > > > Thanks in advanced, > > > > > > ``` > > > { > > > > > > ``` > > > > > > "user" : { > > > "properties" : { > > > "name": {"type" : "string"}, > > > "events" : { > > > "type" : "nested", > > > "properties" : { > > > "event\_time" : {"type" : "Date"}, > > > "products" : { "properties" : { "product" : {"type" : "string"} > > > } } > > > } > > > } > > > } > > > } > > > } > > > > > > -- > > > View this message in context: > > > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html) > > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > > > > > -- > > > 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][http://user/SendEmail.jtp?type=node&node=4037241&i=1](http://user/SendEmail.jtp?type=node&node=4037241&i=1) > > > . > > > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > -- > > [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene > > > > -- > > 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][http://user/SendEmail.jtp?type=node&node=4037241&i=2](http://user/SendEmail.jtp?type=node&node=4037241&i=2) > > . > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > * * * > > > > If you reply to this email, your message will be added to the > > discussion below: > > > > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037241.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037241.html) > > To unsubscribe from elasticsearch - count users query, click here. > > NAML[http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro\_viewer&id=instant\_html!nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify\_subscribers!nabble%3Aemail.naml-instant\_emails!nabble%3Aemail.naml-send\_instant\_email!nabble%3Aemail.naml](http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml) > > -- > > _Oren Orgad_ > > _eXelate_ > software engineer > > _p:_ +972- 72-2221329 | _m:_ +972- 52-3401051 | _e:_ [hidden email][http://user/SendEmail.jtp?type=node&node=4037265&i=1](http://user/SendEmail.jtp?type=node&node=4037265&i=1) > > * * * > > View this message in context: Re: elasticsearch - count users query[http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037265.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037265.html) > > Sent from the Elasticsearch Users mailing list archive[http://elasticsearch-users.115913.n3.nabble.com/](http://elasticsearch-users.115913.n3.nabble.com/)at [Nabble.com](http://Nabble.com). > > -- > 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](mailto:elasticsearch+unsubscribe@googlegroups.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene -- 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](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![oreno](https://avatars.discourse-cdn.com/v4/letter/o/b4bc9f/32.png) [@oreno](https://discuss.elastic.co/u/oreno)\ **Post date:** [June 30, 2013, 2:26pm UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564/5 "2013-06-30T14:26:34Z")
Thanks Radu, I appreciate your effort here. I'm now looking into native script option for running java based logic after running a more basic query(that will just return the documents containing the range I need and contain the TV string). is that what you meant by running on application side? do you think I will pay a big price for this way of searching? Thanks in advanced, Oren On Sun, Jun 30, 2013 at 4:53 PM, Radu Gheorghe-2 [via Elasticsearch Users] \< [ml-node+s115913n4037282h77@n3.nabble.com](mailto:ml-node+s115913n4037282h77@n3.nabble.com)\> wrote: > Hi Oren, > > Unfortunately, I don't see a way of indexing your documents so you can get > what you want in a fast way. But maybe someone else can find a way... > > Best regards, > Radu > > On Sat, Jun 29, 2013 at 12:34 AM, oreno \<[hidden email][http://user/SendEmail.jtp?type=node&node=4037282&i=0](http://user/SendEmail.jtp?type=node&node=4037282&i=0) > > > wrote: > > > Hi Radu, > > thank you for taking the time to assist me with this problem. > > Since the search time is highly important, my first choice would have > > been to use the second option that you mentioned, and > > use a separate index for each product such as TV. > > > > unfortunately, there is another restriction, that makes the problem even > > more complex. > > I need to be able to set a date range for the query. > > For example, if again I am looking for the user count of TOSHIBA buyers > > at 18 - 20 of May, I should get count=1 (as before).But if my range is 18 > > -21 of May (as appear below), I should get count = 0, because at that > > range, there is no user that his last TV was TOSHIBA. > > > > So as you can see, creating an user\_tv\_index that gets overridden on each > > TV bought is a problem, because I will still need the info for old actions. > > > > Do you think that there is still a good way of retrieving this > > information without slowing the search time? > > > > Thanks in advanced. I highly appreciate your help. > > > > Oren > > > > user1|2013-05-18|TV,SAMSUNG > > user1|2013-05-19|TV,TOSHIBA > > user1|2013-05-20|PC > > user2|2013-05-18|TV,TOSHIBA > > user2|2013-05-19|TV,SAMSUNG > > user2|2013-05-20|PC > > _user1|2013-05-21|TV,SAMSUNG_ > > > > On Fri, Jun 28, 2013 at 3:41 PM, Radu Gheorghe-2 [via Elasticsearch > > Users] \<[hidden email][http://user/SendEmail.jtp?type=node&node=4037265&i=0](http://user/SendEmail.jtp?type=node&node=4037265&i=0) > > > > > wrote: > > > > > Hello, > > > > > > I don't see a clean way of doing this. But you can pay some prices, > > > either at query or at index time, to obtain this. > > > > > > For example, at query time, you can try to get a list of unique users by > > > doing a terms facet[http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet/)on the user field. Then, you can do a multi > > > search [http://www.elasticsearch.org/guide/reference/api/multi-search/](http://www.elasticsearch.org/guide/reference/api/multi-search/)for all the users to see what was the last thing they bought. Finally, you > > > can count on the application side which of those was a Toshiba TV. Very > > > slow and doesn't scale, but you can do anything. > > > > > > On the other hand, if you have a predefined list of such queries, you > > > can a separate index (eg: last\_item\_bought) for each query. For example, > > > with this one, you can have an index where, each time a use buys something, > > > you update the document with their name as the ID, and the item they > > > bought. It shouldn't hurt indexing performance too much. When you want to > > > search, you just do a filter on "tv" and "toshiba", with size=0 and see the > > > number of hits. > > > > > > Best regards, > > > Radu > > > > > > On Thu, Jun 27, 2013 at 6:21 PM, oreno \<[hidden email][http://user/SendEmail.jtp?type=node&node=4037241&i=0](http://user/SendEmail.jtp?type=node&node=4037241&i=0) > > > > > > > wrote: > > > > > > > Hi All, > > > > I have a problem that I hope someone can help me with: > > > > > > > > I have the next mapping for my user index -\> docs(users) containing > > > > events > > > > in which the user bought different products. each event(date) can > > > > contain > > > > several products. I would like to retrieve the count of users that their > > > > last purchase of TV was TOSHIBA. for that I am willing to have a 'TV' > > > > as a > > > > product by itself. > > > > > > > > So in case a have the next events: > > > > > > > > user1|2013-05-18|TV,SAMSUNG > > > > user1|2013-05-19|TV,TOSHIBA > > > > user1|2013-05-20|PC > > > > user2|2013-05-18|TV,TOSHIBA > > > > user2|2013-05-19|TV,SAMSUNG > > > > user2|2013-05-20|PC > > > > user1 should be counted because its last bought TV was TOSHIBA but user2 > > > > should not. > > > > > > > > Does anyone have an idea how to approach this query? > > > > > > > > I tried several times but I'm new to elasticsearch so no success yet. > > > > > > > > Thanks in advanced, > > > > > > > > ``` > > > > { > > > > > > > > ``` > > > > > > > > "user" : { > > > > "properties" : { > > > > "name": {"type" : "string"}, > > > > "events" : { > > > > "type" : "nested", > > > > "properties" : { > > > > "event\_time" : {"type" : "Date"}, > > > > "products" : { "properties" : { "product" : {"type" : "string"} > > > > } } > > > > } > > > > } > > > > } > > > > } > > > > } > > > > > > > > -- > > > > View this message in context: > > > > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172.html) > > > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). > > > > > > > > -- > > > > 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][http://user/SendEmail.jtp?type=node&node=4037241&i=1](http://user/SendEmail.jtp?type=node&node=4037241&i=1) > > > > . > > > > > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > > > -- > > > [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene > > > > > > -- > > > 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][http://user/SendEmail.jtp?type=node&node=4037241&i=2](http://user/SendEmail.jtp?type=node&node=4037241&i=2) > > > . > > > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > > > * * * > > > > > > If you reply to this email, your message will be added to the > > > discussion below: > > > > > > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037241.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037241.html) > > > To unsubscribe from elasticsearch - count users query, click here. > > > NAML[http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro\_viewer&id=instant\_html!nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify\_subscribers!nabble%3Aemail.naml-instant\_emails!nabble%3Aemail.naml-send\_instant\_email!nabble%3Aemail.naml](http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml) > > > > -- > > > > _Oren Orgad_ > > > > _eXelate_ > > software engineer > > > > _p:_ +972- 72-2221329 | _m:_ +972- 52-3401051 | _e:_ [hidden email][http://user/SendEmail.jtp?type=node&node=4037265&i=1](http://user/SendEmail.jtp?type=node&node=4037265&i=1) > > > > * * * > > > > View this message in context: Re: elasticsearch - count users query[http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037265.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037265.html) > > > > Sent from the Elasticsearch Users mailing list archive[http://elasticsearch-users.115913.n3.nabble.com/](http://elasticsearch-users.115913.n3.nabble.com/)at [Nabble.com](http://Nabble.com). > > > > -- > > 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][http://user/SendEmail.jtp?type=node&node=4037282&i=1](http://user/SendEmail.jtp?type=node&node=4037282&i=1) > > . > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > -- > [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene > > -- > 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][http://user/SendEmail.jtp?type=node&node=4037282&i=2](http://user/SendEmail.jtp?type=node&node=4037282&i=2) > . > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > * * * > > If you reply to this email, your message will be added to the discussion > below: > > [http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037282.html](http://elasticsearch-users.115913.n3.nabble.com/elasticsearch-count-users-query-tp4037172p4037282.html) > To unsubscribe from elasticsearch - count users query, click here[http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe\_by\_code&node=4037172&code=b3Jlbm9AZXhlbGF0ZS5jb218NDAzNzE3Mnw4ODk1Mjk2Nzg=](http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4037172&code=b3Jlbm9AZXhlbGF0ZS5jb218NDAzNzE3Mnw4ODk1Mjk2Nzg=) > . > NAML[http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro\_viewer&id=instant\_html!nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify\_subscribers!nabble%3Aemail.naml-instant\_emails!nabble%3Aemail.naml-send\_instant\_email!nabble%3Aemail.naml](http://elasticsearch-users.115913.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml) -- [image: Inline image 1] \* \* _Oren Orgad_ | Software Engineer | _eXelate [http://www.exelate.com/](http://www.exelate.com/)_ _p:_ +972- 72-2221329 | _e:_ [oreno@exelate.com](mailto:oreno@exelate.com) ---
**Author:** ![radu\_gheorghe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/radu_gheorghe/32/556_2.png) [@radu\_gheorghe](https://discuss.elastic.co/u/radu_gheorghe)\ **Post date:** [July 1, 2013, 7:06am UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564/6 "2013-07-01T07:06:01Z")
Hello Oren, On Sun, Jun 30, 2013 at 5:27 PM, oreno [oreno@exelate.com](mailto:oreno@exelate.com) wrote: > Thanks Radu, > I appreciate your effort here. > I'm now looking into native script option for running java based logic > after running a more basic query(that will just return the documents > containing the range I need and contain the TV string). > is that what you meant by running on application side? I'm not sure where scripting would fit here. What I had in mind was a 3-step process: 1. You get the list of unique users from that time range (that bought a samsung TV) using a facet 2. You get the last buy for each user by running a filter for each user and sort descending on the date of the buy 3. At this point you still have to see if the last buy of these users was a samsung TV or not. You can simply get all the info you have in your application (nothing to do with ES here) and filter those out. Alternatively, you can treat results at step 2 as a stream and push that back into a new index/type in ES. Then, you could use ES for filtering. Alternatively, you can use TTL to keep that data for a while, as some sort of cache in case you need to repeat the query. > do you think I will pay a big price for this way of searching? Yeah, if the number of unique users at step 1 is high, my guess is it will be horribly expensive/slow. You can try and see if it works for you, though. But I keep thinking "there must be a better way". I just don't see it right now ☹ -- 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](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:28am UTC](https://discuss.elastic.co/t/elasticsearch-count-users-query/12564/7 "2017-07-06T02:28:49Z")