elasticsearch nested range query

Question

Suppose i want this structure for a document:

{
  "hours": {
    "open": [
      {
        "start": 10,
        "end": 19
      },
      {
        "start": 21,
        "end": 29
      }
      ...
    ],
    "closed": [
      {
        "start": 100,
        "end": 199
      },
      {
        "start": 201,
        "end": 299
      }
      ...
    ]
  }
}

whose index has this mapping:

{
  "mappings": {
    "_doc": {
      "properties": {
        "hours": {
          "properties": {
            "open": {
              "type": "nested",
              "properties": {
                "start": { "type": "integer" },
                "end": { "type": "integer" }
              }
            },
            "closed": {
              "type": "nested",
              "properties": {
                "start": { "type": "integer" },
                "end": { "type": "integer" }
              }
            }
          }
        }
      }
    }
  }
}

In the Elasticsearch Query DSL, how do i find all documents where 20 lies inside an open segment and not inside a closed segment. The query I tried was incorrect.

failed query

{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "must": [
              {
                "nested": {
                  "path": "hours.open",
                  "query": {
                    "range": {
                      "hours.open.start": { "lte": 20 }
                    }
                  }
                }
              },
              {
                "nested": {
                  "path": "hours.open",
                  "query": {
                    "range": {
                      "hours.open.end": { "gte": 20 }
                    }
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "bool": {
                  "must": [
                    {
                      "nested": {
                        "path": "hours.closed",
                        "query": {
                          "range": {
                            "hours.closed.start": { "lte": 20 }
                          }
                        }
                      }
                    },
                    {
                      "nested": {
                        "path": "hours.closed",
                        "query": {
                          "range": {
                            "hours.closed.end": { "gte": 20 }
                          }
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

whats wrong with my query? it is returning this document which is not what i intended. 20 does not lie inside an open segment.

Please show the query you tried

Val
– Val

2018-05-25 08:28:11 +00:00
Commented May 25, 2018 at 8:28 — Val
– Val, Commented May 25, 2018 at 8:28

Vincent · Accepted Answer · 2018-05-26 05:16:14Z

I finally got it working. The following is the correct query:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "hours.open",
            "query": {
              "bool": {
                "must": [
                  { "range": { "hours.open.start": { "lte": 20 } } },
                  { "range": { "hours.open.end": { "gte": 20 } } }
                ]
              }
            }
          }
        }
      ],
      "must_not": [
        {
          "nested": {
            "path": "hours.closed",
            "query": {
              "bool": {
                "must": [
                  { "range": { "hours.closed.start": { "lte": 20 } } },
                  { "range": { "hours.closed.end": { "gte": 20 } } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

With that said, it looks like my original attempt was wrong because there were two different hours.open nested path queries and likewise two different hours.closed nested path queries. The parser must only take one of them for a single path.

Aman B · Accepted Answer · 2018-05-25 09:34:54Z

0

Seems like you need to swap lte and gte:

"hours.open.start": { "gte": 20 }
"hours.open.end": { "lte": 20 }

and same for the closing times:

"hours.closed.start": { "gte": 20 }
"hours.closed.end": { "lte": 20 }

Edit: The must and must_not need to be part of the same bool query:

{
    "query": {
        "bool": {
            "must": [{
                    "nested": {
                        "path": "hours.open",
                        "query": {
                            "range": {
                                "hours.open.start": {
                                    "gte": 20
                                }
                            }
                        }
                    }
                },
                {
                    "nested": {
                        "path": "hours.open",
                        "query": {
                            "range": {
                                "hours.open.end": {
                                    "lte": 20
                                }
                            }
                        }
                    }
                }
            ],
            "must_not": [{
                "bool": {
                    "must": [{
                            "nested": {
                                "path": "hours.closed",
                                "query": {
                                    "range": {
                                        "hours.closed.start": {
                                            "gte": 20
                                        }
                                    }
                                }
                            }
                        },
                        {
                            "nested": {
                                "path": "hours.closed",
                                "query": {
                                    "range": {
                                        "hours.closed.end": {
                                            "lte": 20
                                        }
                                    }
                                }
                            }
                        }
                    ]
                }
            }]
        }
    }
}

edited May 25, 2018 at 9:34

answered May 25, 2018 at 9:03

Aman B

2,4081 gold badge20 silver badges28 bronze badges

3 Comments

Vincent Over a year ago

your missing some braces/brackets

Aman B Over a year ago

@Vincent sorted

Vincent Over a year ago

this is still returning the document.

Collectives™ on Stack Overflow

elasticsearch nested range query

failed query

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

failed query

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related