DocumentDB SQL with ARRAY_CONTAINS

Question

I'm playing around on https://www.documentdb.com/sql/demo, which allows me to query against sample documents that look like:

{
  "id": "19015",
  "description": "Snacks, granola bars, hard, plain",
  "tags": [
    {
      "name": "snacks"
    }
  ],
  "version": 1,
  "isFromSurvey": false,
  "foodGroup": "Snacks",
  "servings": [
    {
      "amount": 1,
      "description": "bar",
      "weightInGrams": 21
    }
  ]
}

I'm confused about ARRAY_CONTAINS(). This query returns results:

SELECT root
FROM root 
WHERE ARRAY_CONTAINS(root.tags, { "name": "snacks" })

However, this query does not:

SELECT root
FROM root 
WHERE ARRAY_CONTAINS(root.servings, { "description": "bar" })

What gives?

What I'm trying to achieve is illustrated by how I would write the query if this was C#:

var filteredDocs = docs.Where(d => d.Servings != null &&
                                   d.Servings.Length > 0 &&
                                   d.Servings.Any(s => s.Description == "bar"));

It appears the first example query on root.tags works because { "name": "snacks" } is the entire object in the root.tags array, while, in the second query, { "description": "bar" } is only one field in the root.servings objects.

How can I modify the second query on root.servings to work with only knowing the serving description?

The reason `tags` matches, and `servings` doesn't is that `ARRAY_CONTAINS` appears to be an exact match. So it's looking for a serving object that only has a description. Here's an example of it working if you specify the full object: https://www.documentdb.com/sql/demo#Sandbox?SELECT%20%09food%0AFROM%20%09food%0AWHERE%20%09ARRAY_CONTAINS(food.nutrients,%20%7B%0A%20%20%20%20%22id%22:%20%22612%22,%0A%20%20%20%20%22description%22:%20%2214:0%22,%0A%20%20%20%20%22nutritionValue%22:%200.088,%0A%20%20%20%20%22units%22:%20%22g%22%0A%20%20%7D) — , Jun 27 '17 at 14:31

score 26 · Answer 1 · edited Dec 13 '19 at 22:52

Not sure if this functionality was available when you were looking at the API originally but the ARRAY_CONTAINS now supports an optional Boolean value at the end to provide partial match support.

SELECT root
FROM root
WHERE ARRAY_CONTAINS(root.servings, { "description": "bar" }, true)

Here is sample using the documentdb demo site that queries an array that contains multiple fields for each object stored.

SELECT  *
FROM    food as f
WHERE   ARRAY_CONTAINS(f.servings, {"description":"bar"}, true)

Larry Maccherone · Accepted Answer · 2019-09-10T01:00:05.963

7

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")

edited Sep 10 '19 at 01:00

answered Feb 01 '16 at 19:54

Larry Maccherone

9,393
3
27
43

What if my `servings` had ten entries and I wanted all `root`s where at least one entry has `description = "bar"`? – core Feb 01 '16 at 22:13
Can you edit the question with an example showing 2 or 3 and your expected output? – Larry Maccherone Feb 01 '16 at 23:28
I am still confused as to why ARRAY_CONTAINS works for tags but does not work for, say, the nutrients array: SELECT * FROM food WHERE ARRAY_CONTAINS(food.nutrients, { id: "287" }) This to me looks like the exact same query. Only difference is that a nutrients element has more than one value (id, description, nutritionValue, units) while tags only has "name". – Dan Sep 06 '16 at 14:22
3

It has nothing to do with whether or not it's a tag. `ARRAY_CONTAINS` expects an array of JSON base types (String, Number) rather than an Object. So, an array of tags (`["High", "Medium"]`) meets that requirement as does an array of Numbers (`[1, 4, 7]`) whereas your example does not. – Larry Maccherone Sep 06 '16 at 18:51
`ARRAY_CONTAINS` limitation is that it only works on simple value types, not objects - you need to either use a `JOIN` or **UDF** – SliverNinja - MSFT Apr 12 '17 at 16:51
1

@SliverNinja `ARRAY_CONTAINS` works on complex types too, but it's an exact match, so you have to specify the entire object. https://www.documentdb.com/sql/demo#Sandbox?SELECT%20%09food%0AFROM%20%09food%0AWHERE%20%09ARRAY_CONTAINS(food.nutrients,%20%7B%0A%20%20%20%20%22id%22:%20%22612%22,%0A%20%20%20%20%22description%22:%20%2214:0%22,%0A%20%20%20%20%22nutritionValue%22:%200.088,%0A%20%20%20%20%22units%22:%20%22g%22%0A%20%20%7D) – Jun 27 '17 at 14:29

score 5 · Answer 3 · answered Jul 26 '17 at 11:44

5

Note that this is a workaround that works fine.

SELECT c.id FROM c JOIN a in c.companies where a.id = '{id}'

In Linq this would be

x => x.companies.Any(z => z.id == id)

answered Jul 26 '17 at 11:44

Egbert Nierop

2,066
1
14
16

1

I upvoted because I think you are correct but it would improve your answer if you used the field names in the original example. – Larry Maccherone Jul 26 '17 at 11:50

score 4 · Answer 4 · answered Jun 16 '16 at 11:57

Using a UDF, as Larry Maccherone mentioned, has the disadvantage that it does not use the collection's index. A UDF should only be used in (very) small collections or in combination with criteria that use the collection's index.

A good solution for this problem is currently missing in DocumentDB. I would advice to vote on the following item: https://feedback.azure.com/forums/263030-documentdb/suggestions/14829654-support-sub-query-functions-like-exists-not-exist

DocumentDB SQL with ARRAY_CONTAINS

4 Answers4

Linked