Query for nested JSON property in azure CosmosDb
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 cSample data:
Output:
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")