How can I make a count/aggregate on object's array

Gi Ma

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?

Pawel Magdanski

Hi,

I think that if you want to use aggragate you need to start with reaction query and add notification data via addon after grouping and aggreagating

Liz Anaya

Hi @Gi Ma! 👋

I created a video that walks through a possible solution for receiving an API response with the sum of all reactions of all notifications combined per organizationID. You can check it out here.

Also, in the video above, I mentioned a way to update the keys in a list of objects. Here is the link to the video that walks through how to do that.

Let me know if this information is helpful. I hope you have a great weekend! 😊