N1QL/Couchbase - Show all the events produced each day, where don't know how many different days maybe in the table

Question

Suppose I have a bucket with documents that looks like this:

[
{"topic":..., "event":..., "message":...,"date":......}
.
.
.
]

How can I write a query that groups all messages of a specific event type for each day, without hard-coding the date and suppose we do not know how many different dates are there in the bucket?

I'm thinking of getting the distinct dates first. (For example, SELECT DISTINCT(date) FROM bucket). Is there a way to loop through these days and put all events with the same date into an array, and union all arrays together as a nested array?

Answer 1

Use Aggregate query.

SELECT b.date, ARRAY_AGG(b) AS docs
FROM mybucket AS b
WHERE b.event = "myevent"
GROUP BY b.date ;

If date is ISO-8601 format use https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html date functions group by desired format.

SELECT DATE_FORMAT_STR(b.date,"1111-11-11") AS day, ARRAY_AGG(b) AS docs
FROM mybucket AS b
WHERE b.event = "myevent"
GROUP BY DATE_FORMAT_STR(b.date,"1111-11-11") ;