Skip to content
Advertisement

Convert list of dictionaries into dictionary in Postgres jsonb

I have an actions_table looking like:

I would like to aggregate by profile_id and jsonify as:

The closest I could get to is:

By means of the query:

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

Online example

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