I have a bunch of dynamic survey results stored in a table with a JSON column that looks like so:
{"regional":"never","local":"sometimes","personal":"often","bike":"field_4","walk":"field_5","carpool":"field_4","carshare":"often","rideshare":"sometimes"}
The keys are values that exist dynamically throughout the dataset. I need to find an efficient way to aggregate the keys and count the correlated results. This data represents a Likert scale survey question ie.
"How often do you go to the bus station? Not Often, Sometimes, Very Often"
The resulting dataset I'd like to produce is:
{
"regional": { "sometimes": 20, "often": 10, "never": 5 },
"bike": { "sometimes": 20, "often": 10, "never": 5 }
"walk": { "sometimes": 20, "often": 10, "never": 5 }
}
I've attempted JSON_EACH to break the values into key/value pairs but I'm now having trouble aggregating the keys and frequency of potential results. This is what I have so far:
SELECT
json_data.key,
json_data.value
FROM
"SubmissionData" AS sd,
JSON_EACH_TEXT(sd.data) AS json_data
WHERE
sd.key = 'transit'
Definitely hit a roadblock here though.
Thanks.
Use json_object_agg(), e.g.:
with my_table(data) as (
values
('{"regional":"never","local":"sometimes","personal":"often"}'::json),
('{"regional":"often","local":"sometimes","personal":"often"}'::json)
)
select json_object_agg(key, vals)
from (
select key, json_object_agg(value, count) vals
from (
select key, value, count(*)
from
my_table,
json_each_text(data)
group by 1, 2
) s
group by 1
) s;
json_object_agg
------------------------------------------------------------------------------------------------------------
{ "regional" : { "never" : 1, "often" : 1 }, "personal" : { "often" : 2 }, "local" : { "sometimes" : 2 } }
(1 row)