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 · Accepted Answer · 2017-09-12 21:56:42Z

4

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.

answered Sep 12, 2017 at 21:56

Larry Maccherone

9,5433 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DocumentDB SQL check multiple values in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related