CODEWITHSUNDEEP
jsonazureazure-cosmosdb
Kyle
Kyle

Reputation: 1503

Query for nested JSON property in azure CosmosDb

I am having some difficulty crafting a query for nested data in cosmosDB.

Say I have data stored in this structure:

{
   id:"1234",
   data:{
       people:{
           "a826bbc5-add9-42d8-ba52-f5de52973556":{
               first_name: "Kyle"
           },
           "efb119d-9f12-4d11-a7e1-38e4719a699c":{
               first_name: "Bob"
           },
           "b402faac-d1ba-4317-9ba6-673c76a8fc37":{
               first_name: "Jane"
           }
       }
   }
}

Now I want to write a query that would return all of the people with the first name of "Bob"

I need something like: 

Select * from c where c.data.people[*].first_name = "Bob";

Notice that the "people" object is an actual JSON object not a JSON array, so no array_contains, I need basically the JSON obj equivalent.

I've looked around and can't seem to find the appropriate query for this common use-case.

Anyone know how I can accomplish this query?

Upvotes: 5

Views: 8916

Answers (2)

Kyle
Kyle

Reputation: 1503

Marked Jay's answer as the accepted answer as I ended up using udfs - I'll post the function I ended up using and the query for anyone looking for something a little more generic.

function userDefinedFunction(properties, fieldName, filedValue){
    for(var k in properties){  
        if(properties[k][fieldName] && properties[k][fieldName] == filedValue)
            return true;  
    }
    return false;
}

with a query of:

select * from c where udf.hasValue(c.data.people,"first_name","Bob")

Upvotes: 1

Jay Gong
Jay Gong

Reputation: 23792

Since the key of people objects is random,i'm afraid you can't query it with normal sql.I tried to implement your needs with UDF in cosmos db.

Udf code:

function userDefinedFunction(peopleObj){
    var returnArray = [];
    for(var key in peopleObj){
        if (peopleObj[key].first_name == "Bob"){
            var map = {};
            map[key] = peopleObj[key];
            returnArray.push(map);
        }
    }
    return returnArray;
}

Sql:

SELECT udf.test(c.data.people) as BobPeople FROM c

Sample data:

enter image description here

Output:

enter image description here

Upvotes: 2

Related Questions