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


1 Answer 1


You need a two step aggregation:

select bar_id, 
       jsonb_object_agg(externalidentifier, internals) as mappings
from (       
  SELECT foos.bar_id,
         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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.