I have an actions_table
looking like:
x
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;