# Emulating join-like behavior in es? **URL:** \ **Category:** Elasticsearch\ **Created:** [June 25, 2011, 2:04pm UTC](https://discuss.elastic.co/t/emulating-join-like-behavior-in-es/4706 "2011-06-25T14:04:03Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Hari\_Shankar](https://avatars.discourse-cdn.com/v4/letter/h/ad7895/32.png) [@Hari\_Shankar](https://discuss.elastic.co/u/Hari_Shankar)\ **Post date:** [June 25, 2011, 2:04pm UTC](https://discuss.elastic.co/t/emulating-join-like-behavior-in-es/4706/1 "2011-06-25T14:04:03Z")
Hi, Let's say I have 2 tables in my relational DB, one called customers, which contains customer data and one called metrics, which contains customer metrics against customer id and date. For most of my queries, I will need to join the two tables on the customer id. How can this behavior be emulated efficiently in es? My SQL queries are usually like "SELECT customers.customerid, SUM(metrics.logins), SUM(something else), ... FROM metrics JOIN customers ON metrics.customerid=customers.customerid WHERE metrics.date BETWEEN date1 AND date2 AND customers.isActive=1 GROUP BY customers.customerid ORDER BY SUM(metrics.logins) DESC" We could do this in the client side, but that would be very inefficient, right? Can I use the \_parent mapping and has\_child query to do this? Does it return both the relevant parent and child docs as a single merged document? I could store both of them together in a de-normalized way, but that looks ugly. Any other ideas? Thanks, Hari ---
**Author:** ![Karussell1](https://avatars.discourse-cdn.com/v4/letter/k/50afbb/32.png) [@Karussell1](https://discuss.elastic.co/u/Karussell1)\ **Post date:** [June 27, 2011, 1:33pm UTC](https://discuss.elastic.co/t/emulating-join-like-behavior-in-es/4706/2 "2011-06-27T13:33:33Z")
Take a look into facets: > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. On Jun 25, 4:04 pm, Hari Shankar [shaan.h...@gmail.com](mailto:shaan.h...@gmail.com) wrote: > Hi, > > Let's say I have 2 tables in my relational DB, one called customers, which > contains customer data and one called metrics, which contains customer > metrics against customer id and date. For most of my queries, I will need to > join the two tables on the customer id. How can this behavior be emulated > efficiently in es? > > My SQL queries are usually like > > "SELECT customers.customerid, SUM(metrics.logins), SUM(something else), ... > FROM metrics JOIN customers ON metrics.customerid=customers.customerid > WHERE metrics.date BETWEEN date1 AND date2 > AND customers.isActive=1 > GROUP BY customers.customerid > ORDER BY SUM(metrics.logins) DESC" > > We could do this in the client side, but that would be very inefficient, > right? Can I use the \_parent mapping and has\_child query to do this? Does it > return both the relevant parent and child docs as a single merged document? > I could store both of them together in a de-normalized way, but that looks > ugly. Any other ideas? > > Thanks, > Hari ---
**Author:** ![Hari\_Shankar](https://avatars.discourse-cdn.com/v4/letter/h/ad7895/32.png) [@Hari\_Shankar](https://discuss.elastic.co/u/Hari_Shankar)\ **Post date:** [June 27, 2011, 2:11pm UTC](https://discuss.elastic.co/t/emulating-join-like-behavior-in-es/4706/3 "2011-06-27T14:11:57Z")
Hi, I understand facets will be used for the SUM(..) part of the query. But how do I get data from both the customers and metrics table at once? We could implement it client-side, but on server-side would be nicer, right? Basically I need to show customer name (which comes from the customers index) and customer logins (which is in metrics index, against customer id) side by side. Thanks, Hari On Mon, Jun 27, 2011 at 7:03 PM, Karussell [tableyourtime@googlemail.com](mailto:tableyourtime@googlemail.com)wrote: > Take a look into facets: > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/) > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/statistical-facet.html) > > On Jun 25, 4:04 pm, Hari Shankar [shaan.h...@gmail.com](mailto:shaan.h...@gmail.com) wrote: > > > Hi, > > > > Let's say I have 2 tables in my relational DB, one called customers, > > which > > contains customer data and one called metrics, which contains customer > > metrics against customer id and date. For most of my queries, I will need > > to > > join the two tables on the customer id. How can this behavior be emulated > > efficiently in es? > > > > My SQL queries are usually like > > > > "SELECT customers.customerid, SUM(metrics.logins), SUM(something else), > > ... > > FROM metrics JOIN customers ON metrics.customerid=customers.customerid > > WHERE metrics.date BETWEEN date1 AND date2 > > AND customers.isActive=1 > > GROUP BY customers.customerid > > ORDER BY SUM(metrics.logins) DESC" > > > > We could do this in the client side, but that would be very inefficient, > > right? Can I use the \_parent mapping and has\_child query to do this? Does > > it > > return both the relevant parent and child docs as a single merged > > document? > > I could store both of them together in a de-normalized way, but that > > looks > > ugly. Any other ideas? > > > > Thanks, > > Hari ---
**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, 4:02am UTC](https://discuss.elastic.co/t/emulating-join-like-behavior-in-es/4706/4 "2017-07-06T04:02:31Z")