By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE people (id INTEGER, name TEXT);
✓
CREATE TABLE junction_pants (person INTEGER, pants INTEGER);
✓
CREATE TABLE junction_shirts (person INTEGER, shirts INTEGER);
✓
CREATE TABLE junction_shoes (person INTEGER, shoes INTEGER);
✓
INSERT INTO people (id, name) VALUES
('1', 'John'), ('2', 'Mary'), ('3', 'Jane');
✓
INSERT INTO junction_shirts (person, shirts) VALUES
('1', '3'), ('1', '4'), ('2', '3'), ('3', '1');
✓
INSERT INTO junction_pants (person, pants) VALUES
('1','3'), ('2', '2');
✓
INSERT INTO junction_shoes (person, shoes) VALUES
('1', '5'), ('2', '2');
✓
WITH cte AS (SELECT person, ('[' || GROUP_CONCAT(clothing,',') || ']') AS clothing
FROM (SELECT person, json_object('type','shirt','id',shirts) AS clothing
FROM junction_shirts
UNION
SELECT person, json_object('type','pant','id',pants) AS clothing
FROM junction_pants
UNION
SELECT person, json_object('type','shoe','id',shoes) AS clothing
FROM junction_shoes)
GROUP BY person)
SELECT p.id, p.name, c.clothing
FROM people p LEFT JOIN cte c
ON c.person = p.id;
| id | name | clothing |
|---|---|---|
| 1 | John | [{"type":"pant","id":3},{"type":"shirt","id":3},{"type":"shirt","id":4},{"type":"shoe","id":5}] |
| 2 | Mary | [{"type":"pant","id":2},{"type":"shirt","id":3},{"type":"shoe","id":2}] |
| 3 | Jane | [{"type":"shirt","id":1}] |