CODEWITHSUNDEEP
postgresql
Aserian
Aserian

Reputation: 1117

PostgreSQL aggregate json objects into single json array

I have data like:

Name PhoneNumber
Bob {"type":"mobile", "number":"1-234-567-8910"}
Bob {"type":"work", "number":"1-555-555-5555"}
Jane {"type":"mobile", "number":"1-333-333-3333"}

I would like to combine over "Name" to be in a single json array like so:

PhoneNumbers
{"name": "Bob", "PhoneNumbers": [{"type":"mobile", "number":"1-234-567-8910"},{"type":"work", "number":"1-555-555-5555"}]}
{"name": "Jane", "PhoneNumbers": [{"type":"mobile", "number":"1-333-333-3333"}]}

I was looking at json_agg, but I am not sure how to group properly to get the results above.

Upvotes: 0

Views: 480

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

Using the JSON functions:

SELECT jsonb_build_object(
          'name',
          name,
          'PhoneNumbers',
          jsonb_agg(phonenumber)
       )
FROM tab
GROUP BY name;

Upvotes: 2

Related Questions