I have items for rent. The User specifies a start_date and end_date. Every item has multiple blocked_periods also with start and end date.
Goal:
Query all available items. Lets say: 12.11., 13.11., 14.11., 15.11.
Blocked are 13. and 14.
The item should be available on 12. or 15. or from 12. until 15. but start and end date can't be on 13. and 14.
Current Index:
{
"development_items" : {
"aliases" : { },
"mappings" : {
"item" : {
"properties" : {
"blocked_periods" : {
"type" : "nested",
"properties" : {
"end_date" : {
"type" : "date",
"format" : "yyyy-MM-dd"
},
"start_date" : {
"type" : "date",
"format" : "yyyy-MM-dd"
}
}
}
}
}
},
"settings" : {
"index" : {
"creation_date" : "1503327829680",
"number_of_shards" : "5",
"number_of_replicas" : "1",
"uuid" : "9b9BhF-ITta2dlCKRLrnfA",
"version" : {
"created" : "2040499"
}
}
},
"warmers" : { }
}
}
Current Query:
{
bool: {
must_not: {
nested: {
path: 'blocked_periods',
query: {
bool: {
should: [
{
bool: {
must: [
{
range: {
'blocked_periods.start_date': {
lte: start_date
}
}
},
{
range: {
'blocked_periods.end_date': {
gte: end_date
}
}
}
]
}
}
]
}
}
}
}
}
}
You WANT documents where the given start isn't in a blocked period and the given end isn't in a blocked period. Another way of saying this is
you DONT want documents where the given start is in a blocked period or where the given end is in a blocked period (!A AND !B === !(A OR B)).
If we stick with your mapping and do this the nested way, heres how:
{
"query": {
"bool": {
"must_not": [
{
"nested": {
"path": "blocked_periods",
"query": {
"bool": {
"should": [
{
"bool": {
"must": [
{
"range": {
"blocked_periods.start_date": {
"lte": "START"
}
}
},
{
"range": {
"blocked_periods.end_date": {
"gte": "START"
}
}
}
]
}
},
{
"bool": {
"must": [
{
"range": {
"blocked_periods.start_date": {
"lte": "END"
}
}
},
{
"range": {
"blocked_periods.end_date": {
"gte": "END"
}
}
}
]
}
}
]
}
}
}
}
]
}
}
}
However if you can handle changing the names of your fields from start_date/end_date to gte/lte, I think you'll find the date_range approach preferable.
PUT my_index
{
"mappings": {
"item": {
"properties": {
"blocked_periods": {
"type": "date_range",
"format": "yyyy-MM-dd"
}
}
}
}
}
POST my_index/item/1
{
"blocked_periods": [
{
"gte": "2020-1-10",
"lte": "2020-1-15"
},
{
"gte": "2020-1-17",
"lte": "2020-1-25"
}
]
}
GET my_index/_search
{
"query": {
"bool": {
"must_not": [
{
"term": {
"blocked_periods": "START"
}
},
{
"term": {
"blocked_periods": "END"
}
}
]
}
}
}