Array vs. Value Index for ARRAY_CONTAINS

Mobile Couchbase Lite
, ,
1

Using Couchbase Lite Swift, suppose I have a document that looks like this, where clearedProjects is an array of UUID strings:

// Pseudocode:

Foo {
    "clearedProjects": [String]
}

To find all Foo documents that contain a certain value in clearedProjects, I run this:

SELECT Meta().id FROM `Foo` WHERE (ARRAY_CONTAINS(`clearedProjects`, "e45a4b6c-9c01-4819-9d38-4dc448149b79"))

If I want to speed up this query with an index, should I use a value index or an array index?

let idxValueConfig = ValueIndexConfiguration(["clearedProjects"])
let idxArrayConfig = ArrayIndexConfiguration("clearedProjects")

// Should I use `idxValueConfig` or `idxArrayConfig` in place of the XXX? -->
try fooCollection.createIndex(withName: "clearedProjectsIndex", config: XXX)

The docs for ArrayIndexConfiguration state that this type of index is specifically for UNNEST. Which type of index is best for using ARRAY_CONTAINS?

3

Check examples here you also need query to use UNNEST make it faster

If u need to use ARRAY_CONTAINS basically use idxValueConfig which index whole array as scalar. That can bolt index and may not able to do indexscan efficently it may have to do whole indexscan

4

@vsr1 That doesn’t appear to be the case. Using array_contains() with a value index in place, here’s the query plan showing that the entire table is being scanned:

SELECT fl_result(MasterCue.key) FROM "kv_.\Production.\Master\Cue" AS MasterCue WHERE array_contains(fl_value(MasterCue.body, 'clearedClients'), 'ad01b231-7ea2-454b-9713-1d89b4613594')

2|0|0| SCAN MasterCue

{"FROM":[{"AS":"MasterCue","COLLECTION":"MasterCue","SCOPE":"Production"}],"WHAT":[["_.",["meta()","MasterCue"],".id"]],"WHERE":["ARRAY_CONTAINS()",[".clearedClients"],"ad01b231-7ea2-454b-9713-1d89b4613594"]}

(I may be misunderstanding your post. Are you advising against using array_contains() in favor of using UNNEST? I interpreted it as the index should cover array_contains())

5 
let idxArrayConfig = ArrayIndexConfiguration("clearedProjects")

SELECT Meta(d).id FROM `Foo` AS d UNNEST d.clearedProjects AS p WHERE p =  "e45a4b6c-9c01-4819-9d38-4dc448149b79";

If this works it may be faster for you (as this Mobile you should check what they support)

6

@vsr1 Thanks. That uses the ArrayIndex and is much, much faster.

I’m building the missing Couchbase Swift SDK and translating Swift syntax into SQL++ query statements at compile time, so that you can just write this instead of futzing around with QueryBuilder or SQL++ directly:

let objects: [MasterCue] = modelContext.fetch(MasterCue.self, where: #Predicate<MasterCue> { $0.clearedClients.contains { $0.id == someID } })

That gives you 100% compiler-enforced type checking so that refactors and typos can’t break things. It also makes it a snap to adopt Couchbase for anyone who’s used SwiftData. The returned objects are also “live objects”—they update their properties as soon as the database changes.

I’ve essentially rebuilt SwiftData for Couchbase.

The only downside is that it’s harder to translate Swift Predicates into UNNEST than it is to parse them into ARRAY_CONTAINS, but I’ve got it done now. It gets tricky when there are conjunctions with multiple UNNESTs, etc.

(You don’t know anyone at Couchbase who might be interested in looking at merging this with the official Swift SDK, do you? The official one is very dated and very basic. You’ve got to reinvent the wheel anytime you want to build an app with it.)