matching array content in azure documentdb

Question

is there a way of producing following query?

{ 
  "user": { 
    "name": "john", 
    "tags": ["one", "two", "three"] 
  } 
}

now given an array of tags

["one","two","five"]

I want to select all users (distinct if possible) who have at least one tag in common with incoming tag array.

score 2 · Accepted Answer · edited May 23 '17 at 12:00

2

You have to write a user defined function to do this. For example, I searched for JavaScript array intersection in StackOverflow and found this: Simplest code for array intersection in javascript

You can register this as a UDF in your collection, and then query like:

SELECT * FROM docs WHERE udf.ARRAY_INTERSECTS(docs.tags, ["a", "b", "c"]) != []

Note however that this requires a scan..

edited May 23 '17 at 12:00

Community

answered May 28 '15 at 16:49

If you used a three-tag OR clause, would it use an index on the array? – Larry Maccherone May 28 '15 at 19:22
Yes, if you used a three-tag OR, it would use the index. – Aravind Krishna R. May 29 '15 at 15:09
could you give an example on how to write that query so it would use an index? – Larry Maccherone May 29 '15 at 15:13
2

This will use the index: SELECT * FROM docs JOIN tag in docs.tags WHERE tag = "a" OR tag = "b" OR tag = "c" – Aravind Krishna R. May 29 '15 at 15:14
1

And what about something like SELECT * FROM docs WHERE ARRAY_CONTAINS(docs.tags, "a") OR ARRAY_CONTAINS(docs.tags, "b") OR ARRAY_CONTAINS(docs.tags, "c") – Benoit Patra Feb 26 '17 at 14:14

1 Answers1