Postgres: flatten Key-Value (K-V) store with jsonb_agg 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
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