Use ARRAY_CONTAINS to detect ANY matches


#1

ARRAY_CONTAINS is perfect to check if an array contains a particular value. There is also a WHERE ANY WITHIN SATISFIES loop; is there a shortcut way to compare if any array contains ANY values from another array? The solution we have currently:

WHERE
ANY x WITHIN y
SATISFIES
ANY a WITHIN b
SATISFIES
ARRAY_CONTAINS(a.field1, x.field1) OR
ARRAY_CONTAINS(a.field2, x.field2)
END
END


#2

Hi, can you post some sample data. I don’t think there is a different solution, but we can take a look.


#3

Thanks Gerald - attaching here is a simplified excerpt (hopefully preserving the meaningful):

Product documents (PR_890256 and PR_577281)
[
{
“product”: {
“sku”: 577281
}
},
{
“product”: {
“sku”: 890256
}
}
]

Block document (BR_0001018242001NAT)
[
{
“block”: {
“skulist”: [
1238525,
123951,
440955,
577281,
“HEWQ5949A”
]
}
}
]

Query
SELECT ARRAY_AGG(product.product.sku) AS blocklist
FROM default product USE KEYS [“PR_890256”, “PR_577281”]
NEST default blocked ON KEYS [“BR_0001018242001NAT”]
WHERE
ANY sku WITHIN product
SATISFIES
ANY blockRule WITHIN blocked
SATISFIES
ARRAY_CONTAINS(blockRule.skulist, sku.sku)
END
END

Query Result
[
{
“blocklist”: [
577281
]
}
]


#4

Maybe I need an English description of your data and the results you want to produce. The document boundaries are not clear to me.

Another approach might be:

SELECT ... FROM ... WHERE sku IN (SELECT RAW ... FROM ...);

In the subquery, you would generate all values you want to match.


#5

Not sure if you’ve already solved this, but could you use ARRAY_INTERSECT to do this simply? If it returns an empty array, then no values overlap, so any non-empty array means at least some values overlap (and you’d also know which values overlapped, if that ever mattered).


#6

We were notified of this feature - thanks! Note it was Since Couchbase Server 4.5.1 so we will be upgrading to take advantage.


#7

I am little bit late, but this works like a charm:

ARRAY_LENGTH(ARRAY_INTERSECT(ckl.tagIds, [“val1”, “val2”])) > 0