How to match all item in nested array

The mapping:

 "working_hour": {
          "type": "nested",
          "properties": {
            "from": {
              "type": "long"
            }
          }
        }

Data example:

Record 1:

   "_source" : {
          "working_hour" : [
            {
              "from" : 3
            },
            {
              "from" : 6
            }
          ]
        }

record 2:

 "_source" : {
      "working_hour" : [
        {
          "from" : 6
        },
        {
          "from" : 6
        }
      ]
    }

query:

    "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "working_hour",
            "query": {
              "range": {
                "working_hour.from": {
                  "gte": 5,
                  "lte": 7
                }
              }
            }
          }
        }
      ]
    }
  }

My expected result only "record 2" will be returned. However, record 1 and record 2 return. How can I create a query?

Looks like a candiate for a range field?

This is working well:

DELETE test
PUT test
{
  "mappings": {
    "_doc": {
      "properties": {
        "working_hour": {
          "type": "nested",
          "properties": {
            "from": {
              "type": "long"
            }
          }
        }
      }
    }
  }
}
PUT test/_doc/1
{
  "working_hour": [
    {
      "from": 3
    },
    {
      "from": 6
    }
  ]
}
PUT test/_doc/2
{
  "working_hour": [
    {
      "from": 6
    },
    {
      "from": 6
    }
  ]
}
GET test/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "working_hour",
            "query": {
              "range": {
                "working_hour.from": {
                  "gte": 3,
                  "lte": 4
                }
              }
            }
          }
        }
      ]
    }
  }
}

The response is:

{
  "took" : 59,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "test",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "working_hour" : [
            {
              "from" : 3
            },
            {
              "from" : 6
            }
          ]
        }
      }
    ]
  }
}

Sorry, I made a typo mistake the query should be the following:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "working_hour",
            "query": {
              "range": {
                "working_hour.from": {
                  "gte": 5,
                  "lte": 7
                }
              }
            }
          }
        }
      ]
    }
  }
}

And expect record 2 return only

I presume that's the "contains" operator then if you use the new range field.
It will be more efficient to execute and easier to express these sorts of queries.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.