查询CosmosDb-其中数组包含数组中的项目 [英] Query CosmosDb - where array contains item(s) from array

问题描述

我不知道是否有这个词，猜想有，但是现在我无法比其中数组包含数组中的项目"更好地解释它.

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

这听起来可能很奇怪，但实际上并非如此(我认为)，而且我很难确定如何在Azure CosmosDB中做到这一点.

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.

在这里.我有一个这样的文档(简体):

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

}

好吧，现在我想查询这样的文档并查找所有文件，例如ZipCodes.Code在邮政编码列表中，例如. ("6500"，"2700").

Ok, now I want to query documents like this and find all, where ZipCodes.Code is in a list of zipcodes, ex. ('6500', '2700').

我在这里很困惑...

I'm puzzle here...

我找到了 ARRAY_CONTAINS 方法，如果我只带一个邮政编码，它就会起作用-我的问题是我带了一个清单.

I found the ARRAY_CONTAINS method and it works, if I only come in with one zipcode - my problem is I come with a list.

希望有人能提供帮助，谢谢.

Hope somebody can help, thanks in advance.

推荐答案

根据我的经验，ARRAY_CONTAINS (arr_expr, expr [, bool_expr])方法中的expr不支持列表参数.

Per my experience , expr in ARRAY_CONTAINS (arr_expr, expr [, bool_expr]) method is not supported list arguments.

根据您的情况，建议您使用 Cosmos DB中的UDF .

According to your situation , I suggest you use UDF in Cosmos DB.

我创建了3个样本文档作为您的描述.

I created 3 sample documents as your description.

[
  {
    "id": "1",
    "zip": [
      {
        "code": "1111"
      },
      {
        "code": "2222"
      }
    ]
  },
  {
    "id": "2",
    "zip": [
      {
        "code": "2222"
      },
      {
        "code": "3333"
      }
    ]
  },
  {
    "id": "3",
    "zip": [
      {
        "code": "4444"
      },
      {
        "code": "1111"
      },
      {
        "code": "2222"
      }
    ]
  }
]

请参考以下UDF代码段:

Please refer to the snippet of UDF code as below :

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

您可以选择zip数组(从c中选择c.zip)，然后循环结果并使用zip[i]参数在代码中调用上面的UDF.

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.

希望它对您有帮助.

仅供参考:

使用 Cosmos DB SQL API 来查询列表条件中包含的条目.

Use the IN operator from Cosmos DB SQL APIs to query entry which is included in the list condition.

喜欢

SELECT * FROM c WHERE c.ZipCodes[0].Code IN ("6500", "6700")

或

SELECT DISTINCT c FROM c JOIN zc IN c.ZipCodes WHERE zc.Code IN ("2720", "2610")

这篇关于查询CosmosDb-其中数组包含数组中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！