I’m trying to create a FTS index to use with a DateRangeQuery (in the .NET SDK) on a field that stores dates in the format ‘yyyy-MM-dd’ (no timestamp) but I’m unable to create a custom date parser using the UI in CB 5.5 and I’ve been unable to find any documentation on how to do this using the REST API. Is there an example somewhere of how to do this?
Can you assist here? We’re hitting a wall on this problem at the last minute before a deadline. We thought we’d be doing a simple range comparison in FTS on a date, but since our field has only the date and not the time component we’re having a hard time.
Thanks,
Brant
Hey Brant,
I’m no longer with Couchbase, so you may get better answers directly from the FTS team, but I would expect parsing a date in this format to be straightforward. The date formats used by FTS and bleve are in the format used by the Go time package. The date format you show here would be configured using the format string:
2006-01-02
This format is pretty common, and one of the ones bleve should recognize automatically. You can see it is one of the formats we try automatically here:
As a next step, it’d be helpful to look at actual mapping and document to reproduce the issue. Also we should check the query as well, the date range queries seem to be tricky to configure sometimes and I wouldn’t be surprised to find issues in the SDK related to this.
Query:
{
“query”: {
“must”: {
“conjuncts”: [
{
“start”: “2019-03-15”,
“inclusive_start”: true,
“end”: “9999-12-31”,
“inclusive_end”: true,
“field”: “expiresOn”
}
]
}
}
}
Documents:
{
“type”: “waiver”,
“keyId”: “waivers-waiver-1-1”,
“expiresOn”: “2019-01-02”,
“service”: “waivers”
}
{
“type”: “waiver”,
“keyId”: “waivers-waiver-1-2”,
“expiresOn”: “2020-01-02”,
“service”: “waivers”
}
Index:
{
“type”: “fulltext-index”,
“name”: “idx_search”,
“sourceType”: “couchbase”,
“sourceName”: “primary”,
“planParams”: {
“maxPartitionsPerPIndex”: 171
},
“params”: {
“doc_config”: {
“docid_prefix_delim”: “”,
“docid_regexp”: “”,
“mode”: “type_field”,
“type_field”: “type”
},
“mapping”: {
“default_analyzer”: “standard”,
“default_datetime_parser”: “dateTimeOptional”,
“default_field”: “_all”,
“default_mapping”: {
“default_analyzer”: “”,
“dynamic”: false,
“enabled”: false
},
“default_type”: “_default”,
“docvalues_dynamic”: true,
“index_dynamic”: true,
“store_dynamic”: false,
“type_field”: “_type”,
“types”: {
“waiver”: {
“default_analyzer”: “standard”,
“dynamic”: false,
“enabled”: true,
“properties”: {
“expiresOn”: {
“enabled”: true,
“dynamic”: false,
“fields”: [
{
“name”: “expiresOn”,
“type”: “datetime”,
“store”: false,
“index”: true,
“include_term_vectors”: true,
“include_in_all”: false
}
]
}
}
}
}
},
“store”: {
“indexType”: “upside_down”,
“kvStoreName”: “mossStore”
}
},
“sourceParams”: {}
}
Response from REST API :
{
“status”: {
“total”: 6,
“failed”: 0,
“successful”: 6
},
“request”: {
“query”: {
“must”: {
“conjuncts”: [
{
“start”: “2019-03-15T00:00:00Z”,
“end”: “9999-12-31T00:00:00Z”,
“inclusive_start”: true,
“inclusive_end”: true,
“field”: “expiresOn”
}
]
}
},
“size”: 10,
“from”: 0,
“highlight”: null,
“fields”: null,
“facets”: null,
“explain”: false,
“sort”: [
“-_score”
],
“includeLocations”: false
},
“hits”: ,
“total_hits”: 0,
“max_score”: 0,
“took”: 226700,
“facets”: null
}
Note that the response from couchbase here has added a timestamp to the start and end dates that was not present in the request sent to the REST API.
Hi Dan,
This issue of not getting a response is due to high value upper bound given here in query=> “end”: “9999-12-31”.
If you give an “end” value, smaller than year 2262 (2261) it should work for you.
(During some date parsing code paths- we try to get the nanoseconds from the date value given.
The Unix time in nanoseconds cannot be represented by an int64 for any date before the year 1678 or after 2262.)
thanks,
Sreekanth
Dan,
Aside, I just noted that you are using the older “upside_down” indexing format for FTS in your index definition.
Since 6.0, we have the newer, smaller and better performing indexing format called “scorch”, and we highly recommend you to try that out.
Cheers!
@sreeks would it make sense to catch this early and return an error to the user?
1 Like
The right analysis by @sreeks.
@Dan_Grim This is a go unix time since limitation with int64.
We’d recommend running your query as:
curl -XPOST -H "Content-type:application/json" http://<username>:<password>@<ip>:8094/api/index/<index_name>/query -d '{"query": {"must": {"conjuncts":[{"start":"2019-03-15", "inclusive_start":true, "field":"expiresOn"}]}}}'
@daschl It makes sense to catch this early and return an error to the user, let me track it with a ticket (Loading...)
Even I am having a hard time in searching dates with yyyy-mm-dd format.
Here is the query:
{
“explain”: true,
“fields”: [
" * "
],
“highlight”: {},
“query”: {“must”: {“conjuncts”:[{“start”:“1990-10-28”,“end”:“1990-10-28”, “inclusive_start”:true,“inclusive_end”:true, “field”:" * "}]}}
}
Response:
{
“status”: {
“total”: 6,
“failed”: 0,
“successful”: 6
},
“request”: {
“query”: {
“must”: {
“conjuncts”: [
{
“start”: “1990-10-28T00:00:00Z”,
“end”: “1990-10-28T00:00:00Z”,
“inclusive_start”: true,
“inclusive_end”: true,
“field”: " * "
}
]
}
},
“size”: 10,
“from”: 0,
“highlight”: {
“style”: null,
“fields”: null
},
“fields”: [
“*”
],
“facets”: null,
“explain”: true,
“sort”: [
“-_score”
],
“includeLocations”: false
},
“hits”: ,
“total_hits”: 0,
“max_score”: 0,
“took”: 497503,
“facets”: null
}
@abhinav @sreeks
My objective is to search for birth date…but not able to accomplish it. I have the date specified in query in the index, but query is not hitting that
Hi @Rajeev_Bhat,
Not sure why you made this a boolean query where as it could have been just a date range query.
ref - https://docs.couchbase.com/server/6.5/fts/fts-query-types.html#date-range
Any reason for not specifying the birth date field in the query instead of “*” value?
Try exact indexed field there.
If you just wanted to search on birth date, ie if it is of exact point look up on date field (with no range query requirements like find all ppl whose birth date falls between so and so), you don’t need to index it like a date field. Just indexing your data like a text field with keyword analyser would help with point look up.
Thanks,
Sreekanth
1 Like
Thanks @sreeks for your response. I was just referring to the above query by abhinav.
This query by specifying the date field works!
But just curious why " * " does not work in this case.
cheers,
Rajeev
If you don’t specify the exact field, the default analyser configured against the default field in the index definition page under the “Advanced” section will be applied to the query parameters which would have text analysed them incorrectly. So it is important to specify the field indexed too while querying. (field scope the query)
Please go over - https://docs.couchbase.com/server/current/fts/fts-creating-indexes.html
1 Like
If you don’t specify the exact field, the default analyser configured against the default field in the index definition page under the “Advanced” section will be applied to the query parameters which would have text analysed them incorrectly.
Though the above comment is very valid in many such non working query cases, It isn’t quite right here with dateRange query as the date (start/end)parsing happens before inspecting the given query field. But here not giving the right field value creates empty looks up err later on. I am not getting into the specifics here which might not be very relevant to you.
1 Like