Query CosmosDb - where the array contains elements from the array

Question

I don’t know if there is a word for this, I think it is, but right now I couldn’t explain it better than “where the array contains elements from the array”.

It may sound weird, but actually it is not (I think), and it is difficult for me to understand how I can do this in Azure CosmosDB.

Here it is. 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 request documents like these and find everything where ZipCodes.Code is in the list of zip codes, e.g. ("6500", "2700").

I'm a mystery here ...

I found the ARRAY_CONTAINS method , and it works if I enter with only one zip code - my problem is that I came with a list.

, - , .

Answer 1

, expr ARRAY_CONTAINS (arr_expr, expr [, bool_expr]) .

, UDF .

3 .

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

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

zip- ( c.zip c), UDF zip[i].

, .

---

:

IN API- SQL Cosmos DB , .

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

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

Answer 2

, UDF , UDF , . , , UDF , , , , UDF . . , , , , , - ARRAY_CONTAINS (c, 1) ARRAY_CONTAINS (c, 2) ....

, , .

Answer 3

You can do something like this: for each element in ZipCodes you get a zip and compare the array of codes you are checking.

{
  query: 'SELECT DISTINCT value r FROM root r JOIN zip IN r.zipCodes WHERE ARRAY_CONTAINS(@zipIds, zip, true)',
  parameters: [{name: "@zipIds", value: zipIds}]
}