Interval Based Search in Elastic Search


(Bijuv V) #1

I have a search requirement , which Im trying to solve using Elastic Search.

Assuming that the below JSON is indexed, what will be the query to fetch an
availability with starttime as 10/12/2013 11:00:00 and time period of 35
minutes . The search should return only the MeetingRoom1.

Im sure that I should be looking at date functions but not able to find a
correct query criteria that can be used . Any pointers?

Meeting Room 1:

{
"meetingroomid": 45,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 13:00:00"
},
{
"starttime": "10/12/2013 14:00:00" ,
"endtime": "10/12/2013 15:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

Meeting Room 2:

{
"meetingroomid": 100,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 11:01:00"
},
{
"starttime": "10/12/2013 11:05:00" ,
"endtime": "10/12/2013 12:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


(Matt Weber) #2

You will have to use a nested mapping [1] for the Availabilities field.
Then you can do a nested filter [2] similar to the following:

"constant_score" : {
"filter" : {
"nested": {
"path": "Availabilities",
"filter": {
"bool": {
"must": [
{"term": {"Availabilities.starttime": "10/12/2013
11:00:00/m"}},
{"range": {"Availabilities.endtime":
{"gte":"10/12/2013 11:00:00||+35m/m"}}}
]
}
}
}
}
}

Hope this helps.

Thanks,
Matt Weber

[1]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html
[2]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-nested-filter.html

On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V vvbiju2005@gmail.com wrote:

I have a search requirement , which Im trying to solve using Elastic
Search.

Assuming that the below JSON is indexed, what will be the query to fetch
an availability with starttime as 10/12/2013 11:00:00 and time period of 35
minutes . The search should return only the MeetingRoom1.

Im sure that I should be looking at date functions but not able to find a
correct query criteria that can be used . Any pointers?

Meeting Room 1:

{
"meetingroomid": 45,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 13:00:00"
},
{
"starttime": "10/12/2013 14:00:00" ,
"endtime": "10/12/2013 15:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

Meeting Room 2:

{
"meetingroomid": 100,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 11:01:00"
},
{
"starttime": "10/12/2013 11:05:00" ,
"endtime": "10/12/2013 12:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.


(Bijuv V) #3

Hi Matt

I did the below steps and the search doesnt return the correct data

HTTP PUT Request : http://localhost:9200/avails/avail/_mapping

{
"avail" :
{
"properties" :
{
"id": {type:"long"},
"location" : {"type" : "string"},
"availablities" :
{
"type" : "nested",
"properties":
{

            "endtime":{"type":"date", "format" :

"date_hour_minute_second"},
"starttime":{"type":"date" , "format" :
"date_hour_minute_second"}

        }


            }

    }
}

}

Inserted 2 records

{
avail:
{
"id": 1,
"location" : "us",
"availablities":
[
{
"starttime": "2013-12-10T10:00:00" ,
"endtime": "2013-12-10T13:00:00"
},
{
"starttime": "2013-12-10T14:00:00" ,
"endtime": "2013-12-10T15:30:00"
},
{
"starttime": "2013-12-10T16:00:00" ,
"endtime": "2013-12-10T18:00:00"
}
]
}
}

{
avail:
{
"id": 2,
"location" : "uk",
"availablities":
[
{
"starttime": "2013-12-10T10:00:00" ,
"endtime": "2013-12-10T11:00:00"
},
{
"starttime": "2013-12-10T11:05:00" ,
"endtime": "2013-12-10T11:30:00"
},
{
"starttime": "2013-12-10T17:00:00" ,
"endtime": "2013-12-10T18:00:00"
}
]
}
}

Then performed the search

http://localhost:9200/avails/_search

{"query":{
"filtered": {
"query": {
"match_all": {}
},
"filter": {
"nested": {
"path": "availablities",
"filter": {
"bool": {
"must": [
{
"term": {
"availablities.starttime":
"2013-12-10T11:00:00/m"
}
},
{
"range": {
"availablities.endtime": {
"gte": "2013-12-10T11:00:00||+35m/m"
}
}
}
]
}
}
}
}
}
}
}

Now the issues -

a. The starttime is not 11:00 AM in my data but its 10:00AM. So how does
the equality match of starttime to 11:00AM match?

b. when I tried the same, it complains that date is malformed at /m.

Regards

On Tue, Dec 10, 2013 at 9:15 PM, Matt Weber matt.weber@gmail.com wrote:

You will have to use a nested mapping [1] for the Availabilities field.
Then you can do a nested filter [2] similar to the following:

"constant_score" : {
"filter" : {
"nested": {
"path": "Availabilities",
"filter": {
"bool": {
"must": [
{"term": {"Availabilities.starttime": "10/12/2013
11:00:00/m"}},
{"range": {"Availabilities.endtime":
{"gte":"10/12/2013 11:00:00||+35m/m"}}}
]
}
}
}
}
}

Hope this helps.

Thanks,
Matt Weber

[1]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html
[2]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-nested-filter.html

On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V vvbiju2005@gmail.com wrote:

I have a search requirement , which Im trying to solve using Elastic
Search.

Assuming that the below JSON is indexed, what will be the query to fetch
an availability with starttime as 10/12/2013 11:00:00 and time period of 35
minutes . The search should return only the MeetingRoom1.

Im sure that I should be looking at date functions but not able to find a
correct query criteria that can be used . Any pointers?

Meeting Room 1:

{
"meetingroomid": 45,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 13:00:00"
},
{
"starttime": "10/12/2013 14:00:00" ,
"endtime": "10/12/2013 15:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

Meeting Room 2:

{
"meetingroomid": 100,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 11:01:00"
},
{
"starttime": "10/12/2013 11:05:00" ,
"endtime": "10/12/2013 12:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

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

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com
.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to a topic in the
Google Groups "elasticsearch" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CALU-2jwv%3DVuuSM36Rt-nfSv8x%2BjLvMn4nHbGoPBuDHjWgMX3ng%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.


(Bijuv V) #4

I got it working - overlooked the term match

{"query":{
"filtered": {
"query": {
"match_all": {}
},
"filter": {
"nested": {
"path": "availablities",
"filter": {
"bool": {
"must": [
{
"range": {
"availablities.starttime":{"lte":
"2013-12-10T11:00:00"}
}
},
{
"range": {
"availablities.endtime": {
"gte": "2013-12-10T11:35:00"
}
}
}
]
}
}
}
}
}
}
}

On Wed, Dec 11, 2013 at 11:41 PM, Bijuv V vvbiju2005@gmail.com wrote:

Hi Matt

I did the below steps and the search doesnt return the correct data

HTTP PUT Request : http://localhost:9200/avails/avail/_mapping

{
"avail" :
{
"properties" :
{
"id": {type:"long"},
"location" : {"type" : "string"},
"availablities" :
{
"type" : "nested",
"properties":
{

            "endtime":{"type":"date", "format" :

"date_hour_minute_second"},
"starttime":{"type":"date" , "format" :
"date_hour_minute_second"}

        }


            }

    }
}

}

Inserted 2 records

{
avail:
{
"id": 1,
"location" : "us",
"availablities":
[
{
"starttime": "2013-12-10T10:00:00" ,
"endtime": "2013-12-10T13:00:00"
},
{
"starttime": "2013-12-10T14:00:00" ,
"endtime": "2013-12-10T15:30:00"
},
{
"starttime": "2013-12-10T16:00:00" ,
"endtime": "2013-12-10T18:00:00"
}
]
}
}

{
avail:
{
"id": 2,
"location" : "uk",
"availablities":
[
{
"starttime": "2013-12-10T10:00:00" ,
"endtime": "2013-12-10T11:00:00"
},
{
"starttime": "2013-12-10T11:05:00" ,
"endtime": "2013-12-10T11:30:00"
},
{
"starttime": "2013-12-10T17:00:00" ,
"endtime": "2013-12-10T18:00:00"
}
]
}
}

Then performed the search

http://localhost:9200/avails/_search

{"query":{
"filtered": {
"query": {
"match_all": {}
},
"filter": {
"nested": {
"path": "availablities",
"filter": {
"bool": {
"must": [
{
"term": {
"availablities.starttime":
"2013-12-10T11:00:00/m"
}
},
{
"range": {
"availablities.endtime": {
"gte": "2013-12-10T11:00:00||+35m/m"
}
}
}
]
}
}
}
}
}
}
}

Now the issues -

a. The starttime is not 11:00 AM in my data but its 10:00AM. So how does
the equality match of starttime to 11:00AM match?

b. when I tried the same, it complains that date is malformed at /m.

Regards

On Tue, Dec 10, 2013 at 9:15 PM, Matt Weber matt.weber@gmail.com wrote:

You will have to use a nested mapping [1] for the Availabilities field.
Then you can do a nested filter [2] similar to the following:

"constant_score" : {
"filter" : {
"nested": {
"path": "Availabilities",
"filter": {
"bool": {
"must": [
{"term": {"Availabilities.starttime": "10/12/2013
11:00:00/m"}},
{"range": {"Availabilities.endtime":
{"gte":"10/12/2013 11:00:00||+35m/m"}}}
]
}
}
}
}
}

Hope this helps.

Thanks,
Matt Weber

[1]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html
[2]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-nested-filter.html

On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V vvbiju2005@gmail.com wrote:

I have a search requirement , which Im trying to solve using Elastic
Search.

Assuming that the below JSON is indexed, what will be the query to fetch
an availability with starttime as 10/12/2013 11:00:00 and time period of 35
minutes . The search should return only the MeetingRoom1.

Im sure that I should be looking at date functions but not able to find
a correct query criteria that can be used . Any pointers?

Meeting Room 1:

{
"meetingroomid": 45,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 13:00:00"
},
{
"starttime": "10/12/2013 14:00:00" ,
"endtime": "10/12/2013 15:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

Meeting Room 2:

{
"meetingroomid": 100,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 11:01:00"
},
{
"starttime": "10/12/2013 11:05:00" ,
"endtime": "10/12/2013 12:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

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

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com
.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to a topic in the
Google Groups "elasticsearch" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CALU-2jyRXUfqQFqFTA%2BLqAx3xqC24ry406x3E7-UBpjvdq9RNg%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.


(Matt Weber) #5

Glad this worked... I see your example doesn't use the date math which
means you must be calculating the endtime yourself. If you wanted
elasticsearch to calculate the correct endtime based on a given amount of
time you can use the date math [1] functionality like I do below:

curl -XPOST 'http://localhost:9200/avails/_search' -d '{
"query": {
"constant_score" : {
"filter" : {
"nested": {
"path": "availablities",
"filter": {
"bool": {
"must": [
{"range": {"availablities.starttime":
{"lte":"2013-12-10T11:00:00||/m"}}},
{"range": {"availablities.endtime":
{"gte":"2013-12-10T11:00:00||+35m/m"}}}
]
}
}
}
}
}
}
}'

Thanks,
Matt Weber

[1]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-date-format.html#date-math

On Wed, Dec 11, 2013 at 2:56 PM, Bijuv V vvbiju2005@gmail.com wrote:

I got it working - overlooked the term match

{"query":{
"filtered": {
"query": {
"match_all": {}
},
"filter": {
"nested": {
"path": "availablities",
"filter": {
"bool": {
"must": [
{
"range": {
"availablities.starttime":{"lte":
"2013-12-10T11:00:00"}
}
},
{
"range": {
"availablities.endtime": {
"gte": "2013-12-10T11:35:00"
}
}
}
]
}
}
}
}
}
}
}

On Wed, Dec 11, 2013 at 11:41 PM, Bijuv V vvbiju2005@gmail.com wrote:

Hi Matt

I did the below steps and the search doesnt return the correct data

HTTP PUT Request : http://localhost:9200/avails/avail/_mapping

{
"avail" :
{
"properties" :
{
"id": {type:"long"},
"location" : {"type" : "string"},
"availablities" :
{
"type" : "nested",
"properties":
{

            "endtime":{"type":"date", "format" :

"date_hour_minute_second"},
"starttime":{"type":"date" , "format" :
"date_hour_minute_second"}

        }


            }

    }
}

}

Inserted 2 records

{
avail:
{
"id": 1,
"location" : "us",
"availablities":
[
{
"starttime": "2013-12-10T10:00:00" ,
"endtime": "2013-12-10T13:00:00"
},
{
"starttime": "2013-12-10T14:00:00" ,
"endtime": "2013-12-10T15:30:00"
},
{
"starttime": "2013-12-10T16:00:00" ,
"endtime": "2013-12-10T18:00:00"
}
]
}
}

{
avail:
{
"id": 2,
"location" : "uk",
"availablities":
[
{
"starttime": "2013-12-10T10:00:00" ,
"endtime": "2013-12-10T11:00:00"
},
{
"starttime": "2013-12-10T11:05:00" ,
"endtime": "2013-12-10T11:30:00"
},
{
"starttime": "2013-12-10T17:00:00" ,
"endtime": "2013-12-10T18:00:00"
}
]
}
}

Then performed the search

http://localhost:9200/avails/_search

{"query":{
"filtered": {
"query": {
"match_all": {}
},
"filter": {
"nested": {
"path": "availablities",
"filter": {
"bool": {
"must": [
{
"term": {
"availablities.starttime":
"2013-12-10T11:00:00/m"
}
},
{
"range": {
"availablities.endtime": {
"gte": "2013-12-10T11:00:00||+35m/m"
}
}
}
]
}
}
}
}
}
}
}

Now the issues -

a. The starttime is not 11:00 AM in my data but its 10:00AM. So how does
the equality match of starttime to 11:00AM match?

b. when I tried the same, it complains that date is malformed at /m.

Regards

On Tue, Dec 10, 2013 at 9:15 PM, Matt Weber matt.weber@gmail.com wrote:

You will have to use a nested mapping [1] for the Availabilities field.
Then you can do a nested filter [2] similar to the following:

"constant_score" : {
"filter" : {
"nested": {
"path": "Availabilities",
"filter": {
"bool": {
"must": [
{"term": {"Availabilities.starttime":
"10/12/2013 11:00:00/m"}},
{"range": {"Availabilities.endtime":
{"gte":"10/12/2013 11:00:00||+35m/m"}}}
]
}
}
}
}
}

Hope this helps.

Thanks,
Matt Weber

[1]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html
[2]
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl-nested-filter.html

On Tue, Dec 10, 2013 at 11:37 AM, Bijuv V vvbiju2005@gmail.com wrote:

I have a search requirement , which Im trying to solve using Elastic
Search.

Assuming that the below JSON is indexed, what will be the query to
fetch an availability with starttime as 10/12/2013 11:00:00 and time period
of 35 minutes . The search should return only the MeetingRoom1.

Im sure that I should be looking at date functions but not able to find
a correct query criteria that can be used . Any pointers?

Meeting Room 1:

{
"meetingroomid": 45,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 13:00:00"
},
{
"starttime": "10/12/2013 14:00:00" ,
"endtime": "10/12/2013 15:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

Meeting Room 2:

{
"meetingroomid": 100,
"Availablities":
[
{
"starttime": "10/12/2013 10:00:00" ,
"endtime": "10/12/2013 11:01:00"
},
{
"starttime": "10/12/2013 11:05:00" ,
"endtime": "10/12/2013 12:30:00"
},
{
"starttime": "10/12/2013 16:00:00" ,
"endtime": "10/12/2013 18:00:00"
}
]
}

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

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/84aabd42-39e4-4de5-925d-9a579dd6a204%40googlegroups.com
.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to a topic in the
Google Groups "elasticsearch" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/elasticsearch/Rd3Bg9qeyws/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoDsr0q-bUkHb%3DkxH%2BhRYKZ2m5gspRZqeSX1KDe%2B_dTViQ%40mail.gmail.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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CALU-2jyRXUfqQFqFTA%2BLqAx3xqC24ry406x3E7-UBpjvdq9RNg%40mail.gmail.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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAJ3KEoBkJ457mWJh%3D4ppEs5oLRST1gDfCiv1oa55Mg_d-rbC8w%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.


(system) #6