javascript 查询 CosmosDb - 其中数组包含数组中的项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47500720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query CosmosDb - where array contains item(s) from array
提问by Mads Laumann
I don't know if there is a word for this, guess there is, but right now I couldn't explain it better than "where array contains item(s) from array".
我不知道是否有这样的词，我猜有，但现在我不能比“数组包含数组中的项目”更好地解释它。
It might sound weird, but actually it not (I think), and I'm having a hard time figuring out how I can do this in Azure CosmosDB.
这听起来可能很奇怪，但实际上并非如此（我认为），而且我很难弄清楚如何在 Azure CosmosDB 中做到这一点。
Here goes. I have a document like this (simplified):
开始。我有一个这样的文件（简化）：
{
"id": "2a62fcf4-988f-4ebe-aedc-fb0c664b85d8",
"Title": "Seks ?rs f?ngsel for overgreb",
"ZipCodes": [
{
"Code": "6500",
"Name": "Vojens",
"FoundViaTerm": "Vojens"
},
{
"Code": "6400",
"Name": "S?nderborg",
"FoundViaTerm": "S?nderborg"
},
{
"Code": "6700",
"Name": "Esbjerg",
"FoundViaTerm": "Esbjerg"
}
],
"_rid": "k1sVAPf7SQAMAAAAAAAAAA==",
"_self": "dbs/k1sVAA==/colls/k1sVAPf7SQA=/docs/k1sVAPf7SQAMAAAAAAAAAA==/",
"_etag": "\"00001000-0000-0000-0000-5a14898e0000\"",
"_attachments": "attachments/",
"_ts": 1511295374
}
}
Ok, now I want to query documents like this and find all, where ZipCodes.Code is in a list of zipcodes, ex. ('6500', '2700').
好的，现在我想查询这样的文档并找到所有，其中 ZipCodes.Code 在邮政编码列表中，例如。（'6500'，'2700'）。
I'm puzzle here...
我在这里困惑...
I found the ARRAY_CONTAINSmethod and it works, if I only come in with one zipcode - my problem is I come with a list.
我找到了ARRAY_CONTAINS方法并且它有效，如果我只输入一个邮政编码 - 我的问题是我带来了一个列表。
Hope somebody can help, thanks in advance.
希望有人可以提供帮助，提前致谢。
采纳答案by Jay Gong
Per my experience , exprin ARRAY_CONTAINS (arr_expr, expr [, bool_expr])method is not supported list arguments.
根据我的经验，exprinARRAY_CONTAINS (arr_expr, expr [, bool_expr])方法不支持列表参数。
According to your situation , I suggest you use UDFin Cosmos DB.
根据你的情况，我建议你在 Cosmos DB 中使用UDF。
I created 3 sample documents as your description.
我创建了 3 个示例文档作为您的描述。
[
{
"id": "1",
"zip": [
{
"code": "1111"
},
{
"code": "2222"
}
]
},
{
"id": "2",
"zip": [
{
"code": "2222"
},
{
"code": "3333"
}
]
},
{
"id": "3",
"zip": [
{
"code": "4444"
},
{
"code": "1111"
},
{
"code": "2222"
}
]
}
]
Please refer to the snippet of UDF code as below :
请参考下面的UDF代码片段：
function test(zipcode){
var arrayList = ["1111","2222"]
var ret = false ;
for(var i=0 ;i <zipcode.length;i++){
if(arrayList.indexOf(zipcode[i].code)){
ret= true;
}else{
ret = false;
break;
}
}
return ret;
}
You could select zip array (select c.zip from c) ,then loop the results and invoke the UDF above in your code with the zip[i]arguments.
您可以选择 zip 数组（从 c 中选择 c.zip），然后循环结果并在您的代码中使用zip[i]参数调用上面的 UDF 。
Hope it helps you.
希望对你有帮助。
Just for summary:
仅作总结：
Use the IN operator from Cosmos DB SQL APIsto query entry which is included in the list condition.
使用Cosmos DB SQL API 中的 IN 运算符来查询包含在列表条件中的条目。
Like
喜欢
SELECT * FROM c WHERE c.ZipCodes[0].Code IN ("6500", "6700")
Or
或者
SELECT * FROM c JOIN zc IN c.ZipCodes WHERE zc.Code IN ("2720", "2610")
回答by Hugo Barona
Apart from the fact that using UDF looks as the easier option, i would not use UDFs in your query's filter, since it compromises the performance of your query. I faced the same problem in my work environment, where things are designed to use UDFs to help in the queries, but the reality is that most of the times we are doing queries by using single values, and using UDF will actually result on the query not using the index. So in that case if you need to validate multiple values in the array, depending on the volume of values you need to validate, you can always write something like ARRAY_CONTAINS(c, 1) or ARRAY_CONTAINS(c, 2) or ....
除了使用 UDF 看起来更简单这一事实之外，我不会在查询的过滤器中使用 UDF，因为它会影响查询的性能。我在我的工作环境中遇到了同样的问题，其中事物旨在使用 UDF 来帮助查询，但现实是大多数时候我们使用单个值进行查询，而使用 UDF 实际上会导致查询不使用索引。因此，在这种情况下，如果您需要验证数组中的多个值，根据需要验证的值的数量，您始终可以编写类似 ARRAY_CONTAINS(c, 1) 或 ARRAY_CONTAINS(c, 2) 或 ....
It doesn't look so elegant solution, but will ensure that it will use the index and will do the best performance in your query.
它看起来不是那么优雅的解决方案，但会确保它将使用索引并在您的查询中实现最佳性能。
回答by Sandokan El Cojo
You can do something like this: For each item in ZipCodes, you get a zip and compare with the array of codes you are checking. This, IMHO, is much better than using UDF.
您可以执行以下操作：对于 ZipCodes 中的每个项目，您将获得一个 zip 并与您正在检查的代码数组进行比较。恕我直言，这比使用 UDF 好得多。
{
query: '
SELECT DISTINCT value r
FROM root r
JOIN zip IN r.zipCodes
WHERE ARRAY_CONTAINS(@zipIds, zip, true)
',
parameters: [{name: "@zipIds", value: zipIds}]
}
The last param of ARRAY_CONTAINS tells the function to accept partial matches.
ARRAY_CONTAINS 的最后一个参数告诉函数接受部分匹配。
