How to get objects which has same value as an array in N1QL

user_5880486 0
Viewed 26

I have a couchbase query which gets runCount of every team.

SELECT  COUNT(*) as `runCount`,team
FROM test_runs  AS run
WHERE meta().id LIKE 'run:%' AND team IS NOT NULL 
GROUP BY team
ORDER BY COUNT(team) DESC

This query returns this:

[
  {
    "runCount": 247,
    "team": "team-red"
  },
  {
    "runCount": 148,
    "team": "team-blue"
  },
  {
    "runCount": 1,
    "team": "team-purple"
  },
  {
    "runCount": 1,
    "team": "team-yellow"
  },
  {
    "runCount": 1,
    "team": "team-brown"
  }
]

I want to get results as below, is it possible? Or is it better to do it in the golang backend application after get the result as above and manipulate.

[
  {
    "runCount": 247,
    "team": ["team-red"]
  },
  {
    "runCount": 148,
    "team": ["team-blue"]
  },

  {
    "runCount": 1,
    "team": [team-purple, team-yellow, team-brown]
  },

]
n1ql sql
1 Answers

Use another level aggregate

CREATE INDEX ix1 ON test_runs(team) WHERE META().id LIKE "run:%";

SELECT t.runCount, ARRAY_AGG(t.team) AS team
FROM (SELECT  COUNT(1) as `runCount`,team
      FROM test_runs  AS run
      WHERE meta().id LIKE 'run:%' AND team IS NOT NULL 
      GROUP BY team) AS t
GROUP BY t.runCount
ORDER  BY t.runCount DESC;
user_60805360
Related