Azure Cosmos DB query groups and sum totals

Question

I have a Cosmos DB that contains "teams" with a field called playerIds (which is an array of id strings]

I need to be able to pass an array of player id and return the following:

[
{playerId: "qwerty", nTeams: 3},
{playerId: "6yhjk", nTeams: 0},
{playerId: "uytgyu", nTeams: 22}
]

I've been able to return ALL the teams the playerIds array are in I've also been able to count ALL the teams ALL users in the database are in.

Any advice?

Example document:

[
    {
        teamName: "Wonder Mutt",
        id: "3456789",
        playerIds: [
          "uygduygf",
          "37nfyefyewf",
          "u298y2hf3f"]
    }
]

I have this, but it returns ALL the players in the database NOT JUST the ones in the array of ids

SELECT COUNT(r) as nTeams, r as Players FROM c JOIN r IN c.PlayerIds 
WHERE EXISTS
(SELECT VALUE z FROM z in c.PlayerIds WHERE ARRAY_CONTAINS(
[
    "cdb5394b-401a-a752-50196bc99f23",
    "22b95c43-4466-82e5-24a2ae12faca",
    "1e62ed86-5a29bbae-0a791e578578",
    "df6679dd4f86-84e9-d44611c72451",
    "6074fa26-4842-8768-eaa23b89bdbd"
], z))
GROUP BY r

Answer 1

If you don't mind it skipping players that have no teams the following should work:

SELECT p AS playerId, COUNT(1) AS nTeams
FROM c
JOIN p IN c.playerIds
WHERE ARRAY_CONTAINS([ 'qwerty', '6yhjk', 'uygduygf' ], p)
GROUP BY p

This assumes that a player only appears once in a team.