Documentdb SQL with Array_Contains

DocumentDB SQL with ARRAY_CONTAINS

EDIT: ARRAY_CONTAINS now supports partial match as Jim Scott points out below, which I think is a better answer than this accepted one.

You servings array only has one entry {"amount": 1, "description": "bar", "weightInGrams": 21}.

This should work for your example with a single serving:

SELECT root
FROM root 
WHERE root.servings[0].description = "bar"

But it sounds like that's not what you are looking for. So, assuming you have this:

{
  ...
  "servings": [
    {"description": "baz", ....},
    {"description": "bar", ....},
    {"description": "bejeweled", ....}
  ],
  ...
}

And you want to find the documents where one of the servings has the description "bar", then you could use this UDF:

function(servings, description) {
    var s, _i, _len;
    for (_i = 0, _len = servings.length; _i < _len; _i++) {
        s = servings[_i];
        if (s.description === description) {
            return true;
        }
    }
    return false;
}

With this query:

SELECT * FROM c WHERE udf.findServingsByDescription(c.servings, "bar")

DocumentDB SQL where Array of Objects matches an array of strings

If i don't misunderstanding your requirement,you need to search the results where any name property of scopes array is included by the ["apples", "strawberries", "bananas"].

No need to use udf, please see the sample documents i made as below:

Using sql:

SELECT distinct c.scopes FROM c
join fruit in c.scopes
where Array_contains(["apples", "strawberries", "bananas"],fruit.name,false) 

Result:

Azure DocumentDB ARRAY_CONTAINS on nested documents

The first argument to ARRAY_CONTAINS must be an array. For example, in this case food.tags is valid as an argument, but food.tags.name is not.

Both the following DocumentDB queries are valid and might be what you're looking for:

SELECT food
FROM food
JOIN tag IN food.tags
WHERE tag.name = "blueberries"

Or

SELECT food
FROM food
WHERE ARRAY_CONTAINS(food.tags, { name: "blueberries" })

DocumentDB SQL check multiple values in array

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 queries with arrays

You can combine the JOIN operator , which is used to form cross products with nested array elements, with the IN operator.

SELECT docs
FROM docs
JOIN tags IN docs.tags
WHERE tags IN ("B", "C")

Note that because you are creating a cross product, that you will get a result for each matching child element, rather than for each document.

Alternatively you could combine several ARRAY_CONTAINS with OR operators, or write a UDF.

---

Related Topics