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.
CodePudding user response：
Using the JSON functions:
SELECT jsonb_build_object(
'name',
name,
'PhoneNumbers',
jsonb_agg(phonenumber)
)
FROM tab
GROUP BY name;