Query CosmosDb Unstructured JSON

Question

How can CosmosDB Query the values of the properties within a dynamic JSON?

The app allows storing a JSON as a set of custom properties for an object. They are serialized and stored in CosmosDb. For example, here are two entries:

{
    "id": "ade9f2d6-fff6-4993-8473-a2af40f071f4",
    ...
    "Properties": {
        "fn": "Ernest",
        "ln": "Hemingway",
        "a_book": "The Old Man and the Sea"
    },
    ...
}

and

{
    "id": "23cb9d4c-da56-40ec-9fbe-7f5178a92a4f",
    ...
    "Properties": {
        "First Name": "Salvador",
        "Last Name": "Dali",
        "Period": "Surrealism"
    },
    ...
}

How can the query be structured so that it searches in the values of Properties?

Answer 1

I’m looking for something that doesn’t involve the name of the sub-propety, like SELECT * FROM c WHERE some_function_here(c.Properties, ‘Ernest’)

Maybe I get your idea that you want to filter the documents by the value of the Properties, not the name. If so , you could use UDF in cosmos db.

sample udf:

function query(Properties,filedValue){
    for(var k in Properties){  
        if(Properties[k] == filedValue)
            return true;  
    }
    return false;
}

sample query:

SELECT  c.id FROM c where udf.query(c.Properties,'Ernest')

output:

---

Just summary here, Ovi's udf function like:

function QueryProperties (Properties, filedValue) {     
    for (var k in Properties) { 
        if (Properties[k] && Properties[k].toString().toUpperCase().includes(filedValue.toString().toUpperCase())) 
            return true;    
    return false; 
}