CODEWITHSUNDEEP
sqlazure-cosmosdb
core
core

Reputation: 33059

DocumentDB SQL with ARRAY_CONTAINS

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?

Upvotes: 13

Views: 20781

Answers (4)

Jim Scott
Jim Scott

Reputation: 2553

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)

Upvotes: 28

Larry Maccherone
Larry Maccherone

Reputation: 9523

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

Upvotes: 7

user2410689
user2410689

Reputation:

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)

Upvotes: 6

TJ Galama
TJ Galama

Reputation: 477

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

Upvotes: 4

Related Questions