N1QL for like queries

SQL++
1

I am quite stumped here whereas to why all my N1QL queries are and mostly not using an index even when forced :confused:

I have 70k documents of this structure
{
"__key": “rate::198929”,
“active”: 1,
“code”: “3851”,
“connection_fee”: 0,
“destination”: “Croatia Fixed - Zagreb”,
“failed_call_duration”: 0,
“first_interval”: 60,
“grace_period”: 0,
“id”: 198929,
“import_id”: 1,
“increment_interval”: 60,
“rateGroup”: 3,
“rate_status”: “Active”,
“rpm”: 0.1039,
“type”: “rate”
}

I need a N1QL query as such:
SELECT id, name, destination, rpi, code, type FROM bucket WHERE type=“rate” AND code LIKE “385%” ORDER BY createdOn DESC;

My index thinking was the following
CREATE INDEX rate_code ON bucket(type,TOSTRING(code)) where type=“rate” USING GSI;

my explain of - SELECT code FROM zipt USE INDEX(rate_index_code USING GSI) WHERE type=“rate” AND code LIKE “385%”;

{
“requestID”: “b0bbaf2a-fc0a-4632-ab86-2a5d23d545b9”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “primary_index”,
“keyspace”: “bucket”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “bucket”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(((bucket.type) = “rate”) and ((bucket.code) like “385%”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(bucket.code)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “18.274591ms”,
“executionTime”: “18.153236ms”,
“resultCount”: 1,
“resultSize”: 1510
}
}

It never uses the index :confused:

my system:indexes:
bq> select * from system:indexes;
{
“requestID”: “9e91dc6f-3469-46b7-bf93-97741ba22ec9”,
“signature”: {
"": ""
},
“results”: [
{
“indexes”: {
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “d4f2884933cf7d0”,
“index_key”: [],
“keyspace_id”: “bucket”,
“name”: “primary_index”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
},
{
“indexes”: {
“condition”: “(type = rate)”,
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “9b3bb325a18fa9f9”,
“index_key”: [
"type",
“to_string(code)”
],
“keyspace_id”: “bucket”,
“name”: “rate_index_code”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
},
{
“indexes”: {
“condition”: “(type = “rate”)”,
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “e7a7b948a8d5f31”,
“index_key”: [
"type",
“to_string(code)”
],
“keyspace_id”: “bucket”,
“name”: “rate_index_code2”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “156.795327ms”,
“executionTime”: “156.69033ms”,
“resultCount”: 3,
“resultSize”: 1392
}
}

What am I doing wrong?!
How to best use n1ql indexes for this type of query?

2

Try the following. Note the addition of TOSTRING():

3

Seems like this like query is case sensitive. How can I do a case insensitive like query?

4

The predicate expression has to match the index key expression. Since you used TOSTRING(code) as the index key, you’d need to use the same expression in the predicate as well.

FYI:
If you have WHERE type = “rate” in the create index, you don’t need to have the type as additional index key as well.

As for attribute (key) reference, JSON is case sensitive. You’d need to use correct case.

For comparing/searching the values in case insensitive way, you have two options.

  1. Use LOWER() in the index key to convert to lower case and use LOWER in query predicate as well.
  2. Checkout TOKENS(), which has bit more flexibility.

See details in: N1QL: A Practical Guide - The Couchbase Blog

5

OK, Thanks for the reply!