Hi there,
I need help setting up a query that includes a count of an object column within a database table.
The set-up:
I have a database called 'notifications'
Each notification has a column called 'reactions'
This column is an array of an object. Each reaction being for example an object containing userID, date, …
so in general, the notifications table looks like this:
{
id: integer
date: timestamp
expiration_date: timestamp
notifications_types_id: integer
organizationID: integer
users_reaction: [{
userID: integer
date: timestamp
reaction: enum
name: text
img: text
}]
}
The required output:
I want to set up a Query, that shows a sum of all reactions of all notifications combined, per organizationID.
Each organization can have multiple notifications, and each notification can have multiple reactions.
Example Result: organizationID=1, reactions=100
(which could be the case if there are 2 notifications for this organization with ID 1, each notification having 50 reactions)
The problem:
I tried pretty much anything I could think of with the Query All functionality (an addon which didnt produce any result, using the aggregate Output instead of list, I even tried an eval for reactions using the length filter on that column, ….), but couldn't come up with a solution for this particular case.
Any ideas, anyone?