I have an actions_table
looking like:
profile_id | action_name | action_count 1 | action1 | 2 1 | action2 | 5 2 | action1 | 3 2 | action2 | 6 2 | action3 | 7 3 | action1 | 1
I would like to aggregate by profile_id and jsonify as:
profile_id | actions_count 1 | {"action1": 2, "action2": 5} 2 | {"action1": 3, "action2": 6, "action3": 7} 3 | {"action1": 1}
The closest I could get to is:
profile_id | actions_count 1 | [{"action1": 2}, {"action2": 5}] 2 | [{"action1": 3}, {"action2": 6}, {"action3": 7}] 3 | [{"action1": 1}]
By means of the query:
select profile_id, jsonb_agg(jsonb_build_object(action_name, action_count)) as "actions_count" from actions_table group by profile_id order by profile_id
How do I get to my desired result from here (convert list of dicts into dict), or how do I fix my original query?
CAVEATS
- I need a general answer, I might have > 10 action_names.
- I would like to avoid text replacement, e.g.
concat('{',replace(replace(replace(replace(jsonb_agg(jsonb_build_object(action_name, action_count)))::text, '[', ''), ']', ''), '{', '' ), '}', ''), '}')::jsonb
Advertisement
Answer
This can be done using jsonb_object_agg()
select profile_id, jsonb_object_agg(action_name, action_count) from actions_table group by profile_id;