FTS sort not working correctly

Full Text Search
9

There are multiple problems here …

  1. The intersect scan between GSI and FTS is possibly causing the query to scramble the output despite ordering within the FTS search. The intersect scan needs to be avoided for FTS to handle this query properly - try your query with USE INDEX(USING FTS) for this.
  2. We’ve found a bug as highlighted here: How to configure the FTS scoring? - #15 by abhinav , I’ve tracked it internally with https://issues.couchbase.com/browse/MB-44356. We’ve addressed it for an upcoming release. That said there’s a workaround for this.
  3. Also if you’re trying to retrieve fields from your documents (in your example a_app_release_date) - I’d recommend you to “store” it when adding the field as a number within your FTS index definition. By doing this, you can improve the latency of your query by retrieving the field from the search_meta().

Try this query and you should see results you expect …

SELECT search_meta().fields.a_app_release_date
FROM `search` AS a USE INDEX(USING FTS)
WHERE sub_type="me"
    AND SEARCH(a,{ "fields": [ "a_app_release_date" ], "highlight": {}, "sort": [{ "by" : "field", "field" : "a_app_release_date", "mode" : "max", "missing" : "last", "type": "number" }], "query": { "match": "dark" }, "size": 100, "from": 0 })
1 Like