How to index multiple elements in ARRAY along with CONCATENATE operator?

Couchbase Server
1

Hi CB Community,

Does any one know how to index a query which has 2 elements of its ARRAY and the predicate contains CONCATENATE operator.

select count(*) as cvalue from config where __t=‘irdb-net’ and ANY cfg in config.codes satisfies ‘30697’ like cfg.cc || cfg.ndc ||‘%’ END;

Here is how the data looks like:

{
“__t”: “irdb-net”,
“bands”: [
3,
6,
16,
60,
61,
63
],
“brandname”: “Cosmote”,
“codes”: [
{
“cc”: “30”,
“ndc”: “697”,
“primary”: 1,
“timezone”: “Europe/Athens”
},
{
“cc”: “30”,
“ndc”: “698”,
“primary”: 0,
“timezone”: “Europe/Athens”
},
{
“cc”: “30”,
“ndc”: “97”,
“primary”: 0,
“timezone”: “Europe/Athens”
}
],
“countryid”: 375,

Based on the ARRAY INDEX documentation examples, I am able to create following index.

CREATE INDEX pratiktest_index ON config (ALL ARRAY v.cc FOR v IN config.codes END) where __t=‘irdb-net’;

Sadly it does not help to reduce its response time! :frowning:
Any suggestions ?

2

Checkout Array Indexing | Couchbase Docs
Example 3, 5, 6, 7

SELECT COUNT(1) as cvalue
FROM config AS c
WHERE c.__t = "irdb-net"
      AND ANY cfg IN c.codes SATISFIES cfg.cc = "30" AND cfg.ndc = "697" END;

CB 7.1.0+

CREATE INDEX ix1 ON config(DISTINCT ARRAY FLATTEN_KEYS(cfg.cc, cfg.ndc) FOR cfg IN codes END ) WHERE __t = "irdb-net";

Pre CB 7.1.0

   CREATE INDEX ix1 ON config(DISTINCT ARRAY cfg.cc FOR cfg IN codes END ) WHERE __t = "irdb-net";

   OR

   CREATE INDEX ix1 ON config(DISTINCT ARRAY cfg.ndc FOR cfg IN codes END ) WHERE __t = "irdb-net";

   OR

  CREATE INDEX ix1 ON config(DISTINCT ARRAY [cfg.cc, cfg.ndc] FOR cfg IN codes END ) WHERE __t = "irdb-net";
  SELECT COUNT(1) as cvalue
  FROM config AS c
  WHERE c.__t = "irdb-net"
      AND ANY cfg IN c.codes SATISFIES [cfg.cc, cfg.ndc] = ["30", "697"] END;
3

Hi @vsr1 thank you for your response last week. I tried all 3 Index suggestions, however I am unable to gain improvement in query response time. Is it so that my query has LIKE operator plus the 2 elements of ARRAY (cc, ndc) are joined with concatenate operator. Could this be blocking the usage of index ?

If no, can you tell me how can I achieve index pushdown ?

Regards,
Pratik

4

If you need to use LIKE, more than 1 field from ARRAY and performance gain you need 7.1.0

5

@vsr1 We are using CB 7.1.1 now. Looking at your response of May 23, what were you planning to suggest then ?

6

@vsr1 We are using CB 7.1.1 now. Looking at your response of May 23, what were you planning to suggest then ?

In your first response, I see you have provided “CREATE INDEX” stmt for CB 7.1.0+, however just above that you have re-written our N1QL query without “LIKE” clause. So I believe that is not the suggestion you were referring to ?

7 
CREATE INDEX ix1 ON config(DISTINCT ARRAY FLATTEN_KEYS(cfg.cc, cfg.ndc) FOR cfg IN codes END ) WHERE __t = "irdb-net";

SELECT COUNT(1) as cvalue
FROM config AS c
WHERE c.__t = "irdb-net"
      AND ANY cfg IN c.codes SATISFIES cfg.cc = "30" AND cfg.ndc LIKE "697%" END;
8

Hi @vsr1 , thanks for quick response.
Our query has string “30697” compared with concatenation of fields:

select count(*) as cvalue from config where __t=‘irdb-net’ and ANY cfg in config.codes satisfies ‘30697’ like cfg.cc || cfg.ndc ||’%’ END;

In your query, I see you have compared field “cfg.cc” with first 2 digits and field “cfg.ndc” with remaining 3 digits. Our Customer’s requirement is that, our query should even work if “cfg.cc” comprises of first 1 or first 3 digits and “cfg.ndc” comprising of later 4 or later 2 digits respectively.

9 
CREATE INDEX ix1 ON config(DISTINCT ARRAY FLATTEN_KEYS(cfg.cc||cfg.ndc) FOR cfg IN codes END ) WHERE __t = "irdb-net";
SELECT COUNT(1) as cvalue
FROM config AS c
WHERE c.__t = "irdb-net"
      AND ANY cfg IN c.codes SATISFIES cfg.cc|| cfg.ndc LIKE "30697%" END;