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

user_15471131 0
Viewed 63

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?

n1ql couchbase sql
1 Answers

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") ;
user_60805360
Related