SQLite 3.39

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"]}