PostgreSQL query : distinct count of jsonb values column

Question

I have a table with the name mainapp_project_data which has a jsonb column project_user_data

TABLE

 public | mainapp_project_data | table | admin

select project_user_data from mainapp_project_data;

                                         project_user_data

-----------------------------------------------------------------------------------------------------------------
 [{"name": "john", "age": "21", "gender": "M"}, {"name": "randy", "age": "23", "gender": "M"}]
 [{"name": "donald", "age": "31", "gender": "M"}, {"name": "wick", "age": "32",
"gender": "M"}]
 [{"name": "orton", "age": "18", "gender": "M"}, {"name": "russel", "age": "55",
"gender": "M"}]
 [{"name": "angelina", "age": "open", "gender": "F"}, {"name": "josep", "age": "21",
"gender": "M"}]
(4 rows)

(END)

I would like to count the distinct values of keys gender and age of JSON.

output format : [{key:count(repeated_values)}]

filtering on `gender` : [{"M":7},{"F":1}]
filtering on `age` : [{"21":2},{"23":1},{"31":1}.....]

Why the <mysql> tag?

jarlh
– jarlh

2019-12-18 21:38:09 +00:00
Commented Dec 18, 2019 at 21:38 — jarlh
– jarlh, Commented Dec 18, 2019 at 21:38

Miles Elam · Accepted Answer · 2019-12-19 17:37:12Z

WITH flat AS (
  SELECT
    kv.key,
    -- make into a JSON object with a single value and count, e.g., '{"M": 7}'
    jsonb_build_object(kv.value, COUNT(*)) AS val_count
  FROM mainapp_project_data AS mpd
    -- Flatten the JSON arrays into single objects per row
    CROSS JOIN LATERAL jsonb_array_elements(mpd.project_user_data) AS unarrayed(udata)
    -- Convert to a long, flat list of key-value pairs
    CROSS JOIN LATERAL jsonb_each_text(unarrayed.udata) AS kv(key, value)
  GROUP BY kv.key, kv.value
)
SELECT
  -- de-deplicated object keys
  flat.key,
  -- aggregation of all values and counts per key
  jsonb_agg(flat.val_count) AS value_counts
FROM flat
GROUP BY flat.key

Returns

  key   |                                                  value_counts
--------+---------------------------------------------------------------------------------------------------------------------
 gender | [{"M": 7}, {"F": 1}]
 name   | [{"josep": 1}, {"russel": 1}, {"orton": 1}, {"donald": 1}, {"wick": 1}, {"john": 1}, {"randy": 1}, {"angelina": 1}]
 age    | [{"18": 1}, {"32": 1}, {"21": 2}, {"23": 1}, {"open": 1}, {"31": 1}, {"55": 1}]

This will provide any key-value pair instance count. If you just want genders and ages, just add a where clause before the first GROUP BY clause.

  WHERE kv.key IN ('gender', 'age')

richyen · Accepted Answer · 2019-12-18 21:07:05Z

Does something like this work for you?

postgres=# select count(*), (foo->'gender')::text as g from (select json_array_elements(project_user_data) as foo from mainapp_project_data) as j group by (foo->'gender')::text;                           
 count |  g                                                                                           
-------+-----                                                                                         
     7 | "M"                                                                                          
     1 | "F"                                                                                          
(2 rows)                               

postgres=# select count(*), (foo->'age')::text as g from (select json_array_elements(project_user_data) as foo from mainapp_project_data) as j group by (foo->'age')::text;                                 
 count |   g                                                                                          
-------+--------                                                                                      
     2 | "21"                                                                                         
     1 | "32"                                                                                         
     1 | "open"                                                                                       
     1 | "23"                                                                                         
     1 | "18"                                                                                         
     1 | "55"                                                                                         
     1 | "31"                                                                                         
(7 rows)                                                                                              ```

Collectives™ on Stack Overflow

PostgreSQL query : distinct count of jsonb values column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related