Sort elements in embedded array NoSQL


Author: Malice

Originally Sourced from: https://stackoverflow.com/questions/68489550/sort-elements-in-embedded-array-nosql

Currently, I have a NoSQL database structure within Azure Cosmos DB (with sql api) as such:

{
    "id": "0",
    "name": "testApp",
    "versions": [
        {
            "id": "0",
            "name": "testVersion",
            "app_id": "0",
        },
        {
            "id": "1",
            "name": "testVersion1",
            "app_id": "0",
        },
        {
            "id": "2",
            "name": "testVersion2",
            "app_id": "0",
        }
    ]
}

I have multiple apps with this structure, so to get all of the versions for a selected app I use the SQL command:

SELECT a.versions FROM Items a WHERE a.id = '0'

As expected, this does return the elements as such:

[
    {
        "versions": [
            {
                "id": "0",
                "name": "testVersion",
                "app_id": "0",
            },
            {
                "id": "1",
                "name": "testVersion1",
                "app_id": "0",
            },
            {
                "id": "2",
                "name": "testVersion2",
                "app_id": "0",
            }
        ]
    }
]

My question is, what would the SQL command be to sort these versions within an app by their own id (the version id, not the app id)? The id's will eventually be scattered throughout and unorganized so I cannot rely on them being in order all the time. I tried using a ORDER BY command but I could not figure out how to access the inner id's of the returned versions array.