如何改写PostgreSQL单JSON文档查询适配多JSON行数据表？

假设你的数据表名为data_table，存储JSON的列名为json_data（可替换为实际的表名和列名），只需要把原查询中单个JSON变量json_source替换为表的JSON列，并通过**横向关联（LATERAL JOIN）**遍历表中每一行数据，对每行的JSON执行拆解操作即可。

基础改写版本（过滤无有效findings的行） #

SELECT
    json_array_elements(x.codes)->>'code' AS code
FROM
    data_table dt,
    json_to_recordset(dt.json_data->'findings') AS x(codes json);

显式LATERAL JOIN版本（可读性更强） #

SELECT
    json_array_elements(x.codes)->>'code' AS code
FROM
    data_table dt
LATERAL JOIN
    json_to_recordset(dt.json_data->'findings') AS x(codes json);

保留所有行的版本（兼容findings为空/无效的情况） #

如果表中存在部分行的JSON没有findings字段、findings不是数组，或者codes为空，想要保留这些行并返回NULL值，可以使用LEFT JOIN LATERAL：

SELECT
    json_array_elements(x.codes)->>'code' AS code
FROM
    data_table dt
LEFT JOIN LATERAL
    json_to_recordset(dt.json_data->'findings') AS x(codes json) ON true;

改写逻辑说明 #

• 原查询针对单个JSON变量，现在以数据表作为主数据源，遍历每一行的JSON列。
• json_to_recordset(dt.json_data->'findings')会将当前行JSON中的findings数组拆解为记录集，每个记录包含codes字段。
• 再通过json_array_elements(x.codes)把每个codes数组的元素展开为单独行，最终提取code字段的值。

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