Elastic search - Searching for asset availibility

Question

i was wondering if there would be a way to use elastic search to make a query that returns only objects that are available in a certain date range? how to structure the data?

What i would need is to query a db giving the starting and ending date, and finding all objects that available in that period for the whole duration between start and end date?

{object-available:
{
{start:'01/01/2012', end:'03/02/2012'},
{start:'05/05/2012', end:'31/12/2012'}
}

And searching for object that is available between 01/01/2012 - 15/01/2012 should return this object, but searching for 01/03/2012 - 01/04/2012 should not return it.

Answer 1

It can be done by storing availability ranges as Nested Objects and then using Nested Query or Filter to check that both start and end date fall into the desired date range. You can perform this check using Bool Query with two must clauses containing Date Range Queries. For example:

# Delete old version to make sure new settings are applied
curl -XDELETE "localhost:9200/dates-test/"
echo
# Create a new index with proper mapping 
# See http://www.elasticsearch.org/guide/reference/index-modules/analysis/pathhierarchy-tokenizer.html
curl -XPUT "localhost:9200/dates-test" -d '{
    "mappings": {
        "doc": {
            "properties": {
                "name": {"type": "string"},
                "object-available": {
                    "type": "nested",
                    "properties" : {
                        "end" : {
                            "type" : "date"
                        },
                        "start" : {
                            "type" : "date"
                        }
                    }
                }
            }
        }
    }
}'
echo
# Put some test data
curl -XPUT "localhost:9200/dates-test/doc/1" -d '{
    "name": "Record 1",
    "object-available":[
        {"start":"2012-01-01", "end":"2012-02-03"},
        {"start":"2012-05-05", "end":"2012-12-31"}
    ]
}
'
curl -XPUT "localhost:9200/dates-test/doc/2" -d '{
    "name": "Record 2",
    "object-available":[
        {"start":"2012-02-01", "end":"2012-04-20"},
        {"start":"2012-04-25", "end":"2012-11-30"}
    ]
}
'
curl -XPOST "localhost:9200/dates-test/_refresh"
echo
echo Test for the range 2011-12-01 - 2012-02-05. Should find only 1st record 
curl -XPOST "localhost:9200/dates-test/doc/_search?pretty=true" -d '{
    "query": {
        "nested": {
            "path": "object-available",
            "query": {
                "bool": {
                    "must": [
                        {
                            "range": {
                                "start": {
                                    "from": "2011-12-01",
                                    "to": "2012-02-05"
                                }
                            }
                        },
                        {
                            "range": {
                                "end": {
                                    "from": "2011-12-01",
                                    "to": "2012-02-05"
                                }
                            }
                        }

                    ]
                }
            }
        }
    }
}'
echo
echo Test for the range 2012-01-20 - 2012-12-01. Should find only 2nd record 
curl -XPOST "localhost:9200/dates-test/doc/_search?pretty=true" -d '{
    "query": {
        "nested": {
            "path": "object-available",
            "query": {
                "bool": {
                    "must": [
                        {
                            "range": {
                                "start": {
                                    "from": "2012-01-20",
                                    "to": "2012-12-01"
                                }
                            }
                        },
                        {
                            "range": {
                                "end": {
                                    "from": "2012-01-20",
                                    "to": "2012-12-01"
                                }
                            }
                        }

                    ]
                }
            }
        }
    }
}'
echo
echo Test for the range 2012-04-01 - 2013-01-01. Should find both record 
curl -XPOST "localhost:9200/dates-test/doc/_search?pretty=true" -d '{
    "query": {
        "nested": {
            "path": "object-available",
            "query": {
                "bool": {
                    "must": [
                        {
                            "range": {
                                "start": {
                                    "from": "2012-04-01",
                                    "to": "2013-01-01"
                                }
                            }
                        },
                        {
                            "range": {
                                "end": {
                                    "from": "2012-04-01",
                                    "to": "2013-01-01"
                                }
                            }
                        }

                    ]
                }
            }
        }
    }
}'