By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table user (id int, name varchar(50));
create table sale (id int, user int, item varchar(50))
insert into user values(1, 'User 1')
insert into user values(2, 'User 2')
insert into sale values (1, 1, 't-shirt');
insert into sale values (2, 1, 'jeans');
insert into sale values (3, 2, 'sweatpants');
insert into sale values (4, 2, 'gloves');
SELECT
JSON_PRETTY(JSON_ARRAYAGG(JSON_OBJECT('id', u.id, 'name', u.name, 'sales', s.sales)))
FROM
user u
left join (SELECT user, JSON_ARRAYAGG(JSON_OBJECT('id', id, 'item', item)) sales FROM sale GROUP BY user) s ON s.user = u.id
| JSON_PRETTY(JSON_ARRAYAGG(JSON_OBJECT('id', u.id, 'name', u.name, 'sales', s.sales))) |
|---|
| [ { "id": 1, "name": "User 1", "sales": [ { "id": 1, "item": "t-shirt" }, { "id": 2, "item": "jeans" } ] }, { "id": 2, "name": "User 2", "sales": [ { "id": 3, "item": "sweatpants" }, { "id": 4, "item": "gloves" } ] } ] |