PLSQL中如何将存储JSON对象的CLOB转换为可关联查询的表

首先你给出的示例JSON存在语法错误，数组对象之间的分号需替换为逗号，修正后合法格式为：
[{"a":"1","b":"1"}, {"a":"2", "b":"2"}, {"a":"2","b":"2"}]
以下方案均基于合法JSON数组格式的CLOB内容实现，覆盖主流数据库场景：

Oracle 数据库（12c及以上版本） #

使用原生JSON_TABLE函数直接解析CLOB中的JSON内容，32k以上的大CLOB需增加FORMAT JSON关键字修饰字段：

SELECT t.*
FROM 你的业务表名 src,
     JSON_TABLE(src.存储JSON的CLOB字段名 FORMAT JSON, '$[*]'
         COLUMNS (
             a VARCHAR2(10) PATH '$.a',
             b VARCHAR2(10) PATH '$.b'
         )
     ) t;

返回的结果集可直接作为子查询和其他数据库表做关联查询。

MySQL 数据库 #

8.0及以上版本 #

使用JSON_TABLE函数实现，需先将CLOB字段转为JSON类型：

SELECT t.a, t.b
FROM 你的业务表名 src
JOIN JSON_TABLE(
    CAST(src.存储JSON的CLOB字段名 AS JSON),
    '$[*]' COLUMNS (
        a VARCHAR(10) PATH '$.a',
        b VARCHAR(10) PATH '$.b'
    )
) t ON TRUE;

5.7版本 #

无原生JSON_TABLE函数，可配合序列表实现解析：

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(CAST(src.存储JSON的CLOB字段名 AS JSON), CONCAT('$[', idx.i, '].a'))) AS a,
    JSON_UNQUOTE(JSON_EXTRACT(CAST(src.存储JSON的CLOB字段名 AS JSON), CONCAT('$[', idx.i, '].b'))) AS b
FROM 你的业务表名 src
JOIN (
    -- 按需扩展序列值到JSON数组的最大长度即可
    SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
) idx ON idx.i < JSON_LENGTH(CAST(src.存储JSON的CLOB字段名 AS JSON));

PostgreSQL 数据库 #

使用jsonb_to_recordset函数实现，需先将CLOB字段转为jsonb类型：

SELECT t.a, t.b
FROM 你的业务表名 src,
     jsonb_to_recordset(CAST(src.存储JSON的CLOB字段名 AS jsonb)) AS t(a text, b text);

通用注意事项 #

• 解析前建议先校验CLOB中的JSON内容合法性，避免语法错误导致查询报错
• 解析定义的字段类型需要和JSON中存储的实际值类型匹配，避免隐式转换异常
• 如果JSON结构不固定，可先提取JSON的所有键名再动态生成查询语句

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