# Help with a Query DSL **URL:** \ **Category:** Elasticsearch\ **Created:** [December 20, 2013, 8:16pm UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977 "2013-12-20T20:16:08Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 20, 2013, 8:16pm UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/1 "2013-12-20T20:16:08Z")
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](mailto: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](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](https://groups.google.com/groups/opt_out). ---
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 21, 2013, 1:00am UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/2 "2013-12-21T01:00:10Z")
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](mailto: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](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](https://groups.google.com/groups/opt_out). ---
**Author:** ![sina\_tamanna](https://avatars.discourse-cdn.com/v4/letter/s/a6a055/32.png) [@sina\_tamanna](https://discuss.elastic.co/u/sina_tamanna)\ **Post date:** [December 21, 2013, 11:06am UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/3 "2013-12-21T11:06:45Z")
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](mailto: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](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](https://groups.google.com/groups/opt_out). ---
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 21, 2013, 11:42am UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/4 "2013-12-21T11:42:42Z")
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](mailto: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](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](https://groups.google.com/groups/opt_out). ---
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 21, 2013, 12:45pm UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/5 "2013-12-21T12:45:13Z")
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](mailto: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](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](https://groups.google.com/groups/opt_out). ---
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 21, 2013, 11:01pm UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/6 "2013-12-21T23:01:27Z")
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](mailto: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](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](https://groups.google.com/groups/opt_out). ---
**Author:** ![Bijuv\_V](https://avatars.discourse-cdn.com/v4/letter/b/b77776/32.png) [@Bijuv\_V](https://discuss.elastic.co/u/Bijuv_V)\ **Post date:** [December 23, 2013, 1:43pm UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/7 "2013-12-23T13:43:50Z")
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](mailto: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](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](https://groups.google.com/groups/opt_out). ---
**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, 1:59am UTC](https://discuss.elastic.co/t/help-with-a-query-dsl/14977/8 "2017-07-06T01:59:37Z")