CODEWITHSUNDEEP
sqljsonazure-cosmosdbazure-cognitive-search
Evan Bonnett
Evan Bonnett

Reputation: 13

Returning unknown JSON in a query

Here is my scenario. I have data in a Cosmos DB and I want to return c.this, c.that etc as the indexer for Azure Cognitive Search. One field I want to return is JSON of an unknown structure. The one thing I do know about it is that it is flat. However it is my understanding that the return value for an indexer needs to be known. How, using SQL in a SELECT, would I return all JSON elements in the flat object? Here is an example value I would be querying: 

{
"BusinessKey": "SomeKey",
    "Source": "flat",
    "id": "SomeId",
    "attributes": {
        "Source": "flat",
        "Element": "element",
        "SomeOtherElement": "someOtherElement"
    }
}

So I would want my select to be maybe something like:

SELECT 
    c.BusinessKey,
    c.Source,
    c.id,
    -- SOMETHING HERE TO LIST OUT ALL ATTRIBUTES IN THE JSON AS FIELDS IN THE RESULT

And I would want the result to be:

{
    "BusinessKey": "SomeKey",
    "Source": "flat",
    "id": "SomeId",
    "attributes": [{"Source":"flat"},{"Element":"element"},{"SomeOtherElement":"someotherelement"}]    
}

Currently we are calling ToString on the c.attributes, which is the JSON of unknown structure but it is adding all the escape characters. When we want to search the index, we have to add all those escape characters and it's getting really unruly.

Is there a way to do this using SQL?

Thanks for any help!

Upvotes: 1

Views: 158

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

You could use UDF in cosmos db sql.

UDF code:

function userDefinedFunction(object){
    var returnArray = [];
    for (var key in object) {  
        var map = {};
        map[key] = object[key];
        returnArray.push(map);
    }
    return returnArray;
}

Sql:

SELECT 
    c.BusinessKey,
    c.Source,
    c.id,
    udf.test(c.attributes) as attributes
    from c

Output:

enter image description here

Upvotes: 1

Related Questions