Hey everyone,
I have a document which describes when an entry was/is valid. The entry can be valid for multiple time periods but these can never overlap. Now I want to query only documents which have been changed it's activity based on two timestamps.
I have tried to use the nested type which works good for many test cases. But the following case does have a match while it must not match the document.
The case is as follow:
Timestamp 1: 2022-03-02T00:00:00
Timestamp 2: 2022-11-01T00:00:00
The document was valid active from 2022-03-01 to 2022-05-01, which means it has to be interpreted as active for timestamp 1. It is also active since 2022-10-01, so it also active in timestamp 2.
Am I correct, that I get a match, because of the nested type the entries can not be queried in combination? I guess it can work with a script query but I would love not to use it.
Any advise if it is possible without a script query? Any ideas how I may change the document model to fulfill the query requirement?
PUT /test
{
"settings": {
"number_of_shards": 1
},
"mappings": {
"properties": {
"Activities": {
"type": "nested",
"properties": {
"ValidTo": {
"type": "date",
"null_value": "0001-01-01T00:00:00"
},
"ValidFrom": {
"type": "date"
}
}
}
}
}
}
POST test/_doc
{
"Id": "1792f96e-0911-48c5-9c94-40950eb90ab5",
"Name": "My Entry",
"Activities": [
{
"ValidFrom": "2022-03-01T00:00:00+00:00",
"ValidTo": "2022-05-01T00:00:00+00:00"
},
{
"ValidFrom": "2022-05-10T00:00:00+00:00",
"ValidTo": "2022-06-15T00:00:00+00:00"
},
{
"ValidFrom": "2022-10-01T00:00:00+00:00",
"ValidTo": null
}
]
}
POST test/_search
{
"query": {
"nested": {
"path": "Activities",
"query": {
"bool": {
"minimum_should_match": 1,
"should": [
{
"bool": {
"must": [
{
"bool": {
"must": [
{
"range": {
"Activities.ValidFrom": {
"lte": "2022-03-02T00:00:00"
}
}
},
{
"bool": {
"minimum_should_match": 1,
"should": [
{
"term": {
"Activities.ValidTo": {
"value": "0001-01-01T00:00:00"
}
}
},
{
"range": {
"Activities.ValidTo": {
"gt": "2022-03-02T00:00:00"
}
}
}
]
}
}
]
}
}
],
"must_not": [
{
"bool": {
"must": [
{
"range": {
"Activities.ValidFrom": {
"lte": "2022-11-01T00:00:00"
}
}
},
{
"bool": {
"minimum_should_match": 1,
"should": [
{
"term": {
"Activities.ValidTo": {
"value": "0001-01-01T00:00:00"
}
}
},
{
"range": {
"Activities.ValidTo": {
"gt": "2022-11-01T00:00:00"
}
}
}
]
}
}
]
}
}
]
}
},
{
"bool": {
"must": [
{
"bool": {
"must": [
{
"range": {
"Activities.ValidFrom": {
"lte": "2022-11-01T00:00:00"
}
}
},
{
"bool": {
"minimum_should_match": 1,
"should": [
{
"term": {
"Activities.ValidTo": {
"value": "0001-01-01T00:00:00"
}
}
},
{
"range": {
"Activities.ValidTo": {
"gt": "2022-11-01T00:00:00"
}
}
}
]
}
}
]
}
}
],
"must_not": [
{
"bool": {
"must": [
{
"range": {
"Activities.ValidFrom": {
"lte": "2022-03-02T00:00:00"
}
}
},
{
"bool": {
"minimum_should_match": 1,
"should": [
{
"term": {
"Activities.ValidTo": {
"value": "0001-01-01T00:00:00"
}
}
},
{
"range": {
"Activities.ValidTo": {
"gt": "2022-03-02T00:00:00"
}
}
}
]
}
}
]
}
}
]
}
}
]
}
}
}
}
}