Index for SPLIT or SUFFIXES on nested field

SQL++
,
1

I have documents with the following format (I have omitted many fields to focus on what I am trying to query for):

{
  "deviceInfo": [
    {
      "circuitId": "123-45-67890/DEVICE TYPE/123456789"
    }
  ]
}

I need a query against circuitId that either matches exactly on a sub-string if you split on “/” or allows for the use of LIKE. Here’s sample query for each case I described:

SELECT meta(document).id, document
FROM bucket AS document
WHERE ANY device IN document.deviceInfo SATISFIES "DEVICE TYPE" IN SPLIT(LOWER(device.circuitId), "/") END;

SELECT meta(document).id, document
FROM bucket AS document
WHERE ANY device IN document.deviceInfo SATISFIES device.circuitId LIKE "%DEVICE TYPE%" END;

The problem is that these queries rely on a scan of the primary index and are therefore too slow for use in our application. I need to create an index (or, perhaps, multiple indexes) to make one or both of these queries faster.

I have tried to follow the examples found here A Couchbase Index Technique for LIKE Predicates With Wildcard - DZone Database but have had no luck with getting my queries to use any index I end up creating.

I would be grateful for any help in creating indexes.

2

Based on your requirement you need to use approach that suggested in the link. As the field is inside deviceInfo ARRAY you need nested array index. If data is too big the index size can be issue. In EE you can use partition index.
As article suggested, don’t have to use leading %. See if query covers just projection document key use covering index and use SDKs to fetch whole document

If you don’t need leading % , want exact match each word of by separating (or LIKE search is with in the word) /. Then use SPLIT in following that reduces number of entries in the index drastically.

CREATE INDEX ix1 ON bucket( DISTINCT ARRAY (DISTINCT SUFFIXES(LOWER(di.circuitId)))
                                                                                                FOR di IN deviceInfo END) ;

SELECT meta(d).id
FROM bucket AS d
WHERE ANY di IN d.deviceInfo SATISFIES (ANY v IN SUFFIXES(LOWER(di.circuitId)) SATISFIES v LIKE   "DEVICE TYPE%"  END) END;

SELECT meta(d).id
FROM bucket AS d
WHERE ANY di IN d.deviceInfo SATISFIES (ANY v IN SUFFIXES(LOWER(di.circuitId)) SATISFIES v =   "DEVICE TYPE"  END) END;
3

Thanks, that worked. The nested ANY/SATISIFIES was throwing me off, I think. Due to the index size constraints I went with the SPLIT method instead of the SUFFIXES method.

CREATE INDEX `circuit_id_search` ON `bucket`((DISTINCT (ARRAY (DISTINCT (SPLIT(LOWER((`device`.`circuitId`)), "/"))) FOR `device` in `deviceInfo` END)));

SELECT meta(document).id, document
FROM bucket AS document
WHERE ANY device IN document.deviceInfo SATISFIES (ANY v in SPLIT(LOWER(device.circuitId), "/") SATISFIES v LIKE LOWER("9024702%") END) END;

The index is used whether I look for an exact match or a prefix match, so that’s a bonus.

4

Leading % also works but need to scan whole index may impact latency.