Cosmos DB query on key-value pairs

Question

I have a large collection of json documents whose structure is in the form:

{
    "id": "00000000-0000-0000-0000-000000001122",
    "typeId": 0,
    "projectId": "p001",
    "properties": [
        {
            "id": "a6fdd321-562c-4a40-97c7-4a34c097033d",
            "name": "projectName",
            "value": "contoso",
        },
        {
            "id": "d3b5d3b6-66de-47b5-894b-cdecfc8afc40",
            "name": "status",
            "value": "open",
        },
        .....{etc}
    ]
}

There may be a lot of properties in the collection, all identified by the value of name. The fields in properties are pretty consistent -- there may be some variability, but they will all have the fields that I care about. There's an Id, some labels, etc

I'm wanting to combine these with some other data in PowerBI using the projectId to create some very valuable reports.

I think what I want to do it 'normalize' this data into a table, like:

ProjectId	projectName	status	openDate	closeDate	manager
p001	contoso	open	20200101		me
etc

Where I'm at...

I can go:

SELECT c["value"] AS ProjectName 
FROM c in t.Properties
WHERE c["name"] = "projectName"

... this will give me each projectName

I can do that a heap of times to get the 'values' (status, openDate, manager, etc)

If I want to combine them together then I would need to combine all those sub-queries together with 'id'. But 'id' in not in the scope of the SELECT, so how do I get it?? If I were to do this, it sounds like something that would be very expensive (RU's) to execute.

I think I'm overcomplicating this, but I cant quite get my head around the Cosmos syntax.

Help??

user_9034195 · Answer

You can achieve it with JOINS and the WHERE expressions although the scheme is not ideal for querying and you should consider changing it.

SELECT 
    c['projectId'], --c.projectId also works, but value is a reserved keyword
    n['value'] AS projectName,
    s['value'] AS status
FROM c
JOIN n IN c.properties
JOIN s IN c.properties
WHERE n['name'] = 'projectName' AND s['name'] = 'status'
--note all filtered properties must appear exactly once for it to work properly

Edit; new query that solves the potential issue that filtered properties must appear exactly once.

SELECT 
    c['projectId'],
    ARRAY(
        SELECT VALUE n['value']
        FROM n IN c.properties
        WHERE  n['name'] = 'projectName'
    )[0] AS projectName,
    ARRAY(
        SELECT VALUE n['value']
        FROM n IN c.properties
        WHERE  n['name'] = 'status'
    )[0] AS status
FROM c

Cosmos DB query on key-value pairs

1 Answers