SQLite使用json_extract提取JSON父子层级对象数据的实现问题

你可以通过嵌套CTE（公用表表达式）+ 二次调用json_tree的方式实现需求，无需使用UNION操作，具体SQL如下：

WITH target_control AS (
  -- 第一步：先提取匹配ID的目标控件完整对象及对应type值
  SELECT 
    value AS control_obj,
    json_extract(value, '$.type') AS control_type
  FROM forms, json_tree(forms.formJSON, '$.root') 
  WHERE type = 'object'
    AND json_extract(value, '$.id') = @sControlID
)
SELECT 
  tc.control_type AS `type`,
  jt.key AS `key`,
  json_extract(jt.value, '$.bind') AS `value`
FROM target_control tc, json_tree(tc.control_obj) jt
WHERE 
  jt.parent = '$' -- 仅筛选控件对象的直接子键，避免挖入更深层级
  AND jt.type = 'object' -- 要求子键对应的值为对象格式
  AND json_type(jt.value, '$.bind') = 'text' -- 要求对象包含字符串类型的bind字段

逻辑说明 #

• 第一层CTE先锁定你要查询的ID对应的控件对象，同时提前提取固定的type字段值，避免后续重复解析JSON
• 第二层对目标控件对象单独调用json_tree遍历，通过jt.parent = '$'限制只取控件的直接子属性，不会匹配到更深层级的bind字段
• 最后的筛选条件刚好匹配{"bind":"<string>"}的格式要求，不存在符合条件的子对象时查询会直接返回空结果，符合你的要求

你提供的示例数据执行上述查询后，返回结果和你预期的格式完全一致。

内容的提问来源于stack exchange，提问作者user7711581