Sequelize how to use aggregate function on Postgres JSONB column

Question

I have created one table with JSONB column as "data"

And the sample value of that column is

[{field_id:1, value:10},{field_id:2, value:"some string"}]

Now there are multiple rows like this..

What i want ?

I want to use aggregate function on "data" column such that, i should get

Sum of all value where field_id = 1;
Avg of value where field_id = 1;

I have searched alot on google but not able to find a proper solution. sometimes it says "Field doesn't exist" and some times it says "from clause missing"

I tried referring like data.value & also data -> value lastly data ->> value But nothing is working.

Please let me know the solution if any one knows, Thanks in advance.

mike.k · Accepted Answer · 2019-09-18 02:59:30Z

0

Your attributes should be something like this, so you instruct it to run the function on a specific value:

attributes: [
    [sequelize.fn('sum', sequelize.literal("data->>'value'")), 'json_sum'],
    [sequelize.fn('avg', sequelize.literal("data->>'value'")), 'json_avg']        
]

Then in WHERE, you reference field_id in a similar way, using literal():

where: sequelize.literal("data->>'field_id' = 1")

Your example also included a string for the value of "value" which of course won't work. But if the basic Sequelize setup works on a good set of data, you can enhance the WHERE clause to test for numeric "value" data, there are good examples here: Postgres query to check a string is a number

Hopefully this gets you close. In my experience with Sequelize + Postgres, it helps to run the program in such a way that you see what queries it creates, like in a terminal where the output is streaming. On the way to a working statement, you'll either create objects which Sequelize doesn't like, or Sequelize will create bad queries which Postgres doesn't like. If the query looks close, take it into pgAdmin for further work, then try to reproduce your adjustments in Sequelize. Good luck!

answered Sep 18, 2019 at 2:59

mike.k

3,4771 gold badge14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rahul Over a year ago

SequelizeDatabaseError: function sum(text) does not exist

mike.k Over a year ago

@Rahul then cast it to int sequelize.literal("data->>'value'::int")

Rahul Over a year ago

Giving null value, infact when i run this: SELECT (data->>'field_id') AS "json_sum" FROM "public"."my_table" it is giving null value instead of number

Rahul Over a year ago

and query with ::int is giving below error SequelizeDatabaseError: invalid input syntax forinteger: "field_id"

mike.k Over a year ago

@Rahul You'll need to filter out non-integer values using the where clause, I linked to a related answer in my post

Collectives™ on Stack Overflow

Sequelize how to use aggregate function on Postgres JSONB column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related