SQLite JSON1：三元组转对象格式视图构建求助

问题背景 #

现有SQLite表triples，结构为{ id, rel, tgt }存储三元组数据，需创建视图将其转换为结构化JSON对象：

• id字段为字符串，对应每个实体的唯一标识
• 其他rel字段为数组，存储对应关系的所有去重tgt值
• 避免重复对象、重复键、id被转为数组等问题

正确查询语句 #

CREATE VIEW IF NOT EXISTS entity AS
SELECT
  json_patch(
    json_object('id', id_value),
    json_group_object(rel, tgt_array)
  ) AS entity
FROM (
  SELECT
    t.id,
    MAX(CASE WHEN rel = 'id' THEN tgt END) AS id_value,
    t.rel,
    json_group_array(DISTINCT t.tgt) AS tgt_array
  FROM triples t
  WHERE rel != 'id' AND t.tgt != t.id
  GROUP BY t.id, t.rel
)
GROUP BY id, id_value;

查询逻辑说明 #

1. 子查询处理基础分组

   • 按id和rel分组，确保每个实体的每个关系单独处理
   • 提取id字段的字符串值：利用MAX(CASE...)获取每个实体对应的唯一id标识（因每个实体仅一条rel='id'的记录）
   • 生成关系数组：通过json_group_array(DISTINCT t.tgt)为每个关系生成去重的tgt数组，同时过滤掉rel='id'和tgt等于id的冗余记录

2. 外层查询合并结构化对象

   • 按id分组，用json_group_object将同一实体的所有关系-数组键值对合并为一个对象
   • 用json_patch将单独的id字段合并到对象中，确保id以字符串形式存在且不会被其他关系覆盖

效果验证 #

针对示例输入：

输出结果符合预期：

{
  "id": "Bob",
  "is": ["Person"],
  "age": ["20"],
  "likes": ["cake", "chocolate"]
}

{
  "id": "Alice",
  "is": ["Person"],
  "hates": ["chocolate"]
}

内容的提问来源于stack exchange，提问作者Róisín Grannell