Term on N1QL + SEARCH not working when value has many words like 'Practitioner/3033112248320'

[ssatthy]

I have index a field specifically.

“types”: {
“my-scope.Session”: {
“dynamic”: false,
“enabled”: true,
“properties”: {
“actor”: {
“dynamic”: false,
“enabled”: true,
“properties”: {
“reference”: {
“dynamic”: false,
“enabled”: true,
“fields”: [
{
“analyzer”: “keyword”,
“index”: true,
“name”: “reference”,
“type”: “text”
}
]
}
}
},

If I search FTS WEB UI directly, I get value.

{
“size”: 300,
“from”: 0,
“fields”: [
“*”
],
“query”: {
“must”: {
“conjuncts”: [
{
“field”: “actor.reference”,
“term”: “Practitioner/1014733033112248320”
}
]
}
},
“explain”: true
}

However, when I use N1QL + SEARCH, its. not returning

SELECT d.*
FROM mybucket.my-scope.Session d
WHERE SEARCH(d, {“must”:{“conjuncts”:[
{“field”:“actor.reference”,“term”:“Practitioner/1014733033112248320”}]}}
)
LIMIT 100
OFFSET 0

This does not return any value.

I suspect that ‘term’ used in N1QL + SEARCH is still tokenising the query value?

I have added ‘analyzer’: ‘keyword’ to the query. still does not work.

SELECT d.*
FROM mybucket.my-scope.Session d
WHERE SEARCH(d, {“must”:{“conjuncts”:[
{“field”:“actor.reference”,“term”:“Practitioner/1014733033112248320”, “analyzer”: “keyword”}]}}
)
LIMIT 100
OFFSET 0

I have tried ‘match’ instead of ‘term’. still not working.

Please help.

Couchbase Server Enterprise Edition 8.0.0 build 3777

[capemox]

Hi @ssatthy! I tried reproducing the issue, and it seems to be working for me. Could you provide any more details?

[ssatthy]

@ssatthy

{
“size”: 300,
“from”: 0,
“fields”: [
“*”
],
“query”: {
“must”: {
“conjuncts”: [
{
“field”: “actor.reference”,
“term”: “Practitioner/1014733033112248320”
}
]
}
},
“explain”: true
}

@capemox Thank you for responding.
When I use FTS, I get results. screenshot below.

When I use n1ql, I don’t get results,

its because the value is “Practitioner/1014733034915799040“.

if I try for single ‘word’, fts and n1ql both returns results.

screenshots.

So my assumption is that even though I specify ‘term’ in n1ql, the query analyzer is still tokenising the value ‘Practitioner/1014733034915799040‘ to ‘Practitioner’ and ‘1014733034915799040’, hence not matching the exact value of the field ‘actor.reference’ which is indexed as type: ‘text’, analyzer: ‘keyword’

Please let me know if anything specific I can provide. not sure what else I can provide.

[abhinav]

Hello @ssatthy , so your diagnosis on what’s happened here is partially accurate.

• If you run your SQL++ query as a covered request, I’m confident you would not run into this issue. Here’s an example, where I’m requesting only the doc IDs which the search index will be able to cover ..

select meta().id
FROM `my-bucket`.`my-scope`.session d
WHERE SEARCH(d, { "must": {"conjuncts": [ { "field":"actor.reference", "term": "Practitioner/1014733033112248320" } ] } })
LIMIT 100 OFFSET 0;

• When you run the SQL++ query, and are projecting the entire document to your SELECT predicate - this becomes an uncovered query and an additional KV fetch for document hits qualified by the search index becomes necessary.

SELECT d.* ...

• During the KV fetch, the query engine evaluator does not have context and will use the default settings of standard analysis - which would tokenize your string and fail the document. The approach to take to work around this is to provide this context (the index information) in the SEARCH function (third argument) within your query. Here’s how you adjust your SQL++ request ..

select d.*
FROM `my-bucket`.`my-scope`.session d
WHERE SEARCH(d, { "must": {"conjuncts": [ { "field":"actor.reference", "term": "Practitioner/1014733033112248320" } ] } }, {"index": "<full_search_index_name>"})
LIMIT 100 OFFSET 0;

Hope this helps.