Cosmos DB查询键值对

Question

我有一个json文档的大集合，其结构形式为:

{
"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}
]
}

集合中可能有很多属性，它们都由name的值标识。属性中的字段是非常一致的——可能会有一些变化，但它们都会有我关心的字段。有一个Id，一些标签，等等

我想用projd把这些数据和PowerBI中的其他数据结合起来，创建一些非常有价值的报告。

我想我要做的是将这些数据"规范化"到一个表中，如:

tbody> <<tr>等

projectName	status	openDate	closeDate	manager
p001	contoso	打开	20200101		我

您可以使用JOINS和WHERE表达式实现它，尽管该方案不适合查询，您应该考虑更改它。

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

编辑;新的查询，解决了过滤属性必须只出现一次的潜在问题。

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