By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `contact` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`name` TINYTEXT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `contact_relation` (
`contact_id` BIGINT NOT NULL,
`related_contact_id` BIGINT NOT NULL,
`type` TINYTEXT NOT NULL,
INDEX `fk_contact_relation_contact_idx` (`contact_id` ASC),
INDEX `fk_contact_relation_contact1_idx` (`related_contact_id` ASC),
CONSTRAINT `fk_contact_relation_contact`
FOREIGN KEY (`contact_id`)
REFERENCES `contact` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_contact_relation_contact1`
FOREIGN KEY (`related_contact_id`)
REFERENCES `contact` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO `contact` (`id`, `name`) VALUES
(1, 'Mark'),
(2, 'Matthew'),
(3, 'Luke'),
(4, 'John');
INSERT INTO `contact_relation` (`contact_id`, `related_contact_id`, `type`) VALUES
(1, 2, 'reference'),
(1, 2, 'association'),
(1, 3, 'association'),
(2, 3, 'reference'),
(3, 4, 'association');
| id | name |
|---|---|
| 1 | Mark |
| 2 | Matthew |
| 3 | Luke |
| 4 | John |
| contact_id | related_contact_id | type |
|---|---|---|
| 1 | 2 | reference |
| 1 | 2 | association |
| 1 | 3 | association |
| 2 | 3 | reference |
| 3 | 4 | association |
WITH
cte1 AS ( SELECT cr.contact_id,
cr.related_contact_id,
cr.type,
JSON_OBJECT('id', c.id, 'name', c.name) rel_obj
FROM contact_relation cr
JOIN contact c ON cr.related_contact_id = c.id ),
cte2 AS ( SELECT DISTINCT type
FROM contact_relation ),
cte3 AS ( SELECT DISTINCT contact_id
FROM contact_relation ),
cte4 AS ( SELECT type,
contact_id,
contact.name,
JSON_ARRAYAGG(rel_obj) rel_obj
FROM cte2
CROSS JOIN cte3
NATURAL LEFT JOIN cte1
JOIN contact ON cte3.contact_id = contact.id
GROUP BY type,
contact_id,
contact.name ),
cte5 AS ( SELECT contact_id id,
name,
MAX(CASE WHEN type = 'reference' THEN rel_obj END) reference,
MAX(CASE WHEN type = 'association' THEN rel_obj END) association
FROM cte4
GROUP BY contact_id,
name )
SELECT REPLACE(JSON_ARRAYAGG(JSON_OBJECT('id', id,
'name', name,
'references', reference,
'associations', association)), '[null]', '[]' )output
FROM cte5;
| output |
|---|
| [{"id": 1, "name": "Mark", "references": [{"id": 2, "name": "Matthew"}], "associations": [{"id": 2, "name": "Matthew"}, {"id": 3, "name": "Luke"}]}, {"id": 2, "name": "Matthew", "references": [{"id": 3, "name": "Luke"}], "associations": []}, {"id": 3, "name": "Luke", "references": [], "associations": [{"id": 4, "name": "John"}]}] |