具有ARRAY_CONTAINS的DocumentDB SQL [英] DocumentDB SQL with ARRAY_CONTAINS

问题描述

我正在玩 https://www.documentdb.com/sql/demo，这使我可以查询如下所示的示例文档:

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
    }
  ]
}

我对ARRAY_CONTAINS()感到困惑.该查询返回结果:

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

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

但是，此查询不会:

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

有什么作用?

如果这是C#，我将如何编写查询来说明我要实现的目标:

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

出现在root.tags上的第一个示例查询是有效的，因为{ "name": "snacks" }是root.tags数组中的 entire 对象，而在第二个查询中，{ "description": "bar" }仅是root.servings对象中的一个字段.

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.

如何修改root.servings上的第二个查询，使其仅在知道投放的description时起作用?

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

推荐答案

正如吉姆·斯科特(Jim Scott)在下面指出的那样，ARRAY_CONTAINS现在支持部分匹配，我认为这是一个比这个公认的更好的答案.

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

您的servings数组只有一个条目{"amount": 1, "description": "bar", "weightInGrams": 21}.

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", ....}
  ],
  ...
}

并且您要查找其中一份的描述为"bar"的文档，那么可以使用此UDF:

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;
}

使用此查询:

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

这篇关于具有ARRAY_CONTAINS的DocumentDB SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！