N1QL query count for each document of specific type

Question

I am new to couchbase and to non-relational DB.

I have a bucket with players and teams(2 types of documents). each player has type, playedFor(an array with all the teams he played) and a name for example:

{
    "type":"player"
    "name":"player1"
    "playedFor": [
          "England/Manchester/United"
          "England/Manchester/City"
          ]
}

each team has type, name and category for example:

{
    "type": "team"
    "name": "England/Manchester/City"
    "category": "FC"
}

I want to know how many players played for each team of category FC. I made this query to calc for specific team:

SELECT COUNT(1) AS total 
FROM bucket AS a 
WHERE a.type='player' 
AND (any r in a.playedFor satisfies r in ["England/Manchester/United"] end)

but how can i make this query for all teams?

Answer 1

The wrinkle in the way you've modeled this data is that player can play for 1 or more teams (hence the array).

One way to approach this is to use Couchbase's UNNEST clause to "flatten" these arrays (it's basically joining the document to each of the items in the array).

At that point, it becomes as easy as a standard GROUP BY. Here's an example:

SELECT team, count(1) AS totalPlayers
FROM `bucket` AS a 
UNNEST a.playedFor team
WHERE a.type='player'
GROUP BY team

This query would generate output like:

[
  {
    "team": "Pittsburgh/Pirates",
    "totalPlayers": 8
  },
  {
    "team": "England/Manchester/United",
    "totalPlayers": 10
  },
  {
    "team": "England/Manchester/City",
    "totalPlayers": 15
  },
  {
    "team": "Cincinnati/Reds",
    "totalPlayers": 21
  }
]

(Sorry, I used MLB teams to augment your sample, since I don't know much about soccer teams).

Notice that the separate team documents don't figure into this query, but you could also JOIN to them if you need information from them for your quer(ies).