PostgreSQL aggregate json objects into single json array

Question

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.

Laurenz Albe · Accepted Answer · 2022-01-21 04:47:26Z

2

Using the JSON functions:

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

answered Jan 21, 2022 at 4:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aserian Over a year ago

Thanks! This did the trick. I achieved it with (SELECT row_to_json(_) FROM (SELECT name, json_agg(PhoneNumbers) AS PhoneNumbers) AS _) AS t1, but yours is certainly more elegant

Collectives™ on Stack Overflow

PostgreSQL aggregate json objects into single json array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related