# How do a model this in elasticsearch

**URL:** <https://discuss.elastic.co/t/how-do-a-model-this-in-elasticsearch/13277>\
**Category:** Elasticsearch\
**Created:** [August 21, 2013, 4:29am UTC](https://discuss.elastic.co/t/how-do-a-model-this-in-elasticsearch/13277 "2013-08-21T04:29:03Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Justin\_MacCarthy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/justin_maccarthy/32/2020_2.png) [@Justin\_MacCarthy](https://discuss.elastic.co/u/Justin_MacCarthy)\
**Post date:** [August 21, 2013, 4:29am UTC](https://discuss.elastic.co/t/how-do-a-model-this-in-elasticsearch/13277/1 "2013-08-21T04:29:03Z")

</div>

Hi,

Newbies questions !

If I have a collection of movie info (title, rating, cast, full text of  
script ) and I have a collection of reviews , user A gave "Terminator 5  
stars"

Is there some way in ES to do something analogous to a join ? or do I need  
to de-normalize the data ?  
What is the term for this in ES?

Obviously I don't want to store the full script in an index every time a  
user reviews it, but I do want to find users the like topics such as  
"Skynet"

Thanks  
J

--  
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).

---

<div class="post-metadata">

**Author:** ![Justin\_MacCarthy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/justin_maccarthy/32/2020_2.png) [@Justin\_MacCarthy](https://discuss.elastic.co/u/Justin_MacCarthy)\
**Post date:** [August 22, 2013, 3:03am UTC](https://discuss.elastic.co/t/how-do-a-model-this-in-elasticsearch/13277/2 "2013-08-22T03:03:49Z")

</div>

Anyone help me on this ?

On Wednesday, August 21, 2013 11:29:03 AM UTC+7, Justin MacCarthy wrote:

> Hi,
> 
> Newbies questions !
> 
> If I have a collection of movie info (title, rating, cast, full text of  
> script ) and I have a collection of reviews , user A gave "Terminator 5  
> stars"
> 
> Is there some way in ES to do something analogous to a join ? or do I need  
> to de-normalize the data ?  
> What is the term for this in ES?
> 
> Obviously I don't want to store the full script in an index every time a  
> user reviews it, but I do want to find users the like topics such as  
> "Skynet"
> 
> Thanks  
> J

--  
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).

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 22, 2013, 9:56am UTC](https://discuss.elastic.co/t/how-do-a-model-this-in-elasticsearch/13277/3 "2013-08-22T09:56:25Z")

</div>

Hey,

you might want to check the terms lookup mechanism mentioned here:

> **[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.

You could also use parent/child functionality to model this, this really  
depends on what kind of queries you plan to execute and how your model your  
data in elasticsearch...

> **[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.

both solutions are worth trying out in your case, I guess.

--Alex

On Thu, Aug 22, 2013 at 5:03 AM, Justin MacCarthy \<  
[justin.maccarthy@gmail.com](mailto:justin.maccarthy@gmail.com)\> wrote:

> Anyone help me on this ?
> 
> On Wednesday, August 21, 2013 11:29:03 AM UTC+7, Justin MacCarthy wrote:
> 
> > Hi,
> > 
> > Newbies questions !
> > 
> > If I have a collection of movie info (title, rating, cast, full text of  
> > script ) and I have a collection of reviews , user A gave "Terminator 5  
> > stars"
> > 
> > Is there some way in ES to do something analogous to a join ? or do I  
> > need to de-normalize the data ?  
> > What is the term for this in ES?
> > 
> > Obviously I don't want to store the full script in an index every time a  
> > user reviews it, but I do want to find users the like topics such as  
> > "Skynet"
> > 
> > Thanks  
> > J
> 
> --  
> 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).

--  
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).

---

<div class="post-metadata">

**Author:** ![mattweber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattweber/32/44940_2.png) [@mattweber](https://discuss.elastic.co/u/mattweber)\
**Post date:** [August 22, 2013, 12:04pm UTC](https://discuss.elastic.co/t/how-do-a-model-this-in-elasticsearch/13277/4 "2013-08-22T12:04:42Z")

</div>

FYI, my terms lookup by query pull request might interest you.

> <https://github.com/elastic/elasticsearch/pull/3278>
>
> This PR adds support for generating a terms filter based on the field values
> of …documents matching a specified lookup query/filter. The value of the 
> configurable "path" field is collected from the field data cache for each 
> document matching the lookup query/filter and is then used to filter the main 
> query. This is can also be called a join filter.
> 
> This PR abstracts the TermsLookup functionality in order to support multiple
> lookup methods. The existing functionality is moved into FieldTermsLookup and
> the new query based lookup is in QueryTermsLookup. All existing caching 
> functionality works with the new query based lookup for increased performance.
> 
> During testing of I found that one of the performance bottlenecks was 
> generating the Lucene TermsFilter on large sets of terms (probably since
> it sorts the terms). I have created a FieldDataTermsFilter that uses the
> field data cache to lookup value of the field being filtered and compare it to
> the set of gathered terms. This significantly increased performance at the 
> cost of higher memory usage. Currently a TermsFilter is used when the number
> of filtering terms is less than 1024 and the FieldDataTermsFilter is used
> for everything else. This should eventually be configurable or we need to
> perform some test to find the optimal value.
> 
> Examples:
> 
> Replicate a has\_child query by joining on the child's "pid" field to the
> parent's "id" field for each child that has the tag "something".
> 
> \`\`\`
> curl -XPOST 'http://localhost:9200/parentIndex/\_search' -d '{
> "query": {
> "constant\_score": {
> "filter": {
> "terms": {
> "id": {
> "index": "childIndex",
> "type": "childType",
> "path": "pid",
> "query": {
> "term": {
> "tag": "something"
> }
> }
> }
> }
> }
> }
> }
> }'
> \`\`\`
> 
> Lookup companies that offer products or services mentioning elasticsearch.
> Notice that products and services are kept in their own indices.
> 
> \`\`\`
> curl -XPOST 'http://localhost:9200/companies/\_search' -d '{
> "query": {
> "constant\_score": {
> "filter": {
> "terms": {
> "company\_id": {
> "indices": \["products", "services"\],
> "path": "company\_id",
> "filter": {
> "term": {
> "description": "elasticsearch"
> }
> }
> }
> }
> }
> }
> }
> }'
> \`\`\`

Thanks,  
Matt Weber

On Thursday, August 22, 2013, Alexander Reelsen wrote:

> Hey,
> 
> you might want to check the terms lookup mechanism mentioned here:  
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/terms-filter/)
> 
> You could also use parent/child functionality to model this, this really  
> depends on what kind of queries you plan to execute and how your model your  
> data in elasticsearch...  
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/has-parent-query/)  
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/has-child-query/)
> 
> both solutions are worth trying out in your case, I guess.
> 
> --Alex
> 
> On Thu, Aug 22, 2013 at 5:03 AM, Justin MacCarthy \<  
> [justin.maccarthy@gmail.com](mailto:justin.maccarthy@gmail.com) \<javascript:\_e({}, 'cvml',  
> 'justin.maccarthy@gmail.com');\>\> wrote:
> 
> > Anyone help me on this ?
> > 
> > On Wednesday, August 21, 2013 11:29:03 AM UTC+7, Justin MacCarthy wrote:
> > 
> > > Hi,
> > > 
> > > Newbies questions !
> > > 
> > > If I have a collection of movie info (title, rating, cast, full text of  
> > > script ) and I have a collection of reviews , user A gave "Terminator 5  
> > > stars"
> > > 
> > > Is there some way in ES to do something analogous to a join ? or do I  
> > > need to de-normalize the data ?  
> > > What is the term for this in ES?
> > > 
> > > Obviously I don't want to store the full script in an index every time a  
> > > user reviews it, but I do want to find users the like topics such as  
> > > "Skynet"
> > > 
> > > Thanks  
> > > J
> > 
> > --  
> > 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) \<javascript:\_e({},  
> > 'cvml', 'elasticsearch%2Bunsubscribe@googlegroups.com');\>.  
> > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
> 
> --  
> 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) \<javascript:\_e({},  
> 'cvml', 'elasticsearch%2Bunsubscribe@googlegroups.com');\>.  
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

--  
Thanks,  
Matt Weber

--  
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).

---

<div class="post-metadata">

**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:20am UTC](https://discuss.elastic.co/t/how-do-a-model-this-in-elasticsearch/13277/5 "2017-07-06T02:20:12Z")

</div>