Get Group by and count on Array field in PostgreSQL Jsonb column

Question

I have my table like this

user_Id	survey
1001	{"What are you interested in?": "[Games]", "How do you plan to use Gamester?": "[Play games, Watch other people play, Connect with other gamers]" }
1001	{"What are you interested in?": "[Coupons]", "How do you plan to use Gamester?": "[Watch other people play]" }
1001	{"What are you interested in?": "[Games]", "How do you plan to use Gamester?": "[Play games]" }

I want to group by second field How do you plan to use Gamester? And have output like below

Option	Count
Play games	2
Watch other people play	2
Connect with other gamers	1

All the examples I found were for String fields and not for array. Tried few thing but not working. Kindly help how can I group on array field values in josonb column and get count.

emomaliev · Accepted Answer · 2022-05-21 04:18:10Z

1

Your json data array in the How do you plan to use Gamester? property is wrong. Since you are storing a string and not an array of values.

Replace all values as shown below:

Source Format: "[Play games, Watch other people play, Connect with other gamers]"

Required Format: ["Play games", "Watch other people play", "Connect with other gamers"]

After fixing your data, you can use the jsonb_path_query function and return a list of values from the desired property, and then group them.

with cte_plan as (
  select
  jsonb_path_query(survey, '$."How do you plan to use Gamester?"[*]') as option
  from my_table
)
select option, count(option) from cte_plan group by option;

Result

option	count
Watch other people play	2
Play games	2
Connect with other gamers	1

Demo in DBfiddle

answered May 21, 2022 at 4:18

emomaliev

2,4233 gold badges12 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Get Group by and count on Array field in PostgreSQL Jsonb column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related