使用JSON1 json_each的递归SQLite CTE

mbzjlibv 于 2023-10-23 发布在 SQLite

关注(0)|答案(2)|浏览(126)

我有一个SQLite表，其中一列包含一个JSON数组，该数组包含0个或多个值。大概是这样的：

id|values
0 |[1,2,3]
1 |[]
2 |[2,3,4]
3 |[2]

我想做的是将其“展开”为该列的数组中包含的所有不同值的列表。
首先，我使用JSON1扩展的json_each函数从一行中提取一个值表：

SELECT
  value
FROM
  json_each(
      (
        SELECT
          values
        FROM
          my_table
        WHERE
          id == 2
      )
  )

在这里我可以改变id（上面的2）来选择表中的任何一行。
现在，我尝试将其 Package 在递归CTE中，以便可以将其应用于整个表中的每一行并合并结果。作为第一步，我大致复制了上面的结果如下：

WITH RECURSIVE result AS (
  SELECT null
  UNION ALL
  SELECT
    value
  FROM
      json_each(
          (
            SELECT
              values
            FROM
              my_table
            WHERE
              id == 2
          )
      )  
)
SELECT * FROM result;

下一步，我原本计划让id成为一个变量并递增它（以类似于文档中第一个示例的方式，但还没有能够让它工作。
我已经浏览了文档中的其他示例，但是它们有点复杂，我还没有能够提炼出它们来看看它们如何应用于这个问题。
有人能提供一个简单的例子，说明如何用递归CTE解决这个问题（或类似的问题）吗？
当然，我的目标是解决这个问题，无论有没有CTE，所以我也很高兴听到是否有更好的方法。

sqlite

来源：https://stackoverflow.com/questions/44821117/recursive-sqlite-cte-with-json1-json-each

2条答案

按热度按时间

yvgpqqbh1#

您不需要为此使用递归CTE。
要为多个源行调用json_each，请使用联接：

SELECT t1.id, t2.value
FROM my_table AS t1
JOIN json_each((SELECT "values" FROM my_table WHERE id = t1.id)) AS t2;

赞(0）回复(0）举报 2023-10-23

jvlzgdj92#

我也遇到过类似的问题。在我的例子中，输入是json格式的pmd报告（使用格式-f json），它像这样嵌套多次

{
  "formatVersion": 0, 
  "pmdVersion": "7.0.0-rc3", 
  "timestamp": "2023-08-19T01:11:39.497+02:00",
  "files": [
     { "filename": "D:\\A.cls", "violations": [ {v1}, {v2}, {v3} ]},
     { "filename": "D:\\B.cls", "violations": [ {v4}, {v5}, {v6} ]}
  ],
  "suppressedViolations": [],
  "processingErrors": [],
  "configurationErrors": []
}

数组files: []中的每个文件[f1, f2]可以有n个违规[v1, v2, v3]
我用来提取json对象和一些属性的SQL如下所示

使用公共表表达式

WITH CTE1 AS(
 SELECT PMD_Reports.id, PMD_Reports.report_date, jsonEachFiles.value as File
     FROM PMD_Reports
          ,json_each(PMD_Reports.format_json, '$.files') AS jsonEachFiles
)
SELECT 
    CTE1.id, 
    CTE1.report_date, 
    substr(json_extract(CTE1.File, '$.filename'), 28) as filename,
    json_extract(jsonEachViolation.value, '$.ruleset') as ruleset,
    json_extract(jsonEachViolation.value, '$.rule') as rule,  
    json_extract(jsonEachViolation.value, '$.priority') as priority,
    json_extract(jsonEachViolation.value, '$.description') as description       
    FROM CTE1
         ,json_each(CTE1.File, '$.violations') AS jsonEachViolation

使用子查询

SELECT ruleset, rule, priority, Count(Distinct filename) as files_affected FROM
(
  SELECT 
     T1.id,
     substr(json_extract(T1.Files, '$.filename'), 28) as filename, 
     json_extract(T2.value, '$.ruleset') as ruleset,
     json_extract(T2.value, '$.rule') as rule,  
     json_extract(T2.value, '$.priority') as priority,
     json_extract(T2.value, '$.description') as description
    FROM (
          SELECT PMD_Reports.id, jsonEachFiles.Value as Files
          FROM PMD_Reports
          ,json_each(PMD_Reports.format_json, '$.files') AS jsonEachFiles           
    ) AS T1, json_each(T1.Files, '$.violations') as T2
) as T3
GROUP BY T3.ruleset, T3.rule, T3.priority

分解

json_each(PMD_Reports.format_json, '$.files')对files-array进行解包，结果是每个文件一行。
json_each(T1.Files, '$.violations')解包每个文件的每个违规
json_extract(T2.value, '$.priority') as priority,读取违规的属性priority（T2.value包含违规对象）

赞(0）回复(0）举报 2023-10-23

我来回答

使用JSON1 json_each的递归SQLite CTE

2条答案

使用公共表表达式

使用子查询

相关问题

热门标签

最新问答