How to group results by values that are inside json array in postgreSQL

user_8141265 0
Viewed 255

I have a column of type jSONB that have data like this:

column name: used_filters

row number 1 example:

{ "categories" : ["economic", "Social"], "tags": ["world" ,"eco-friendly"] }

row number 2 example:

{ "categories" : ["economic"], "tags": ["eco-friendly"] , "keywords" : ["2050"] }

I want to group the result to get the most frequent value for each one of the keys something like this:

key most_freq
category economic
tags eco-friendly
keyword 2050

the keys are not constant and could be something other than the example I said but I know that they will be frequent.

jsonb postgresql sql
1 Answers

You can extract keys and values as arrays first by using jsonb_each, and then unnest the generated arrays by jsonb_array_elements_text. The rest is classical aggregation along with sorting through the count values by window function such as

SELECT key, value
  FROM ( SELECT j.key, jj.value,  
                RANK() OVER (PARTITION BY j.key ORDER BY COUNT(*) DESC)
           FROM t,
        LATERAL jsonb_each(js) AS j,
        LATERAL jsonb_array_elements_text(j.value) AS jj
          GROUP BY j.key, jj.value ) AS q
 WHERE rank = 1

Demo

user_58413060
Related