Kind of inner join query in Elasticsearch...?

Hi,

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

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.
For more options, visit https://groups.google.com/groups/opt_out.

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

See: 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 a écrit :

Hi,

please see following gist for sample index setup and sample queries:
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.
For more options, visit 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.
For more options, visit https://groups.google.com/groups/opt_out.

Hi David,

thanks for your reply.

Please see my new gist using nested type

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: 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.
For more options, visit https://groups.google.com/groups/opt_out.

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

http://www.elasticsearch.org/guide/reference/query-dsl/nested-query.html
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.
For more options, visit https://groups.google.com/groups/opt_out.

Hi Clint,

please see my gist 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

http://www.elasticsearch.org/guide/reference/query-dsl/nested-query.html
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.
For more options, visit https://groups.google.com/groups/opt_out.

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 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.
For more options, visit https://groups.google.com/groups/opt_out.

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:

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.
For more options, visit https://groups.google.com/groups/opt_out.

Hi Clint,

awesome! Thanks a lot! You just made my day :slight_smile:

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

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:

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.
For more options, visit https://groups.google.com/groups/opt_out.