Query CosmosDb - where array contains item(s) from array

Question

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.

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').

I'm puzzle here...

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.

Answer 1

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

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

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

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

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.

Hope it helps you.

---

Just for summary:

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

Like

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

Or

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