# Relations
**URL:** https://discuss.elastic.co/t/relations/10331
**Category:** Elasticsearch
**Created:** [January 14, 2013, 1:10pm UTC](https://discuss.elastic.co/t/relations/10331 "2013-01-14T13:10:24Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@GX1](https://discuss.elastic.co/u/GX1)
#### Post date: [January 14, 2013, 1:10pm UTC](https://discuss.elastic.co/t/relations/10331/1 "2013-01-14T13:10:24Z")
Hi All
comming from a strong RMDB background I still tend to think in a sql
manner, so Im sorry for bad terminology and misconceptions but im trying to
acomplish the following:
lets say I have an index called "data" and two types "blogs" and "comments"
"blogs" has the fields [blog\_id, user, subject, body, postdate]
"comments" has the fields [comment\_id, user, text, postdate, blog\_id]
in this manner I can have a website where the user profile can have a link
to "blogs" and another link to "comments" where in each case a simple
search of the type and filtering the user gets me the results I want
now I have a special case where there is a search page and I search "blogs"
between a date range with user "aaa" and body "bbb", again so far so good
the problem is if I only want to list the relative comments of this result
set.
In other words I want to search typeA and return typeB where typeA.id =
typeB.fieldX
is this at all possible without restructuring and re-indexing everything,
im currently running elasticsearch 19.8 using the http api?
Regards
GX
--
---
### Author:  [@polyfractal](https://discuss.elastic.co/u/polyfractal)
#### Post date: [January 14, 2013, 3:30pm UTC](https://discuss.elastic.co/t/relations/10331/2 "2013-01-14T15:30:02Z")
ES does not have anything similar to joins. You have to either denormalize
your data (e.g. comments inside the blog document), structure the data
using nested or parent/child format, or perform multiple queries.
The easiest solution is to perform a second query on the `comments` type
using blog\_id as a filter. You'll have to perform a query for each result
returned from your first `blog` query, but these should be pretty quick
once the filter is in memory. You could also store document IDs of
individual comments inside the `blog` type, which will result in faster
lookups and less memory usage than filters (with the disadvantage of
needing to update the blog doc whenever a new comment is added).
-Zach
On Monday, January 14, 2013 8:10:24 AM UTC-5, GX wrote:
> Hi All
>
> comming from a strong RMDB background I still tend to think in a sql
> manner, so Im sorry for bad terminology and misconceptions but im trying to
> acomplish the following:
>
> lets say I have an index called "data" and two types "blogs" and "comments"
> "blogs" has the fields [blog\_id, user, subject, body, postdate]
> "comments" has the fields [comment\_id, user, text, postdate, blog\_id]
>
> in this manner I can have a website where the user profile can have a link
> to "blogs" and another link to "comments" where in each case a simple
> search of the type and filtering the user gets me the results I want
>
> now I have a special case where there is a search page and I search
> "blogs" between a date range with user "aaa" and body "bbb", again so far
> so good the problem is if I only want to list the relative comments of this
> result set.
> In other words I want to search typeA and return typeB where typeA.id =
> typeB.fieldX
>
> is this at all possible without restructuring and re-indexing everything,
> im currently running elasticsearch 19.8 using the http api?
>
> Regards
>
> GX
--
---
### Author:  [@GX1](https://discuss.elastic.co/u/GX1)
#### Post date: [January 14, 2013, 6:49pm UTC](https://discuss.elastic.co/t/relations/10331/3 "2013-01-14T18:49:56Z")
Zach thanks for your clarification
I did have the running two queries in mind, but we are talking of loads of
data (otherwise I wouldn't need a dedicated search engine right), so if I
query "blogs" and I get 10,000 hits, only 50 are returned due to paging,
enevn if I did return all 10k results, looping all that data and
constructing a second query will be inefficient.
The parent child approach sounds interesting, the docs give a simple
example:
> **[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.
{ "blog\_tag" : { "\_parent" : { "type" : "blog" } }
but how do I stipulate that blog \_tag is related to blog via filedA? (or am
I misunderstanding the use) which blog\_tags belong to which blogs or is
blog tag not a type but a field?
Regards
GX
On Monday, January 14, 2013 5:30:02 PM UTC+2, Zachary Tong wrote:
> ES does not have anything similar to joins. You have to either
> denormalize your data (e.g. comments inside the blog document), structure
> the data using nested or parent/child format, or perform multiple queries.
>
> The easiest solution is to perform a second query on the `comments` type
> using blog\_id as a filter. You'll have to perform a query for each result
> returned from your first `blog` query, but these should be pretty quick
> once the filter is in memory. You could also store document IDs of
> individual comments inside the `blog` type, which will result in faster
> lookups and less memory usage than filters (with the disadvantage of
> needing to update the blog doc whenever a new comment is added).
>
> -Zach
>
> On Monday, January 14, 2013 8:10:24 AM UTC-5, GX wrote:
>
> > Hi All
> >
> > comming from a strong RMDB background I still tend to think in a sql
> > manner, so Im sorry for bad terminology and misconceptions but im trying to
> > acomplish the following:
> >
> > lets say I have an index called "data" and two types "blogs" and
> > "comments"
> > "blogs" has the fields [blog\_id, user, subject, body, postdate]
> > "comments" has the fields [comment\_id, user, text, postdate, blog\_id]
> >
> > in this manner I can have a website where the user profile can have a
> > link to "blogs" and another link to "comments" where in each case a simple
> > search of the type and filtering the user gets me the results I want
> >
> > now I have a special case where there is a search page and I search
> > "blogs" between a date range with user "aaa" and body "bbb", again so far
> > so good the problem is if I only want to list the relative comments of this
> > result set.
> > In other words I want to search typeA and return typeB where typeA.id =
> > typeB.fieldX
> >
> > is this at all possible without restructuring and re-indexing everything,
> > im currently running elasticsearch 19.8 using the http api?
> >
> > Regards
> >
> > GX
--
---
### Author:  [@GX1](https://discuss.elastic.co/u/GX1)
#### Post date: [January 14, 2013, 7:06pm UTC](https://discuss.elastic.co/t/relations/10331/4 "2013-01-14T19:06:58Z")
I found a link that has some helpful example on parent/child relation:
> **[Fun with elasticsearch's children and nested documents - Space Vatican](https://www.spacevatican.org/2012/6/3/fun-with-elasticsearch-s-children-and-nested-documents/)**
>
> When you’re indexing data, the world is rarely as simple as each document existing in isolation. Sometimes, you’re better off …
to answer my question which parent belongs to which child is when indexing
specify parent like the example below
curl -XPOST localhost:9200/authors/book/1?parent=2
Regards
GX
Thanks again Zach
On Monday, January 14, 2013 8:49:56 PM UTC+2, GX wrote:
> Zach thanks for your clarification
>
> I did have the running two queries in mind, but we are talking of loads of
> data (otherwise I wouldn't need a dedicated search engine right), so if I
> query "blogs" and I get 10,000 hits, only 50 are returned due to paging,
> enevn if I did return all 10k results, looping all that data and
> constructing a second query will be inefficient.
>
> The parent child approach sounds interesting, the docs give a simple
> example:
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/mapping/parent-field.html)
>
> { "blog\_tag" : { "\_parent" : { "type" : "blog" } }
>
> but how do I stipulate that blog \_tag is related to blog via filedA? (or
> am I misunderstanding the use) which blog\_tags belong to which blogs or is
> blog tag not a type but a field?
>
> Regards
>
> GX
>
> On Monday, January 14, 2013 5:30:02 PM UTC+2, Zachary Tong wrote:
>
> > ES does not have anything similar to joins. You have to either
> > denormalize your data (e.g. comments inside the blog document), structure
> > the data using nested or parent/child format, or perform multiple queries.
> >
> > The easiest solution is to perform a second query on the `comments` type
> > using blog\_id as a filter. You'll have to perform a query for each result
> > returned from your first `blog` query, but these should be pretty quick
> > once the filter is in memory. You could also store document IDs of
> > individual comments inside the `blog` type, which will result in faster
> > lookups and less memory usage than filters (with the disadvantage of
> > needing to update the blog doc whenever a new comment is added).
> >
> > -Zach
> >
> > On Monday, January 14, 2013 8:10:24 AM UTC-5, GX wrote:
> >
> > > Hi All
> > >
> > > comming from a strong RMDB background I still tend to think in a sql
> > > manner, so Im sorry for bad terminology and misconceptions but im trying to
> > > acomplish the following:
> > >
> > > lets say I have an index called "data" and two types "blogs" and
> > > "comments"
> > > "blogs" has the fields [blog\_id, user, subject, body, postdate]
> > > "comments" has the fields [comment\_id, user, text, postdate, blog\_id]
> > >
> > > in this manner I can have a website where the user profile can have a
> > > link to "blogs" and another link to "comments" where in each case a simple
> > > search of the type and filtering the user gets me the results I want
> > >
> > > now I have a special case where there is a search page and I search
> > > "blogs" between a date range with user "aaa" and body "bbb", again so far
> > > so good the problem is if I only want to list the relative comments of this
> > > result set.
> > > In other words I want to search typeA and return typeB where typeA.id =
> > > typeB.fieldX
> > >
> > > is this at all possible without restructuring and re-indexing
> > > everything, im currently running elasticsearch 19.8 using the http api?
> > >
> > > Regards
> > >
> > > GX
--
---
### Author:  [@polyfractal](https://discuss.elastic.co/u/polyfractal)
#### Post date: [January 14, 2013, 7:23pm UTC](https://discuss.elastic.co/t/relations/10331/5 "2013-01-14T19:23:39Z")
I'm not sure the performance hit would be terrible, especially if you
include document IDs in the `blog` type. Direct ID lookups in ES are _very_ fast.
I asked Shay about this a while ago, and Shay said that direct ID lookups
can be expected to be a bit faster than B-Trees
([https://groups.google.com/d/msg/elasticsearch/hx0D2rKWr0s/EnTrQVTQEH8J](https://groups.google.com/d/msg/elasticsearch/hx0D2rKWr0s/EnTrQVTQEH8J)).
If you setup your routing correctly (such that comments are stored on the
same shard as the blog doc), then you are effectively recreating exactly
how ES does parent/child nesting internally. Although I suppose you'll pay
for network round-trips.
I use the this approach on an index with ~250,000 docs and performance hit
seems to be negligible. Not sure how well it scales, but for my traffic it
works fine.
For parent/child (and nested too)...you posted the link that I had in mind
=) If that solution works for you, it'll be a lot easier than doing what I
outlined above.
-Zach
On Monday, January 14, 2013 1:49:56 PM UTC-5, GX wrote:
> Zach thanks for your clarification
>
> I did have the running two queries in mind, but we are talking of loads of
> data (otherwise I wouldn't need a dedicated search engine right), so if I
> query "blogs" and I get 10,000 hits, only 50 are returned due to paging,
> enevn if I did return all 10k results, looping all that data and
> constructing a second query will be inefficient.
>
> The parent child approach sounds interesting, the docs give a simple
> example:
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/mapping/parent-field.html)
>
> { "blog\_tag" : { "\_parent" : { "type" : "blog" } }
>
> but how do I stipulate that blog \_tag is related to blog via filedA? (or
> am I misunderstanding the use) which blog\_tags belong to which blogs or is
> blog tag not a type but a field?
>
> Regards
>
> GX
>
> On Monday, January 14, 2013 5:30:02 PM UTC+2, Zachary Tong wrote:
>
> > ES does not have anything similar to joins. You have to either
> > denormalize your data (e.g. comments inside the blog document), structure
> > the data using nested or parent/child format, or perform multiple queries.
> >
> > The easiest solution is to perform a second query on the `comments` type
> > using blog\_id as a filter. You'll have to perform a query for each result
> > returned from your first `blog` query, but these should be pretty quick
> > once the filter is in memory. You could also store document IDs of
> > individual comments inside the `blog` type, which will result in faster
> > lookups and less memory usage than filters (with the disadvantage of
> > needing to update the blog doc whenever a new comment is added).
> >
> > -Zach
> >
> > On Monday, January 14, 2013 8:10:24 AM UTC-5, GX wrote:
> >
> > > Hi All
> > >
> > > comming from a strong RMDB background I still tend to think in a sql
> > > manner, so Im sorry for bad terminology and misconceptions but im trying to
> > > acomplish the following:
> > >
> > > lets say I have an index called "data" and two types "blogs" and
> > > "comments"
> > > "blogs" has the fields [blog\_id, user, subject, body, postdate]
> > > "comments" has the fields [comment\_id, user, text, postdate, blog\_id]
> > >
> > > in this manner I can have a website where the user profile can have a
> > > link to "blogs" and another link to "comments" where in each case a simple
> > > search of the type and filtering the user gets me the results I want
> > >
> > > now I have a special case where there is a search page and I search
> > > "blogs" between a date range with user "aaa" and body "bbb", again so far
> > > so good the problem is if I only want to list the relative comments of this
> > > result set.
> > > In other words I want to search typeA and return typeB where typeA.id =
> > > typeB.fieldX
> > >
> > > is this at all possible without restructuring and re-indexing
> > > everything, im currently running elasticsearch 19.8 using the http api?
> > >
> > > Regards
> > >
> > > GX
--
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 2:56am UTC](https://discuss.elastic.co/t/relations/10331/6 "2017-07-06T02:56:36Z")