Postgres count values in json column

Question

I have a column of json arrays, and I need to produce counts of the values inside the arrays

so if my data looks like

["1", "2"]
["1", "2"]
["1", "2", "3"]
["1"]
["2"]
["2"]

I need to produce something like

{"1" => 4, "2" => 5, "3" => 1}

Is this kind of aggregation possible in Postgres? I'm honestly not sure where to start.

AdamKG · Accepted Answer · 2021-04-13 16:14:35Z

3

Use jsonb_array_elements() to turn the array into a SETOF (rows of output). Then just aggregate normally.

testdb=# with input_data as (select '["1", "1", "2"]'::jsonb j)
SELECT elem, count(elem)
FROM input_data, jsonb_array_elements(input_data.j) AS elem
GROUP BY 1
ORDER BY 1;
 elem | count 
------+-------
 "1"  |     2
 "2"  |     1
(2 rows)

answered Apr 13, 2021 at 16:14

AdamKG

14.1k3 gold badges41 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Postgres count values in json 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