CODEWITHSUNDEEP
javascriptnode.jsdatabaseazureazure-cosmosdb
Dr. Piyush Dholariya
Dr. Piyush Dholariya

Reputation: 1336

How to write query in documentdb to match exact arrays?

Suppose in documentdb collection I have 1 document:

i.e. { "school": "1254", "types": [ "manager","customer", "teacher","principal" ] }

Now I want to write 1 query with types, suppose I have types with same values of existing document then how can I match both array in query ?

Upvotes: 0

Views: 93

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9523

I recommend that you construct (in code) a query with a bunch of ANDs like this, if you want a subset match:

SELECT * FROM c WHERE <myArray[0]> IN c.types AND <myArray[1]> IN c.types...

Or like this if you want a truly exact match including order:

SELECT * FROM c WHERE c.types[0] = <myArray[0]> AND c.types[1] = <myArray[1]>...

Or like this if you want exact but not with order:

SELECT * FROM c WHERE c.types[0] IN <myArray> AND c.types[1] IN <myArray>...

My guess is that they would all perform about the same but if you found that the two with IN clauses did not perform as well as the one using =, then you might want to sort the arrays when you store them and sort the one you go to compare to.

You could also use a UDF to accomplish this but that would not allow for the use of an index and result in a full table scan. That said, if you have other highly selective criteria, then using a UDF at that end of your WHERE clause might be OK.

Upvotes: 1

Related Questions