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