Nested search query in Elasticsearch with range filter and unknown names of inner fields

Question

I am storing data in elasticsearch database and am trying to query it, filtered by range of numbers.

This is minimized structure of a document in my database:

"a": {
    "b": {
        "x": [1, 2, 3, 4], // note: x, y and z are not compulsory
        "y": [2, 3, 4], // some documents can have only x or z or maybe x and z etc.
        "z": [5, 6]
    }
}

Now I want my query to return all documents, where there is in any of subfields of "b" at least one number in the range between 2 and 4. Important thing here is that I don't know the names of all subfields of "b".

The query I came up with is:

POST /i/t/_search
{
    "query": {
        "query_string": {
            "fields": ["a.b.*"],
            "query": "number:[2 TO 4]"
        }          
    }
}

The query doesn't rise an error, but also returns no result. I am not really even sure, which kind of search query would even be most appropriate for such task. I only used Query String because it was the only one I found where I could join unknown field name and range.

Answer 1

Try to remove number: in your query:

{
    "query": {
        "query_string": {
            "fields": ["a.b.*"],
            "query": "[2 TO 4]"
        }          
    }
}

Optionally try count:[2 TO 4] (docs)

Another type of query that allows use * wildcard in field path is Multi Match Query but honestly I don't see how to combine it with numeric range in your case. The only thing that comes to my mind is :

{
  "query": {
    "bool": {
      "should": [ 
         {
            "multi_match": {
              "query": "2",
              "fields": ["a.b.*"]
            }
          },
          {
            "multi_match": {
              "query": "3",
              "fields": ["a.b.*"]
            }
          },
          {
            "multi_match": {
              "query": "4",
              "fields": ["a.b.*"]
            }
          }
      ]
    }
  }
}

... which is pretty ugly. The first solution with query_string is short and powerful.