ElasticSearch - search for any nested field that is in range

Question

I have the next filed as part of an elastic element:

"PayPlan" : {   
        "ActivePlans" : {  
            "plan1" : {   
                "startsOn" : "1",   
                "endsOn" : "999999"   
            }   
        },   
        "someOtherData" : [   
            NumberLong(0),   0]   
    },   

plan names are completely without logic (can be 'plan2323a' or 'plan_hh_jj' and so on).

How can I search for ALL the elements that have ANY plan that the startsOn is smaller then X and endsOn is bigger than X? Thank you all

I am unable to do this with query_string or by using range on query and using the next format "PayPlan.ActivePlans.*.startsOn" (the asterisk did not work as a wildcard in range

Thank you all

This is the elasticsearch query I have working now but I want to change 'plan1' into '*' so it will search for any sub plan:

{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "bool": {
          "must": [
            {
              "match_all": {}
            },
            {
              "or": {
                "filters": [
                  {
                    "bool": {
                      "must": [
                        {
                          "range": {
                            "PayPlan.ActivePlans.plan1.startsOn": {
                              "lte": "1234"
                            }
                          }
                        },
                        {
                          "range": {
                            "PayPlan.ActivePlans.plan1.endsOn": {
                              "gte": "1236"
                            }
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Answer 1

You could start with a query string like:

GET test1/_search
{
  "query": {
    "query_string": {
      "default_field": "PayPlan.ActivePlans.plan*.startsOn",
      "query":  ">0"
    }
  }
}

The output (with a quick test run):

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "test1",
        "_type": "plan",
        "_id": "AVq000G1mKJs7uLU8liY",
        "_score": 1,
        "_source": {
          "PayPlan": {
            "ActivePlans": {
              "plan2": {
                "startsOn": "2",
                "endsOn": "999998"
              }
            }
          }
        }
      },
      {
        "_index": "test1",
        "_type": "plan",
        "_id": "AVq00p0pmKJs7uLU8liW",
        "_score": 1,
        "_source": {
          "PayPlan": {
            "ActivePlans": {
              "plan1": {
                "startsOn": "1",
                "endsOn": "999999"
              }
            }
          }
        }
      }
    ]
  }
}