Postgres Count number of items in an array jsonb fields

Question

I have a postgres table like this

activity

comment    likes
Hi         ["bill", "susan"]
Hello      ["susan", "john", "joe"]

I'm trying to get the total number of likes.

Likes is of type jsonb

I know I could select all the data and then loop through it with an increment and count it, but that would create performance issues once you get a lot of data.

Is there a way to do it with a single query?

For the above example, the total number of likes should be 5

Jordash · Accepted Answer · 2019-08-06 15:40:18Z

1

The answer from @dassum was close, I just needed to adjust it to support jsonb

SELECT SUM(jsonb_array_length(likes)) FROM activity;

answered Aug 6, 2019 at 15:40

Jordash

3,1238 gold badges46 silver badges80 bronze badges

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

1 Comment

dassum Over a year ago

sorry, I missed jsonb. the good thing is your issue got resolved.

Collectives™ on Stack Overflow

Postgres Count number of items in an array jsonb fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related