Cosmos DB - SQL - Get All Unique Array Items and a count

Question

I have a cosmos db made up of items such as

{
    "id": 1
    "countries": [
    "Australia", 
    "New Zealand"
    ]
},
{
    "id": 2
    "countries": [
    "Australia", 
    "Antarctica"
    ]
}

I know I can get a distinct result of the values by doing

SELECT DISTINCT country FROM country IN t.countries

which returns data like

    {
        "country": "Australia"
    },
    {
        "country": "New Zealand"
    },
    {
        "country": "Antarctica"
    }

or

SELECT DISTINCT VALUE country FROM country IN t.countries

which returns just an array like

['Australia', 'New Zealand', 'Antarctica']

How would I create a query to get data such as

    {
        "country": "Australia",
        "count": 2
    },
    {
        "country": "New Zealand",
        "count": 1
    },
    {
        "country": "Antarctica",
        "count": 1
    }

Answer 1

You can write something like this with GROUP BY,

SELECT 
    COUNT(r) as Total,r as Country 
FROM c JOIN r IN c.countries 
GROUP BY r

and the results,

[ { "Total": 1, "Country": "Antarctica" }, { "Total": 1, "Country": "New Zealand" }, { "Total": 2, "Country": "Australia" } ]