My DB instance contains some documents with the following schema:

{
    id: <someGuid>
    myJsonArray: [
        {
            subId: SmallUniqueId1,
            val: <someValue>
        },
        {
            subId: SmallUniqueId2,
            val: <someOtherValue> 
        }
    ]
}

I have a Web API that generates new SubIds (you can think of them as randomly generated base64 strings that have a max length of 5 chars).

I'd like to query my DB to verify that the newly minted SubId doesn't already exist somewhere in all of my docs.

I figured the solution probably involves plugging in the new subId value into a WHERE clause (I'm hitting CosmosDB via the REST API). Here's what I don't know:

• I don't know how to query over every element of each document's JsonArray (if it was just querying on a single string field, it would be easy)
• I'm also not sure about the efficiency of this query and whether this approach for validating new Ids against what's already in the DB is, in general, a bad pattern. I have a one partition CosmosDB instance - the subId validation check should not occur very often.

My DB instance contains some documents with the following schema:

{
    id: <someGuid>
    myJsonArray: [
        {
            subId: SmallUniqueId1,
            val: <someValue>
        },
        {
            subId: SmallUniqueId2,
            val: <someOtherValue> 
        }
    ]
}

I have a Web API that generates new SubIds (you can think of them as randomly generated base64 strings that have a max length of 5 chars).

I'd like to query my DB to verify that the newly minted SubId doesn't already exist somewhere in all of my docs.

I figured the solution probably involves plugging in the new subId value into a WHERE clause (I'm hitting CosmosDB via the REST API). Here's what I don't know:

• I don't know how to query over every element of each document's JsonArray (if it was just querying on a single string field, it would be easy)
• I'm also not sure about the efficiency of this query and whether this approach for validating new Ids against what's already in the DB is, in general, a bad pattern. I have a one partition CosmosDB instance - the subId validation check should not occur very often.

• javascript
• azure
• azure-cosmosdb

• Hi, any updates now ? – Jay Gong Commented Oct 12, 2017 at 2:58
• Give me a few more days to get back to you - had a few fires to put out. I'm intrigued by the UDF suggestion at the bottom! (Thank you!) I have a scenario where I need to migrate these docs from one cosmosdb instance to another, and there's no guarantee that these subIds (which are valid in db1) are ALSO valid in db2. I need to think that through a bit before following up. – nciao Commented Oct 12, 2017 at 14:29
• Sure! Waiting for your reply. – Jay Gong Commented Oct 13, 2017 at 1:24

1 Answer 1

I don't know how to query over every element of each document's JsonArray (if it was just querying on a single string field, it would be easy)

I created two sample documents in my azure cosmosdb as you described.

[
  {
    "id": "id1",
    "myJsonArray": [
      {
        "subId": "sub1",
        "val": "value1"
      },
      {
        "subId": "sub2",
        "val": "value2"
      }
    ],
  },
  {
    "id": "id2",
    "myJsonArray": [
      {
        "subId": "sub3",
        "val": "value3"
      },
      {
        "subId": "sub4",
        "val": "value4"
      }
    ],
  }
]

You could use the SQL below to query field in array.

SELECT  a.subId as subId FROM c
join a in  c.myJsonArray

Result:

Add where clause .

SELECT  a.subId as subId FROM c
join a in  c.myJsonArray
where a.subId='sub1'

Result:

I'm also not sure about the efficiency of this query and whether this approach for validating new Ids against what's already in the DB is, in general, a bad pattern. I have a one partition CosmosDB instance - the subId validation check should not occur very often.

As you needs,I thought you could use User Defined Function in Azure Cosmos DB.

Invoke UDF when you create document into your DB, check if the field value already exists, return the boolean result.

Please refer to this official doc.

Hope it helps you. Any concern,please feel free to let me know.