Filtering by DateRange

Full Text Search
1

Hi,
I am struggling with a simple date filtering and not sure if I am doing the filtering wrong or missing something in my configuration.
I have the following document being indexed:

{
    "_class": "com.package.User",
    "email": "jdoe@email.com",
    "createdBy": "system",
    "createdAt": 1614905631637,
}

and trying to filter by createdAt field

The filter by date code looks as:

...
if (createdAtStart != null || createdAtEnd != null) {
    DateRangeQuery dateRangeQuery = SearchQuery.dateRange();
    if (createdAtStart != null) {
        dateRangeQuery = dateRangeQuery.start(createdAtStart.toInstant(), true);
    }
    if (createdAtEnd != null) {
        dateRangeQuery = dateRangeQuery.end(createdAtEnd.toInstant(), true);
    }
    searchQueries.add(dateRangeQuery.field("createdAt"));
}

And the query that is being executed is:

{"conjuncts":[{"field":"_class","match_phrase":"com.package.User"},{"start":"2021-03-04T01:09:39.422Z","end":"2021-03-06T01:09:39.422Z","inclusive_start":true,"inclusive_end":true,"field":"createdAt"}]}

That query returns no results.

My index definitions is:

{
  "type": "fulltext-index",
  "name": "search_idx",
  "uuid": "2426c752dcfa607c",
  "sourceType": "couchbase",
  "sourceName": "identity",
  "sourceUUID": "1990e653ab4a86458102e5cee0fcdd3f",
  "planParams": {
    "maxPartitionsPerPIndex": 171,
    "indexPartitions": 6
  },
  "params": {
    "doc_config": {
      "docid_prefix_delim": "",
      "docid_regexp": "",
      "mode": "type_field",
      "type_field": "type"
    },
    "mapping": {
      "analysis": {},
      "default_analyzer": "standard",
      "default_datetime_parser": "dateTimeOptional",
      "default_field": "_all",
      "default_mapping": {
        "dynamic": true,
        "enabled": true
      },
      "default_type": "_default",
      "docvalues_dynamic": true,
      "index_dynamic": true,
      "store_dynamic": false,
      "type_field": "_type"
    },
    "store": {
      "indexType": "scorch"
    }
  },
  "sourceParams": {}
}

I also tried adding a Date/Time Parser but that didn’t work either.
I was thinking that maybe the problem is that I am storing the date as Long since I am using java.util.Date?
Thanks!

2

The datetime field expects a JSON value of type string.
The dateTimeOptional parser expects the string to be in ISO-8601 format (ISO 8601 - Wikipedia).

Your index definition is what we call a default dynamic index - meaning you’re not setting anything specific in it and the index will index everything in the document by recognizing the data type of every JSON value.

The data type for the JSON entry of “createdAt” is numeric - so it won’t work for your query.

I’d consider these options …

  • Model your data differently - datetime field createdAt to be a string
  • Set it to an ISO-8601 format should you simply want to use the default dateTimeOptional datetime parser
  • Think about but not necessarily (based on your use case) setting up a precise index definition - indexing only what you need to avoid building a very large index.
1 Like
3

Hi @abhinav,
Thanks for the clear explanation. I ended up changing the type to String with ISO-8601 and now is working as expected.
Mauro.