DocumentDB: SQL to select match on optional element

Question

Say I have these 3 documents in a DocumentDB collection:

[
    {
        "name": "tiger",
        "keywords": [
            "animal",
            "cat",
            "stripes"
        ]
    },
    {
        "name": "cat"
    },
    {
        "keywords": [
            "panther",
            "black"
        ]
    }
]

How would I structure a single SQL query to return all documents where the 'name' matches "cat" OR a 'keyword' matches "cat" when 'keywords' and 'name' are optional elements in the document?

Samer Boshra · Accepted Answer · 2016-09-16 20:18:50Z

1

Try this query:

SELECT * FROM root r WHERE r.name = 'cat' OR ARRAY_CONTAINS(r.keywords, 'cat')

answered Sep 16, 2016 at 20:18

Samer Boshra

9396 silver badges6 bronze badges

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

2 Comments

Projectitis Over a year ago

Works great. Do you know how to modify the query so that ARRAY_CONTAINS matches partial strings in the same way as CONTAINS? At the moment ARRAY_CONTAINS must match the string exactly. For example the string "ca": SELECT * FROM root r WHERE CONTAINS(r.name,'ca') OR ARRAY_CONTAINS(r.keywords, 'ca')

Samer Boshra Over a year ago

ARRAY_CONTAINS performs a strict equality comparison of the search item against each item in the array. Mind you that the search item could be of any type. An alternative approach here is to do SELECT VALUE r FROM root r JOIN k IN r.keywords WHERE CONTAINS(r.name, 'ca') OR CONTAINS(k, 'ca') Please keep in mind that JOIN will flatten the keywords array and the result set is the cross product of the input documents with each item in the array. For instance, this query will return the second document twice since r.name satisfies the condition for both array items.

Collectives™ on Stack Overflow

DocumentDB: SQL to select match on optional element

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related