DocumentDB SQL check multiple values in array

Question

I am using Azure's DocumentDB as my database. I have potentially thousands of documents and want to return all the documents that contain certain tags.

For example:

Document 1

{
  "id": "328732,
  "name": "jeff",
  "tags": [
    "A",
    "B",
    "C"
  ]
}

Document 2

{
  "id": "54092,
  "name": "ayla",
  "tags": [
    "B",
    "D",
    "F"
  ]
}

Document 3

{
  "id": "98234,
  "name": "lara",
  "tags": [
    "B",
    "G",
    "H"
  ]
}

If I have a list of tags ["A", "F", "X"], the first two documents (jeff, and ayla) should be returned. I can achieve this by using the built in function ARRAY_CONTAINS with several OR operators:

SELECT * 
FROM c 
WHERE ARRAY_CONTAINS(c.groups, "A") OR
WHERE ARRAY_CONTAINS(c.groups, "F") OR
WHERE ARRAY_CONTAINS(c.groups, "X")

I'm wondering if there is a better way to achieve this. If I had hundreds of tags I would need hundreds of conditions.

Larry Maccherone Larry Maccherone · Accepted Answer · 2017-09-12T21:56:42

I know of no other way to achieve what you want. You could write code that will automatically expand to a series of ARRAY_CONTAINS with ORs.

DocumentDB SQL check multiple values in array

1 Answers