Returning flat hierarchy of Azure Cosmos items using Cosmos Client

Question

In an Azure Cosmos container are saved examination items and each examination may have nested examinations. (see JSON example below).

How can I query all examinations for a given patient using Cosmos Client, returning all examinations in a flat hierarchy?

For the given example, the returned objects should be like:

Exam#1
Exam#2
Exam#3

For clarification: The equivalent in a relational database has an examination table where I will query patient examinations using 'SELECT * from examinations WHERE (patient = Patient#1)'

{
    "patient": {
        "$id": "1",
        "Name": "Patient#1",
        "id": "Exam#1"
    },
    "examinations": [
        {
            "patient": {
                "$ref": "1"
            },
            "examinations": [],
            "id": "Exam#2"
        },
        {
            "patient": {
                "$ref": "1"
            },
            "id": "Exam#3"
        }
    ],
}

Steve Zhao Steve Zhao · Accepted Answer · 2020-05-25T02:37:09

In your given example,nested examinations is empty. So you can try this sql:

SELECT ARRAY_CONCAT(Array(SELECT value c.patient.id from c where c.patient.Name = "Patient#1"),Array(select value e.id from c join e in c.examinations where c.patient.Name = "Patient#1")) AS result FROM c

Here is the result:

[
    {
        "result": [
            "Exam#1",
            "Exam#2",
            "Exam#3"
        ]
    }
]

If nested examinations is empty(I add a simply data into nested examinations which id is Exam#4),you can try this sql:

select ARRAY_CONCAT(Array(select value c.patient.id from c where c.patient.Name = "Patient#1"),Array(select value e.id from c join e in c.examinations where c.patient.Name = "Patient#1"),Array(SELECT value a.id from c join e in c.examinations join a in e.examinations where c.patient.Name = "Patient#1")) AS result from c

Here is the result:

[
    {
        "result": [
            "Exam#1",
            "Exam#2",
            "Exam#3",
            "Exam#4"
        ]
    }
]

By the way,I tried several sql and I want to get result like this:

[
    "Exam#1",
    "Exam#2",
    "Exam#3",
    "Exam#4"
]

But I failed,it seems impossible.

Hope these can help you.

Returning flat hierarchy of Azure Cosmos items using Cosmos Client

1 Answers