How to convert a n1ql statement into mobile sql++?

Mobile Couchbase Lite
1

I use couchbase lite 1.4 on ios and i’m trying to upgrade to 3.0. i have a n1ql query i can run on the server, and i previously used a couchbase lite view on the ios app. i’m trying to get this to work in cbl 3.0 but i’m not able to get it working. anyone have ideas on how to make it work? in particular, the parallel arrays doesn’t seem to be working. i’m happy to use either sql++ or the query builder, whatever works.

here’s our document (simplifying a little bit):

docId: loc::1234

{

“delProfs”: [

{

“delProf”: {

“cds”: [

[

“1”,

“15”

],

[

“1”,

“15”

]

],

“routes”: [

“7442”,

“3000”

]

},

“start”: “2021-01-24”

}

]

}

I want to be able to pass in a date, route, and cd (“2023-04-01”, “7442”, “1”) and see all the docs which match. there’s a lot of nesting objects/arrays so i’m not sure how to write the query. i do have n1ql statement that we run on the server if it helps:

select meta(d).id

from dss d

where delProfs is not missing

AND ( ANY x in delProfs SATISFIES

( (ANY y in x.delProf.routes SATISFIES y=“$1” END)

AND (any z in ARRAY_RANGE(0,ARRAY_LENGTH(x.delProf.routes)) SATISFIES

x.delProf.routes[z]=“$1” AND ARRAY_CONTAINS(x.delProf.cds[z],“$2”) END)

) END)

in particular, cbl 3.0 doesnt seem to like this one:
any z in ARRAY_RANGE(0,ARRAY_LENGTH(x.delProf.routes))

i checked the link for which array functions aren’t supported for sql++ but i didn’t see these on the list. not sure if i need to replace this with something else?

1 Like
2

I am not sure if CBL 3.0 gives pos also when iterating ANY clause . If yes try this.

SELECT META(d).id
FROM dss AS d
WHERE ANY dp IN delProfs SATISFIES (ANY pos:r IN dp.delProf.routes SATISFIES r = $1 AND ARRAY_CONTAINS(dp.delProf.cds[pos],$2) END) END;
3

@jenglerprimo Couchbase Mobile SQL++ currently does not support ARRAY_RANGE or the “pos:r” syntax of ANY. We may extend it in a future release. I am not sure if you could restructure the doc JSON. If so, by tying up “routes” and “cds”, like,
“route_cds”: [ [“7442”, [“1”, “15”] ],
[“3000”, [“1”, “15”] ] ]
then, something like the following should work.

SELECT META().id FROM dss d WHERE delProfs IS NOT MISSING AND ANY x IN delProfs SATISFIES (ANY y IN x.delProf.route_cds SATISFIES y[0] = \"$1\" AND ARRAY_CONTAINS(y[1], \"$2\") END) END
9

Thanks for the suggestion. i found we have data in a slightly different format which allowed me to bypass the parallel arrays. i verified the query works in CBLite 3.0, but the problem is it’s very slow, around 30 seconds on my simulator and even worse on a phone with our data (around 1000 loc documents).

i saw a message from dec 2020 that array indexes are not supported on mobile yet (Array indexing with Couchbase Lite - #5 by priya.rajagopal)

is it possible to add an index to speed up this query? if not, then we’ll have to change our document structure. i’m hoping to avoid that.

edit: i noticed that createQuery takes a long time, but execute is very fast. if an index is not possible, perhaps i can just hold onto my query and execute it multiple times as needed? it is possible for documents to change throughout the day, so i’m hoping calling execute will pick it back up later.

{
    "delProfs": [
        {
            "delProf": {
                "seq": [
                    [
                    "7150",
                    "2",
                    0
                    ]
                    ]
            }
        }
    ]
}

select meta().id 
from _ 
where type = 'loc' 
and delProfs is not missing 
and ( ANY x in delProfs   SATISFIES    (any y in x.delProf.seq satisfies y[0]='7150' and y[1]='2' end)   END )
10

Thanks for sharing your your case. I am able to repro the slowness you experienced in v3.0. The good news is the performance has manifested in a separate SQL++ query, and we already have fixed it in latest 3.0 and 3.1.0 releases. If you want to stay in 3.0, the next 3.0 release will help you.
By the way, will it help you if we add a new function, ARRAY_RANGE, to our mobile SQL++ ?

11

Thanks for the response! we upgraded to 3.1.0 and verified the performance problem went away.

if we can make the original query work very fast, then i would prefer to use that since we have other systems using the same query. so the array_range / array_length would help. but if i cannot use the parallel arrays in a query that is fast, then i don’t think it will help me in particular. this is the only use case i have for array_range.