What is the most efficient way to query an array using values from another array using SQL?

I am trying to retrieve items from my Cosmos DB using and SQL query. I am currently storing items within my Cosmos DB using the following structure...

{ "IDs": [ "123456789", "987654321", "121542535" ] }

Then I need to query my items from the Cosmos DB and find any items that have an array that contains any values from another array of Ids like below.

requiredIds = ["123456789", "4828927593"]

What is the most efficient to do this in SQL?

I have tried using the ARRAY_CONTAINS method, but that only supports searching through an array for a singular value.

Something like this would be great... SELECT * FROM c WHERE ARRAY_CONTAINS(c.IDs, requiredIds)

I understand I can separate out the values of my requiredIds array and use an OR condition or something like that, but I'm hoping for something a bit more efficient.

Thanks in advance.

Solution

You could use the following queries:

SELECT *
FROM c
WHERE ARRAY_LENGTH(setIntersect(c.IDs, ['123456789', '4828927593'])) > 0

This one finds id's that appear in both arrays and then checks if the length of the resulting array is larger than 0; In other words, at least one id appears in both arrays.

SELECT VALUE c
FROM c
JOIN i IN c.IDs
WHERE ARRAY_CONTAINS(['123456789', '4828927593'], i)

This does a self join and checks if any i appears in your array. It's more readable, but if you have more than 1 match per document it also returns the result several times. Could be preferably if you also want to know what id is matched. Might also yield better performance (unsure if query 1 leverages indices).