Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table person
(
  id           serial primary key,
  name        varchar(50)
);

create table contacts
(
  id serial primary key,
  person_id int,
  key   varchar(50),
  value varchar(100),
  foreign key (person_id) references person (id)
);

insert into person values (1, 'A');
insert into person values (2, 'B');
insert into contacts values (1, 1, 'EMAIL', 'a@b.com');
insert into contacts values (2, 1, 'PHONE', '123');
insert into contacts values (3, 2, 'EMAIL', 'b@b.com');
insert into contacts values (4, 2, 'PHONE', '456');

SELECT * FROM contacts

CREATE TABLE



CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1




id
person_id
key
value


1
1
EMAIL
a@b.com

2
1
PHONE
123

3
2
EMAIL
b@b.com

4
2
PHONE
456



SELECT 4


SELECT
    person_id,
    json_agg(json_contacts)
FROM
    contacts,
    json_build_object('key', key, 'value', value) as json_contacts
GROUP BY person_id


person_id
json_agg


2
[{"key" : "EMAIL", "value" : "b@b.com"}, {"key" : "PHONE", "value" : "456"}]

1
[{"key" : "EMAIL", "value" : "a@b.com"}, {"key" : "PHONE", "value" : "123"}]



SELECT 2


SELECT 
    json_agg(json_person)
FROM
    person p
JOIN (
    SELECT
        person_id,
        json_agg(json_contacts) AS contacts
    FROM
        contacts,
        json_build_object('key', key, 'value', value) as json_contacts
    GROUP BY person_id
) c
ON c.person_id = p.id,
json_build_object('id', p.id, 'name', p.name, 'contacts', c.contacts) AS json_person


json_agg


[{"id" : 1, "name" : "A", "contacts" : [{"key" : "EMAIL", "value" : "a@b.com"}, {"key" : "PHONE", "value" : "123"}]}, {"id" : 2, "name" : "B", "contacts" : [{"key" : "EMAIL", "value" : "b@b.com"}, {"key" : "PHONE", "value" : "456"}]}]



SELECT 1

person_id	json_agg
2	[{"key" : "EMAIL", "value" : "b@b.com"}, {"key" : "PHONE", "value" : "456"}]
1	[{"key" : "EMAIL", "value" : "a@b.com"}, {"key" : "PHONE", "value" : "123"}]