Help with a Query DSL

Elastic Stack Elasticsearch
1

I have the data in the following format in Elastic Search (from sense)

POST slots/slot/1
{
locationid:"1",
roomid:"10",
starttime: "08:45"
}

POST slots/slot/2
{
locationid:"1",
roomid:"10",
starttime: "09:00"
}

POST slots/slot/3
{
locationid:"2",
roomid:"100",
starttime: "08:45"
}

POST slots/slot/4
{
locationid:"2",
roomid:"101",
starttime: "09:00"
}

POST slots/slot/5
{
locationid:"3",
roomid:"200",
starttime: "09:30"
}

In short , the data is in the following format.

A Location has multiple rooms and each room has multiple slots of 15
minutes. So slot 1 for Room10 starts at 8:45 and ends at 09:00, Slot 2 for
same room starts at 09:00 and ends at 09:15

Locationid RoomId Starttime
1 10 08:45
1 10 09:00
2 100 08:45
2 101 09:00
3 200 09:30

Im trying to write a query/filter which will give me all locations where a
room is available with two or three slots.

For e.g Find a location that has 08:45 slot and 09:00 slot (configurable)
Answer should be location 1 only
Should Not be location 2 as room 100 has 08:45 slot but not the 09:00 slot.
Room 101 has 09:00 slot but doesnt have the 08:45 slot

--
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/1ed8f122-f947-4be6-af79-0b54f1026334%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

2

Any pointers on the same would help.

Op vrijdag 20 december 2013 21:16:08 UTC+1 schreef Bijuv V:

I have the data in the following format in Elastic Search (from sense)

POST slots/slot/1
{
locationid:"1",
roomid:"10",
starttime: "08:45"
}

POST slots/slot/2
{
locationid:"1",
roomid:"10",
starttime: "09:00"
}

POST slots/slot/3
{
locationid:"2",
roomid:"100",
starttime: "08:45"
}

POST slots/slot/4
{
locationid:"2",
roomid:"101",
starttime: "09:00"
}

POST slots/slot/5
{
locationid:"3",
roomid:"200",
starttime: "09:30"
}

In short , the data is in the following format.

A Location has multiple rooms and each room has multiple slots of 15
minutes. So slot 1 for Room10 starts at 8:45 and ends at 09:00, Slot 2 for
same room starts at 09:00 and ends at 09:15

Locationid RoomId Starttime
1 10 08:45
1 10 09:00
2 100 08:45
2 101 09:00
3 200 09:30

Im trying to write a query/filter which will give me all locations where a
room is available with two or three slots.

For e.g Find a location that has 08:45 slot and 09:00 slot (configurable)
Answer should be location 1 only
Should Not be location 2 as room 100 has 08:45 slot but not the 09:00
slot. Room 101 has 09:00 slot but doesnt have the 08:45 slot

--
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/25861339-0a77-4969-a67f-084cd42b9aa4%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

3

It is a bit tricky to do such query, because the way the data is indexed. I
can't see an obvious solution but perhaps someone else knows better. Though
what I can suggest is that you change the structure of your index, if that
is a possibility.

Index you data by location_id and room_id and keep all the available time
slots in a single document as follows:

POST slots/slot/1
{
"location_id":1,
"room_id": 10,
"start_time": [ "08:45", "09:00"]
}

Now you could do a bool query with must on start_time:

{
"query": {
"bool": {"must": [
{"term": {
"start_time": {
"value": "08:45"
}
}},
{"term": {
"start_time": {
"value": "09:00"
}
}}
]}
}
}

I also suggest that you disable the analyzer on the start_time field. Hope
that helped!

On Friday, December 20, 2013 9:16:08 PM UTC+1, Bijuv V wrote:

I have the data in the following format in Elastic Search (from sense)

POST slots/slot/1
{
locationid:"1",
roomid:"10",
starttime: "08:45"
}

POST slots/slot/2
{
locationid:"1",
roomid:"10",
starttime: "09:00"
}

POST slots/slot/3
{
locationid:"2",
roomid:"100",
starttime: "08:45"
}

POST slots/slot/4
{
locationid:"2",
roomid:"101",
starttime: "09:00"
}

POST slots/slot/5
{
locationid:"3",
roomid:"200",
starttime: "09:30"
}

In short , the data is in the following format.

A Location has multiple rooms and each room has multiple slots of 15
minutes. So slot 1 for Room10 starts at 8:45 and ends at 09:00, Slot 2 for
same room starts at 09:00 and ends at 09:15

Locationid RoomId Starttime
1 10 08:45
1 10 09:00
2 100 08:45
2 101 09:00
3 200 09:30

Im trying to write a query/filter which will give me all locations where a
room is available with two or three slots.

For e.g Find a location that has 08:45 slot and 09:00 slot (configurable)
Answer should be location 1 only
Should Not be location 2 as room 100 has 08:45 slot but not the 09:00
slot. Room 101 has 09:00 slot but doesnt have the 08:45 slot

--
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/22999361-3263-49a0-b60f-3b22320b72d0%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

4

My attempt to the answer

POST slots/slot/_search?pretty=true&search_type=count
{
"facets": {
   "locationswithslots": {
      "terms": {
         "field": "locationid",
            "script" : "term + \"_\" + _source.roomid",
         "size": 10
      },
  
   "facet_filter":
   {         
            "terms": 
            {
               "starttime": 
               [
                  "08:45",
                  "09:00"
               ]
            }
       
    
   }
   }
}

}

This gives the answer as below

{
"took": 12,
"timed_out": false,
"_shards": {
  "total": 5,
  "successful": 5,
  "failed": 0
},
"hits": {
  "total": 5,
  "max_score": 0,
  "hits": []
 },
 "facets": {
  "locationswithslots": {
     "_type": "terms",
     "missing": 0,
     "total": 4,
     "other": 0,
     "terms": [
        {
           "term": "1_10",
           "count": 2
        },
        {
           "term": "2_101",
           "count": 1
        },
        {
           "term": "2_100",
           "count": 1
        }
     ]
   }
}
}

Now I need to figure out a way to filter the facets that return count 2 as
I passed in 2 slots in the filter.

Op zaterdag 21 december 2013 12:06:45 UTC+1 schreef Sina Tamanna:

It is a bit tricky to do such query, because the way the data is indexed.
I can't see an obvious solution but perhaps someone else knows better.
Though what I can suggest is that you change the structure of your index,
if that is a possibility.

Index you data by location_id and room_id and keep all the available time
slots in a single document as follows:

POST slots/slot/1
{
"location_id":1,
"room_id": 10,
"start_time": [ "08:45", "09:00"]
}

Now you could do a bool query with must on start_time:

{
"query": {
"bool": {"must": [
{"term": {
"start_time": {
"value": "08:45"
}
}},
{"term": {
"start_time": {
"value": "09:00"
}
}}
]}
}
}

I also suggest that you disable the analyzer on the start_time field.
Hope that helped!

On Friday, December 20, 2013 9:16:08 PM UTC+1, Bijuv V wrote:

I have the data in the following format in Elastic Search (from sense)

POST slots/slot/1
{
locationid:"1",
roomid:"10",
starttime: "08:45"
}

POST slots/slot/2
{
locationid:"1",
roomid:"10",
starttime: "09:00"
}

POST slots/slot/3
{
locationid:"2",
roomid:"100",
starttime: "08:45"
}

POST slots/slot/4
{
locationid:"2",
roomid:"101",
starttime: "09:00"
}

POST slots/slot/5
{
locationid:"3",
roomid:"200",
starttime: "09:30"
}

In short , the data is in the following format.

A Location has multiple rooms and each room has multiple slots of 15
minutes. So slot 1 for Room10 starts at 8:45 and ends at 09:00, Slot 2 for
same room starts at 09:00 and ends at 09:15

Locationid RoomId Starttime
1 10 08:45
1 10 09:00
2 100 08:45
2 101 09:00
3 200 09:30

Im trying to write a query/filter which will give me all locations where
a room is available with two or three slots.

For e.g Find a location that has 08:45 slot and 09:00 slot (configurable)
Answer should be location 1 only
Should Not be location 2 as room 100 has 08:45 slot but not the 09:00
slot. Room 101 has 09:00 slot but doesnt have the 08:45 slot

--
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/b7b71b17-64b9-4094-b5bf-1b1d5ea7c82e%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

5

Hi Sina

i tried your approach and something seems wrong -

PUT availswitharray

PUT availswitharray/avail/_mapping
{
"avail" :
{
"properties" :
{
"locationid": {type:"long"},
"roomid" : {"type" : "long"},
"starttime" : {"type":"date", "format": "hour_minute"}

    }
}

}

POST availswitharray/avail/1
{
locationid:"1",
roomid:"10",
starttime:[ "08:45","09:00"]
}

POST availswitharray/avail/3
{
locationid:"2",
roomid:"100",
startime:[ "08:45"]
}

POST availswitharray/avail/4
{
locationid:"2",
roomid:"101",
startime:[ "09:00" ]
}

POST availswitharray/avail/5
{
locationid:"3",
roomid:"200",
starttime:[ "09:30" ]

}

POST availswitharray/avail/_search?pretty=true
{
"filter":
{
"bool": {
"must":
[
{
"term":
{
"starttime": {"value": "09:00" }
}
}

  ]}

}
}

The search should return avail/4 and avail/1 but it returns only avail/1.

Op zaterdag 21 december 2013 12:06:45 UTC+1 schreef Sina Tamanna:

It is a bit tricky to do such query, because the way the data is indexed.
I can't see an obvious solution but perhaps someone else knows better.
Though what I can suggest is that you change the structure of your index,
if that is a possibility.

Index you data by location_id and room_id and keep all the available time
slots in a single document as follows:

POST slots/slot/1
{
"location_id":1,
"room_id": 10,
"start_time": [ "08:45", "09:00"]
}

Now you could do a bool query with must on start_time:

{
"query": {
"bool": {"must": [
{"term": {
"start_time": {
"value": "08:45"
}
}},
{"term": {
"start_time": {
"value": "09:00"
}
}}
]}
}
}

I also suggest that you disable the analyzer on the start_time field.
Hope that helped!

On Friday, December 20, 2013 9:16:08 PM UTC+1, Bijuv V wrote:

I have the data in the following format in Elastic Search (from sense)

POST slots/slot/1
{
locationid:"1",
roomid:"10",
starttime: "08:45"
}

POST slots/slot/2
{
locationid:"1",
roomid:"10",
starttime: "09:00"
}

POST slots/slot/3
{
locationid:"2",
roomid:"100",
starttime: "08:45"
}

POST slots/slot/4
{
locationid:"2",
roomid:"101",
starttime: "09:00"
}

POST slots/slot/5
{
locationid:"3",
roomid:"200",
starttime: "09:30"
}

In short , the data is in the following format.

A Location has multiple rooms and each room has multiple slots of 15
minutes. So slot 1 for Room10 starts at 8:45 and ends at 09:00, Slot 2 for
same room starts at 09:00 and ends at 09:15

Locationid RoomId Starttime
1 10 08:45
1 10 09:00
2 100 08:45
2 101 09:00
3 200 09:30

Im trying to write a query/filter which will give me all locations where
a room is available with two or three slots.

For e.g Find a location that has 08:45 slot and 09:00 slot (configurable)
Answer should be location 1 only
Should Not be location 2 as room 100 has 08:45 slot but not the 09:00
slot. Room 101 has 09:00 slot but doesnt have the 08:45 slot

--
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/6cacc77b-4dd2-4878-b30d-16a91c27342c%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

6

Got the problem - typos ! startime instead of starttime

Sense extension really helps in such things!

Op zaterdag 21 december 2013 13:45:13 UTC+1 schreef Bijuv V:

Hi Sina

i tried your approach and something seems wrong -

PUT availswitharray

PUT availswitharray/avail/_mapping
{
"avail" :
{
"properties" :
{
"locationid": {type:"long"},
"roomid" : {"type" : "long"},
"starttime" : {"type":"date", "format": "hour_minute"}

    }
}

}

POST availswitharray/avail/1
{
locationid:"1",
roomid:"10",
starttime:[ "08:45","09:00"]
}

POST availswitharray/avail/3
{
locationid:"2",
roomid:"100",
startime:[ "08:45"]
}

POST availswitharray/avail/4
{
locationid:"2",
roomid:"101",
startime:[ "09:00" ]
}

POST availswitharray/avail/5
{
locationid:"3",
roomid:"200",
starttime:[ "09:30" ]

}

POST availswitharray/avail/_search?pretty=true
{
"filter":
{
"bool": {
"must":
[
{
"term":
{
"starttime": {"value": "09:00" }
}
}

  ]}

}
}

The search should return avail/4 and avail/1 but it returns only avail/1.

Op zaterdag 21 december 2013 12:06:45 UTC+1 schreef Sina Tamanna:

It is a bit tricky to do such query, because the way the data is indexed.
I can't see an obvious solution but perhaps someone else knows better.
Though what I can suggest is that you change the structure of your index,
if that is a possibility.

Index you data by location_id and room_id and keep all the available
time slots in a single document as follows:

POST slots/slot/1
{
"location_id":1,
"room_id": 10,
"start_time": [ "08:45", "09:00"]
}

Now you could do a bool query with must on start_time:

{
"query": {
"bool": {"must": [
{"term": {
"start_time": {
"value": "08:45"
}
}},
{"term": {
"start_time": {
"value": "09:00"
}
}}
]}
}
}

I also suggest that you disable the analyzer on the start_time field.
Hope that helped!

On Friday, December 20, 2013 9:16:08 PM UTC+1, Bijuv V wrote:

I have the data in the following format in Elastic Search (from sense)

POST slots/slot/1
{
locationid:"1",
roomid:"10",
starttime: "08:45"
}

POST slots/slot/2
{
locationid:"1",
roomid:"10",
starttime: "09:00"
}

POST slots/slot/3
{
locationid:"2",
roomid:"100",
starttime: "08:45"
}

POST slots/slot/4
{
locationid:"2",
roomid:"101",
starttime: "09:00"
}

POST slots/slot/5
{
locationid:"3",
roomid:"200",
starttime: "09:30"
}

In short , the data is in the following format.

A Location has multiple rooms and each room has multiple slots of 15
minutes. So slot 1 for Room10 starts at 8:45 and ends at 09:00, Slot 2 for
same room starts at 09:00 and ends at 09:15

Locationid RoomId Starttime
1 10 08:45
1 10 09:00
2 100 08:45
2 101 09:00
3 200 09:30

Im trying to write a query/filter which will give me all locations where
a room is available with two or three slots.

For e.g Find a location that has 08:45 slot and 09:00 slot
(configurable)
Answer should be location 1 only
Should Not be location 2 as room 100 has 08:45 slot but not the 09:00
slot. Room 101 has 09:00 slot but doesnt have the 08:45 slot

--
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/6af25a1d-dbd5-464f-8447-d7b53c82bf3d%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

7

Any help from the experts?
Is this not possible using Elasticsearch query DSL or the only option is to
change the object structure as described below by Sina?

Op vrijdag 20 december 2013 21:16:08 UTC+1 schreef Bijuv V:

I have the data in the following format in Elastic Search (from sense)

POST slots/slot/1
{
locationid:"1",
roomid:"10",
starttime: "08:45"
}

POST slots/slot/2
{
locationid:"1",
roomid:"10",
starttime: "09:00"
}

POST slots/slot/3
{
locationid:"2",
roomid:"100",
starttime: "08:45"
}

POST slots/slot/4
{
locationid:"2",
roomid:"101",
starttime: "09:00"
}

POST slots/slot/5
{
locationid:"3",
roomid:"200",
starttime: "09:30"
}

In short , the data is in the following format.

A Location has multiple rooms and each room has multiple slots of 15
minutes. So slot 1 for Room10 starts at 8:45 and ends at 09:00, Slot 2 for
same room starts at 09:00 and ends at 09:15

Locationid RoomId Starttime
1 10 08:45
1 10 09:00
2 100 08:45
2 101 09:00
3 200 09:30

Im trying to write a query/filter which will give me all locations where a
room is available with two or three slots.

For e.g Find a location that has 08:45 slot and 09:00 slot (configurable)
Answer should be location 1 only
Should Not be location 2 as room 100 has 08:45 slot but not the 09:00
slot. Room 101 has 09:00 slot but doesnt have the 08:45 slot

--
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/084809da-a2ce-4982-83b5-0b10fd1ad50a%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.