By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE content (
id VARCHAR(32),
rel VARCHAR(32),
tgt VARCHAR(32)
);
✓
INSERT INTO
content
VALUES
('Bob' , 'id' , 'Bob'),
('Bob' , 'is' , 'Person'),
('Bob' , 'age' , '20'),
('Bob' , 'likes', 'cake'),
('Bob' , 'likes', 'chocolate'),
('Alice', 'id' , 'Alice'),
('Alice', 'is' , 'Person'),
('Alice', 'hates', 'chocolate')
✓
WITH
id_rel AS
(
SELECT
id,
rel,
JSON_GROUP_ARRAY(tgt) AS tgt
FROM
content
GROUP BY
id,
rel
)
SELECT
JSON_GROUP_OBJECT(
rel,
CASE WHEN rel='id'
THEN JSON(tgt)->0
ELSE JSON(tgt)
END
)
AS entity
FROM
id_rel
GROUP BY
id
ORDER BY
id
| entity |
|---|
| {"hates":["chocolate"],"id":"Alice","is":["Person"]} |
| {"age":["20"],"id":"Bob","is":["Person"],"likes":["cake","chocolate"]} |