SQLize Online / PHPize Online  /  SQLtest Online

create table users ( id serial, name varchar(64) ); create table inventory ( id serial, user_id int, name varchar(64) ); insert into users (name) values ('User 1'); insert into inventory (user_id, name) values (1, 'Stuff 1'), (1, 'Stuff 2'), (1, 'Stuff 3'); select users.id, users.name, jsonb_agg(inventory.name) inventory from users left join inventory on inventory.user_id = users.id where users.id = 1 group by users.id, users.name ; select json_build_object( 'id', users.id, 'name', users.name, 'items', jsonb_agg(inventory.name) ) json_result from users left join inventory on inventory.user_id = users.id where users.id = 1 group by users.id, users.name ;