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?

Answer 1

Try this query:

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