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.
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)