# Kind of inner join query in Elasticsearch...?

**URL:** <https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621>\
**Category:** Elasticsearch\
**Created:** [February 5, 2013, 12:01am UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621 "2013-02-05T00:01:07Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marek\_Stachura](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marek_stachura/32/2497_2.png) [@Marek\_Stachura](https://discuss.elastic.co/u/Marek_Stachura)\
**Post date:** [February 5, 2013, 12:01am UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/1 "2013-02-05T00:01:07Z")

</div>

Hi,

please see following gist for sample index setup and sample queries:

> <https://gist.github.com/dedico/4710731>

I have set up two hotels with rooms - one hotel can have many rooms -  
classic 1 to many example.  
Hotel mapping:

{  
"regular\_hotel":{  
"properties":{  
"rooms": {  
"type": "object"  
}  
}  
}  
}

Every room has given allocation (availability) on given day. Sample hotel  
with rooms:

{  
"name": "Hotel Staromiejski",  
"city": "Słupsk",  
"rooms": [  
{  
"night": "2013-02-15",  
"allocation": "4"  
},  
{  
"night": "2013-02-16",  
"allocation": "2"  
},  
{  
"night": "2013-02-17",  
"allocation": "0"  
},  
{  
"night": "2013-02-18",  
"allocation": "1"  
},  
{  
"night": "2013-02-19",  
"allocation": "5"  
},  
{  
"night": "2013-02-20",  
"allocation": "1"  
}  
]  
}

Question: how should I query elasticsearch to get something similar as following SQL query:

select \* from hotels inner join rooms on hotels.id = rooms.hotel\_id  
where rooms.night between '2013-02-18' and '2013-02-20' and rooms.allocation \> 0

?

Please see my queries using range in the gist. They are not working as I would like them to work...

Thanks in advance,  
Marek

--  
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:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [February 5, 2013, 8:35am UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/2 "2013-02-05T08:35:45Z")

</div>

As there is a direct link between night and allocation, I will use nested docs for rooms.

See: [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/mapping/nested-type.html)

Does it help?

Le 5 févr. 2013 à 01:01, Marek Stachura [marek.stachura@gmail.com](mailto:marek.stachura@gmail.com) a écrit :

> Hi,
> 
> please see following gist for sample index setup and sample queries:  
> [Elasticsearch example - index setup and sample queries · GitHub](https://gist.github.com/4710731)
> 
> I have set up two hotels with rooms - one hotel can have many rooms - classic 1 to many example.  
> Hotel mapping:  
> {  
> "regular\_hotel":{  
> "properties":{  
> "rooms": {  
> "type": "object"  
> }  
> }  
> }  
> }
> 
> Every room has given allocation (availability) on given day. Sample hotel with rooms:  
> {  
> "name": "Hotel Staromiejski",  
> "city": "Słupsk",  
> "rooms": [  
> {  
> "night": "2013-02-15",  
> "allocation": "4"  
> },  
> {  
> "night": "2013-02-16",  
> "allocation": "2"  
> },  
> {  
> "night": "2013-02-17",  
> "allocation": "0"  
> },  
> {  
> "night": "2013-02-18",  
> "allocation": "1"  
> },  
> {  
> "night": "2013-02-19",  
> "allocation": "5"  
> },  
> {  
> "night": "2013-02-20",  
> "allocation": "1"  
> }  
> ]  
> }
> 
> Question: how should I query elasticsearch to get something similar as following SQL query:
> 
> select \* from hotels inner join rooms on hotels.id = rooms.hotel\_id  
> where rooms.night between '2013-02-18' and '2013-02-20' and rooms.allocation \> 0
> 
> ?
> 
> Please see my queries using range in the gist. They are not working as I would like them to work...
> 
> Thanks in advance,  
> Marek
> 
> --  
> 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:** ![Marek\_Stachura](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marek_stachura/32/2497_2.png) [@Marek\_Stachura](https://discuss.elastic.co/u/Marek_Stachura)\
**Post date:** [February 5, 2013, 12:01pm UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/3 "2013-02-05T12:01:40Z")

</div>

Hi David,

thanks for your reply.

Please see my new gist using nested type

> <https://gist.github.com/dedico/4713754>

Unfortunately it is not working as expected. Probably my queries are  
broken...  
If I query:

curl -XGET localhost:9200/hotels/nested\_hotel/\_search -d '{  
"query": {  
"nested": {  
"path" : "rooms",  
"query": {  
"bool": {  
"must": [  
{ "range": { "rooms.night": { "from": "2013-02-16", "to": "2013-02-17" } } },  
{ "range": { "rooms.allocation": { "gt": 0 } } }  
]  
}  
}  
}  
}  
}'

I should get only second hotel back.  
I tried also terms query:

curl -XGET localhost:9200/hotels/nested\_hotel/\_search -d '{  
"query": {  
"nested": {  
"path" : "rooms",  
"query": {  
"bool": {  
"must": [  
{ "terms": { "rooms.night": ["2013-02-16", "2013-02-17"], "minimum\_match": 2 } },  
{ "range": { "rooms.allocation": { "gt": 0 } } }  
]  
}  
}  
}  
}  
}'

The same... I get both hotels back...

Thank you,  
Marek

On Tuesday, February 5, 2013 9:35:45 AM UTC+1, David Pilato wrote:

> As there is a direct link between night and allocation, I will use nested  
> docs for rooms.
> 
> See: [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/mapping/nested-type.html)
> 
> Does it help?

--  
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:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\
**Post date:** [February 5, 2013, 12:59pm UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/4 "2013-02-05T12:59:34Z")

</div>

Hi Marek

> Unfortunately it is not working as expected. Probably my queries are  
> broken...

To query nested documents, you need to use the special nested query or  
filter, otherwise you're just querying the parent document

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

clint

--  
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:** ![Marek\_Stachura](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marek_stachura/32/2497_2.png) [@Marek\_Stachura](https://discuss.elastic.co/u/Marek_Stachura)\
**Post date:** [February 5, 2013, 1:11pm UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/5 "2013-02-05T13:11:29Z")

</div>

Hi Clint,

please see my gist [Elasticsearch example - index setup using nested feature and sample queries · GitHub](https://gist.github.com/dedico/4713754) and the queries:

curl -XGET localhost:9200/hotels/nested\_hotel/\_search -d '{  
"query": {  
"nested": {  
"path" : "rooms",  
"query": {  
"bool": {  
"must": [  
{ "range": { "rooms.night": { "from": "2013-02-16", "to": "2013-02-17" } } },  
{ "range": { "rooms.allocation": { "gt": 0 } } }  
]  
}  
}  
}  
}  
}'

and second one using terms:

curl -XGET localhost:9200/hotels/nested\_hotel/\_search -d '{  
"query": {  
"nested": {  
"path" : "rooms",  
"query": {  
"bool": {  
"must": [  
{ "terms": { "rooms.night": ["2013-02-16", "2013-02-17"], "minimum\_match": 2 } },  
{ "range": { "rooms.allocation": { "gt": 0 } } }  
]  
}  
}  
}  
}  
}'

Am I not using special nested query in that case?  
I think I am...

Thank you,  
Marek

On Tuesday, February 5, 2013 1:59:34 PM UTC+1, Clinton Gormley wrote:

> Hi Marek
> 
> > Unfortunately it is not working as expected. Probably my queries are  
> > broken...
> 
> To query nested documents, you need to use the special nested query or  
> filter, otherwise you're just querying the parent document
> 
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/nested-query.html)  
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/nested-filter.html)
> 
> clint

--  
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:** ![Marek\_Stachura](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marek_stachura/32/2497_2.png) [@Marek\_Stachura](https://discuss.elastic.co/u/Marek_Stachura)\
**Post date:** [February 7, 2013, 11:50am UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/6 "2013-02-07T11:50:43Z")

</div>

Hi,

anyone could help on this one? Is it possible to query similar as inner  
join example presented on my first post?

Thanks,  
Marek

On Tuesday, February 5, 2013 2:11:29 PM UTC+1, Marek Stachura wrote:

> Hi Clint,
> 
> please see my gist [Elasticsearch example - index setup using nested feature and sample queries · GitHub](https://gist.github.com/dedico/4713754) and the  
> queries:
> 
> curl -XGET localhost:9200/hotels/nested\_hotel/\_search -d '{  
> "query": {  
> "nested": {  
> "path" : "rooms",  
> "query": {  
> "bool": {  
> "must": [  
> { "range": { "rooms.night": { "from": "2013-02-16", "to": "2013-02-17" } } },  
> { "range": { "rooms.allocation": { "gt": 0 } } }  
> ]  
> }  
> }  
> }  
> }  
> }'
> 
> and second one using terms:
> 
> curl -XGET localhost:9200/hotels/nested\_hotel/\_search -d '{  
> "query": {  
> "nested": {  
> "path" : "rooms",  
> "query": {  
> "bool": {  
> "must": [  
> { "terms": { "rooms.night": ["2013-02-16", "2013-02-17"], "minimum\_match": 2 } },  
> { "range": { "rooms.allocation": { "gt": 0 } } }  
> ]  
> }  
> }  
> }  
> }  
> }'
> 
> Am I not using special nested query in that case?  
> I think I am...
> 
> Thank you,  
> Marek

--  
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:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\
**Post date:** [February 7, 2013, 12:09pm UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/7 "2013-02-07T12:09:26Z")

</div>

Hi Marek

> Am I not using special nested query in that case?  
> I think I am...

Apologies - I looked at the wrong gist. Yes you are using the nested  
query there, but you are not phrasing the query correctly.

First problem: from/to. "from" is greater-than-or-equal-to. But "to" is  
less-than. So it doesn't include the "to" value.

Second problem, your query isn't phrasing your requirements correctly.  
What you actually want to do is to say:

- give me hotels with:
- availability on 2013-02-16
- and
- availability on 2013-02-17

Which would look like this:

> <https://gist.github.com/dedico/4713754#comment-768993>

It's rather long, so I haven't posted it in the mail.

clint

--  
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:** ![Marek\_Stachura](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marek_stachura/32/2497_2.png) [@Marek\_Stachura](https://discuss.elastic.co/u/Marek_Stachura)\
**Post date:** [February 8, 2013, 10:11am UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/8 "2013-02-08T10:11:14Z")

</div>

Hi Clint,

awesome! Thanks a lot! You just made my day 🙂

I corrected a bit the syntax, correct version below and in gist in comments:

> <https://gist.github.com/dedico/4713754>

curl -XGET localhost:9200/hotels/nested\_hotel/\_search -d '{  
"query": {  
"constant\_score": {  
"filter": {  
"bool" : {  
"must" : [  
{  
"nested" : {  
"path" : "rooms",  
"filter" : {  
"bool" : {  
"must" : [  
{"term" : { "rooms.night" : "2013-02-16" }},  
{ "range": { "rooms.allocation": { "gt": 0 } } }  
]  
}  
}  
}  
},  
{  
"nested" : {  
"path" : "rooms",  
"filter" : {  
"bool" : {  
"must" : [  
{"term" : { "rooms.night" : "2013-02-17" }},  
{ "range": { "rooms.allocation": { "gt": 0 } } }  
]  
}  
}  
}  
}  
]  
}  
}  
}  
}}'

On Thursday, February 7, 2013 1:09:26 PM UTC+1, Clinton Gormley wrote:

> Hi Marek
> 
> > Am I not using special nested query in that case?  
> > I think I am...
> 
> Apologies - I looked at the wrong gist. Yes you are using the nested  
> query there, but you are not phrasing the query correctly.
> 
> First problem: from/to. "from" is greater-than-or-equal-to. But "to" is  
> less-than. So it doesn't include the "to" value.
> 
> Second problem, your query isn't phrasing your requirements correctly.  
> What you actually want to do is to say:
> 
> - give me hotels with:
> - availability on 2013-02-16
> - and
> - availability on 2013-02-17
> 
> Which would look like this:
> 
> [Elasticsearch example - index setup using nested feature and sample queries · GitHub](https://gist.github.com/dedico/4713754#comment-768993)
> 
> It's rather long, so I haven't posted it in the mail.
> 
> clint

--  
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:52am UTC](https://discuss.elastic.co/t/kind-of-inner-join-query-in-elasticsearch/10621/9 "2017-07-06T02:52:28Z")

</div>