Filter result based on a count of inner data

Question

I am building my search query for some listing data. As part of the search people can ask for multiple rooms which sleeps a min amount of people, ie two rooms which sleep 2 and 3 people.

Im not sure how I can perform that with a filter.

Here is a shortened search query so far.

    {
  "query":{
    "filtered":{
      "query":{
        "match_all":{}
      }
    }
  },
  "filter":{
    "and":
      [
        {
          "term":{
          "status":"live"
          }
        },
        {
          "geo_bounding_box":{
            "location":{
              "top_left":"60.856553, -8.64935719999994",
              "bottom_right":"49.8669688, 1.76270959999999"
              }
            }
          }
        ,{
        "range":{
          "bedrooms":{
            "gte":"2"
            }
          }
        }
      ]
    }
  ,
  "size":10
}

Test Data

{
       "took":1,
       "timed_out":false,
       "_shards":{
          "total":5,
          "successful":5,
          "failed":0
       },
       "hits":{
          "total":3,
          "max_score":1.0,
          "hits":[
             {
                "_index":"listings",
                "_type":"listing",
                "_id":"1",
                "_score":1.0,
                "_source":{
                   "name:":"Listing One",
                   "address1":"Some Street",
                   "bedrooms":2,
                   "city":"A City",
                   "id":1,
                   "refno":"FI451",
                   "user_id":1,
                   "rooms":[
                      {
                         "bathroom":"Shared bathroom with bath",
                         "double_standard":null,
                         "id":5,
                         "single":2,
                         "sleeps":2,
                         "title":"Twinny",
                      },
                      {
                         "bathroom":"Ensuite with bath",
                         "double_king_size":1,
                         "double_standard":1,
                         "id":1,
                         "single":null,
                         "sleeps":2,
                         "title":"Double Ensuite Room",
                      }
                   ]
                }
             },
             {
                "_index":"listings",
                "_type":"listing",
                "_id":"2",
                "_score":1.0,
                "_source":{
                   "name":"Listing Two",
                   "address1":"Some Street",
                   "bedrooms":2,
                   "city":"A City",
                   "id":2,
                   "refno":"BL932",
                   "user_id":1,
                   "rooms":[
                      {
                         "bathroom":"Ensuite with bath",
                         "double_standard":1,
                         "id":4,
                         "single":1,
                         "sleeps":3,
                         "title":"Family Room",
                      },
                      {
                         "bathroom":"Ensuite with shower",
                         "double_standard":1,
                         "id":2,
                         "single":null,
                         "sleeps":2,
                         "title":"Single Room",
                      }
                   ]
                }
             },
             {
                "_index":"listings",
                "_type":"listing",
                "_id":"3",
                "_score":1.0,
                "_source":{
                   "name":"Listing Three",
                   "address1":"Another Address",
                   "bedrooms":1,
                   "city":"Your City",
                   "id":3,
                   "refno":"TE2116",
                   "user_id":1,
                   "rooms":[
                      {
                         "bathroom":"Ensuite with shower",
                         "double_king_size":null,
                         "double_standard":1,
                         "id":3,
                         "single":1,
                         "sleeps":3,
                         "title":"Family Room",
                      }
                   ]
                }
             }
          ]
       }
    }

If you look at my data I have 3 listings, two of them have multiple rooms (Listing One & Two) but only Listing Two would match my search, Reason it has one room with that sleeps two and the other sleeps three.

Is it possible to perform this query with elasticsearch?

Sorry, I reread your question several times but I am still not sure what you meant by "rooms.sleeps two or more 2 to be greater or equal to two". It would be also nice to know what you expect to get as a result of your query. — imotov, Mar 21 '13 at 04:02
Hi imotov, I have update the data as I made a slight error and the explanation at the footer Im hoping will help. — Lee, Mar 21 '13 at 11:27

score 1 · Accepted Answer · answered Mar 23 '13 at 00:08

1

If what you want is "Find all listings where a bedroom sleeps 2 AND another bedroom sleeps 3", this query will work. It makes one big assumptions: that you are using inner objects, and not the Nested data type.

This query is using the fact that inner objects are collapsed into a single field, causing "rooms.sleeps" to equal [2,3] for the desired field. Since the field is collapsed into a single array, a simple Terms query will match them. When you change the execution mode to And, it forces both 2 and 3 to be matched.

The caveat is that a room that has [2,3,4] will also be matched.

I've omitted the geo and status portion since that data wasn't provided in the source documents.

{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      }
    }
  },
  "filter": {
    "and": [
      {
        "range": {
          "bedrooms": {
            "gte": "2"
          }
        }
      },
      {
        "terms": {
          "rooms.sleeps": [2,3],
          "execution": "and"
        }
      }
    ]
  },
  "size": 10
}

answered Mar 23 '13 at 00:08

Zach

9,591
1
38
33

Hi Zach, Thank you for the answer but adding the filter gives me 0 results. – Lee Mar 24 '13 at 15:12
Are you using inner objects or nested objects? This only works with inner objects. Can you gist your complete mapping and a few sample docs to insert? It's hard to help without a complete recreation of your environment. It worked on my end, but I was using a mapping derived from your posted data which may not be 100% the same. – Zach Mar 24 '13 at 15:25
Hi Zach, Ty for such a quick response. Here is a gist with data, mapping and query. But for the data I had commented out the sleeps filter: https://gist.github.com/weblee/ff0a8dd17c97bcb12b38 – Lee Mar 24 '13 at 15:36
Hi Zach, After all that you were correct. My DATA was off so not giving me the results I expected. TY very much. – Lee Mar 27 '13 at 11:22
Awesome! Sorry for not following up when you posted the gist, completely overlooked it. Glad you got everything working! – Zach Mar 27 '13 at 11:59

score 0 · Answer 2 · answered Mar 27 '13 at 02:08

As far as I know the filter has to be a sibling of the query inside the filtered element. See: http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query/

If you combine that with Zach's solution it should work.

{
    "query":
    {
        "filtered":
        {
            "query":
            {
                "match_all":{}
            },
            "filter":
            {
                "put" : "your filter here"
            }
        }
    }
}

Filter result based on a count of inner data

Test Data

2 Answers2