Getting a specific item in a sub array and selecting one value from it

Question

I want to get the boardgame rank (value) from this nested array in Cosmos DB.

{
    "name": "Alpha",
    "statistics": {
        "numberOfUserRatingVotes": 4155,
        "averageRating": 7.26201,
        "baysianAverageRating": 6.71377,
        "ratingStandardDeviation": 1.18993,
        "ratingMedian": 0,
        "rankings": [
            {
                "id": 1,
                "name": "boardgame",
                "friendlyName": "Board Game Rank",
                "type": "subtype",
                "value": 746
            },
            {
                "id": 4664,
                "name": "wargames",
                "friendlyName": "War Game Rank",
                "type": "family",
                "value": 140
            },
            {
                "id": 5497,
                "name": "strategygames",
                "friendlyName": "Strategy Game Rank",
                "type": "family",
                "value": 434
            }
        ],
        "numberOfComments": 1067,
        "weight": 2.3386,
        "numberOfWeightVotes": 127
    },
}

So I want:

{
    "name": "Alpha",
    "rank": 746
}

Using this query:

SELECT g.name, r
FROM Games g 
JOIN r IN g.statistics.rankings
WHERE r.name = 'boardgame'

I get this (so close!):

{
    "name": "Alpha",
    "r": {
        "id": 1,
        "name": "boardgame",
        "friendlyName": "Board Game Rank",
        "type": "subtype",
        "value": 746
    }
},

But extending the query to this:

SELECT g.name, r.value as rank
FROM Games g 
JOIN r IN g.statistics.rankings
WHERE r.name = 'boardgame'

I get this error:

Failed to query item for container Games:
 Message: {"errors":[{"severity":"Error","location":{"start":21,"end":26},"code":"SC1001","message":"Syntax error, incorrect syntax near 'value'."}]}

ActivityId: 0a0cb394-2fc3-4a67-b54c-4d02085b6878, Microsoft.Azure.Documents.Common/2.14.0

I don't understand why this doesn't work? I don't understand what the syntax error is. I tried adding square braces but that didn't help. Can some help me understand why I get this error and also how to achieve the output I'm looking for?

Answer 1

This should work,

SELECT g.name, r["value"] as rank
FROM Games g 
JOIN r IN g.statistics.rankings
WHERE r.name = 'boardgame'