CODEWITHSUNDEEP
sqlpostgresqljsonb
Meena Chaudhary
Meena Chaudhary

Reputation: 10675

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

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.

Upvotes: 0

Views: 125

Answers (1)

emomaliev
emomaliev

Reputation: 2383

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

Upvotes: 1

Related Questions