Skip to content
Advertisement

Convert list of dictionaries into dictionary in Postgres jsonb

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;

Online example

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement