具有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屋!