Nested integer_range mappings/query?

Elastic Stack Elasticsearch
1

Suppose i want this structure for a user document:

/users/_doc/1
{
  hours: {
    open: [
      {
        gte: 10,
        lt: 20
      },
      {
        gte: 30,
        lt: 40
      }
      ...
    ],
    closed: [
      {
        gte: 50,
        lt: 60
      },
      {
        gte: 70,
        lt: 80
      }
      ...
    ]
  }
}

  1. What's this document's required mapping?

  2. If I want to find all users where hours.open.contains(15) AND NOT hours.closed.contains(20), what would this query look like?

2

The hours index will have 2 separate nested fields; open and close

The open and close nested fields will have 2 fields inside each one: gte and lt of type integer

About the query: do you want 15 to be between gte and lt for the open field and 20 to NOT be between gte and lt for close field?

3

About the query: do you want 15 to be between gte and lt for the open field and 20 to NOT be between gte and lt for close field?

Yes. Override close hours for special holidays fall under the closed field so if i want to find something open now I perform this query. It doesnt matter but perhaps i should've used 15 in both conditions.

Also, could you show me the mapping/query?

4

I would recommend naming the gte as starttime and lt as endtime in both the open and close nested fields. This is mainly to avoid confusion while writing range queries because gte and lt have another meaning inside the nested query. Not that it would cause the query to fail but you will get confused just writing the query if the fields are named gte and lt. Having said that...

This is how I would define the mapping:

PUT indexname
{
    "mappings" : {
        "typename" : {
            "properties" : {
                "open" : {
					"type" : "nested",
					"properties" : {
						"starttime" : {
							"type" : "integer"
						},
						"endtime" : {
							"type" : "integer"
						}
					}
				},
				"close" : {
					"type" : "nested",
					"properties" : {
						"starttime" : {
							"type" : "integer"
						},
						"endtime" : {
							"type" : "integer"
						}
					}
				}
            }
        }
    }
}

The query would be:

GET /indexname/_search
{
	"size" : 0,
	"query" : {
		"bool"  : {
			"must" : [
				{
					"bool" : {
						"must" : [
							{
								"nested" : {
									"path" : "open",
									"query" : {
										"range" : {
											"starttime" : {
												"lte" : 15
											}
										}
									}
								}
							},
							{
								"nested" : {
									"path" : "open",
									"query" : {
										"range" : {
											"endtime" : {
												"gt" : 15
											}
										}
									}
								}
							}
						]
					}
				},
				{
					"bool" : {
						"must_not" : [
							{
								"bool" : {
									"must" : [
										{
											"nested" : {
												"path" : "close",
												"query" : {
													"range" : {
														"starttime" : {
															"lte" : 15
														}
													}
												}
											}
										},
										{
											"nested" : {
												"path" : "close",
												"query" : {
													"range" : {
														"endtime" : {
															"gt" : 15
														}
													}
												}
											}
										}
									]
								}
							}
						]
					}
				}
			]
		}
	}
}

Let me know if that helps.

5

Hmm I was hoping for the array elements to be of type integer_range. Is this even possible with this structure? Or is your way in no way less performant and I should just go with that. I’d be fine if there was no performance difference.

6

I don't believe that there would be any difference in performance but would be happy to be proven wrong:)

7

Ok thank you for your help!

8

It looks like @RahulD's answer was not quite right. It took me a bit to figure out 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 } } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

See this SO post i made. Thanks again.

9

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