I have the following (Azure) CosmosDB (sub) structure, that has 2 nested arrays:
{
"id": "documentTypes",
"SomeThing": "SomeThing",
"configuration": [
{
"language": "en",
"list": [
{
"id": 1,
"name": "Supporting Documents"
},
{
"id": 2,
"name": "Summary PDF"
},
]
}
],
}
I have tried the following queries, with poor results.
SELECT * FROM c WHERE c.documentTypes.configuration[0].list[0].id FROM c
and
SELECT
p.id,
p.name
FROM f
JOIN c IN f.configuration
JOIN p IN c.list
WHERE c.id == 'documentTypes'
Q
: How can I get only the list of name
and id
s?
CodePudding user response:
You need this?
SELECT ARRAY(SELECT VALUE e FROM c JOIN d IN c["configuration"] JOIN e IN d["list"]) AS Result FROM c
Output:
[ { "Result": [ { "id": 1, "name": "Supporting Documents" }, { "id": 2, "name": "Summary PDF" } ] } ]
CodePudding user response:
My own solution was similar to Sajeetharan's:
SELECT list.id, list.name FROM c
JOIN configuration IN c.configuration
JOIN list IN configuration.list
WHERE c.id = 'documentTypes'
This to me look a bit simpler by not needing [""]
and the ARRAY()
function, and also doesn't produce the additional Result
item. I have not idea if there is any performance difference.