Postgres: flatten Key-Value (K-V) store with jsonb_agg

Question

I have two tables: bar and foo (one to many respectively).

The foo table has an internalId and an external reference. The issue here is that the external reference can be duplicated.

I want my query result to have two columns: bar_id and mappings (where bar_id is not duplicated) where the key is the duplicate id and the values list are all the internal ids it is related to. So far I have been able to aggregate the duplicate external ids to the internal id but not grouped those one more level i.e with data:

('100001', 1, '1a'),
('100001', 2, '1a'),
('100001', 3, '1b')

I can get the result:

100001 {"1a": [1, 2]},
100001 {"1b": [3]}

But I want:

100001 {"1a": [1, 2], "1b": [3]}

Fiddle here: https://www.db-fiddle.com/f/pKbFeRedD59s4f3VTnuyih/0

• Vérace: +1 for an interesting question (with fiddle!!) and great to see you back. :-)

Answer 1

You need a two step aggregation:

select bar_id, 
 jsonb_object_agg(externalidentifier, internals) as mappings
from ( 
 SELECT foos.bar_id,
 externalidentifier, 
 jsonb_agg(internalIdentifier) as internals
 FROM foos
 GROUP BY bar_id, externalidentifier
) t
group by bar_id;

Your updated fiddle: https://www.db-fiddle.com/f/pKbFeRedD59s4f3VTnuyih/0