PostgreSQL复杂嵌套json数据修正案例

参考链接

1. PostgreSql 11 版本的json操作函数文档

问题描述

我们系统由于新需求的影响，需要对历史数据的一个json类型字段进行批量修正，需要写sql脚本用来执行修正。

让我们看一下那个json字段的数据结构（省略无关的部分）：

{
    "config": [
        {
            "inputStyle": "INTEGER",
            "strVal": "59",
            "name": "不及格"
        },
        {
            "inputStyle": "INTEGER",
            "strVal": "60",
            "name": "及格"
        },
        {
            "inputStyle": "INTEGER",
            "strVal": "70",
            "name": "良好"
        },
        {
            "inputStyle": "INTEGER",
            "strVal": "90",
            "name": "优秀"
        }
    ]
}

我们脚本要实现的目的是，将输入类型为数值型（inputStyle 为 INTEGER 或 DECIMAL）的且 strVal 的不为空且 intValue 不等于 strVal 的数据做一个数据转移：将有值的 strVal 复制粘贴给 intValue

遇到的第一个问题：如何判断每条记录的json字段里嵌套的json对象数组里，存在符合要求的元素

我们系统使用的postgresql版本是11，在无法使用新函数的前提下，我们只能自定义函数来调用。

-- 查询所有记录内数值类型的且有strVal值但是intValue为空的指标
-- 参数说明：jsonarr 是一个json对象数组
create or replace function jsonb_path_match_20231013(jsonarr jsonb)
    -- 函数在判定json对象数组存在符合条件的任意元素则返回 true，否则返回 false
    returns boolean as
$$
declare
    total              integer := 0;
    declare ar         RECORD;
    declare is_contain boolean := false;
begin
    -- 参数类型校验
    if 'array' <> jsonb_typeof(jsonarr) then
        RAISE exception '参数不是数组类型，无法处理';
    end if;
    -- 参数内容校验：空数组判定为false
    total := jsonb_array_length(jsonarr);
    if total < 1 then
        return false;
    end if;
    
    -- 遍历数组
    for ar in select jsonb_array_elements(jsonarr) as ele
        loop
            -- 如果遍历过程中数组元素出现了符合条件（true），则跳出循环
            exit when is_contain;
            -- 判断输入类型为数值型且 strVal 不为空而 intValue 不等于 strVal 的数组元素是否存在，存在则赋值 is_contain 为 true
                -- 如果输入类型为数值
            if ('DECIMAL' = (ar.ele ->> 'inputStyle') or 'INTEGER' = (ar.ele ->> 'inputStyle'))
                -- 且 strVal 不为空
            and ar.ele -> 'strVal' is not null
            and length(trim(ar.ele ->> 'strVal')) > 0
                -- 且intValue 不等于 strVal
            and (ar.ele -> 'intValue' is null or
                    (ar.ele ->> 'intValue')::decimal <> (trim(ar.ele ->> 'strVal'))::decimal) 
            then
                is_contain := true;
            end if;
        end loop;
    return is_contain;
end
$$
    language 'plpgsql';

遇到的第二个问题：函数一边调试一边写，需要输出日志观察调试结果

用下面的语法进行日志输出，输出日志在控制台查看：

RAISE NOTICE '某个变量的值：%' val;

遇到的第三个问题：更新json对象数组的部分（多个）元素

正常情况下，更新json结构字段，需要用到 jsonb_set 函数，但是需要一个明确的将要更新的json路径，但是要更新一个数组里的一部分元素就不适用。

实现思路：每个数组元素执行一次 update。

用存储过程实现

DO
$$
    -- rec 是符合条件的查询结果记录
    declare
        rec RECORD;
    BEGIN
        -- 查询所有json字段内嵌套数组的元素中存在输入类型为数值类型的且有 strVal 值但是 intValue 不等于 strVal 的记录
        FOR rec IN select
                       id,
                       config,
                       json_array_length(config -> 'config') as length
                   from
                       test_json_array_table
                   where
                         config is not null
                     and config -> 'config' is not null
                     and config #> '{config,0}' is not null
                     -- 调用上面写的函数判断json对象数组
                     and jsonb_path_match_20231013((config -> 'config')::jsonb)
            LOOP
                -- 遍历每条记录里的 "config.config[*]" json数组，i 为数组索引，从0开始
                for i in 0..(rec.length - 1)
                    loop
                                -- 如果输入类型为数值
                        if ('DECIMAL' = (rec.config::jsonb -> 'config' -> i ->> 'inputStyle') 
                            OR 'INTEGER' = (rec.config::jsonb -> 'config' -> i ->> 'inputStyle'))
                            -- 且 strVal 不为空
                        and rec.config::jsonb -> 'config' -> i -> 'strVal' is not null
                        and length(trim(rec.config::jsonb -> 'config' -> i ->> 'strVal')) > 0
                            -- 且 intValue 不等于 strVal
                        and (rec.config::jsonb -> 'config' -> i -> 'intValue' is null 
                            or (rec.config::jsonb -> 'config' -> i ->> 'intValue')::decimal 
                                <> (trim(rec.config::jsonb -> 'config' -> i ->> 'strVal'))::decimal) 
                        then
                            -- 分次更新每条记录里的每个intValue
                            update test_json_array_table
                            set config = jsonb_set(config::jsonb, array ['config', i::text, 'intValue'],
                                                (config::jsonb -> 'config' -> i ->> 'strVal')::decimal::varchar::jsonb,
                                                true)
                            where
                                id = rec.id;
                        end if;
                    end loop;
            end loop;
    END
$$;

遇到第四个问题：临时函数在执行完应该及时删除

-- 删除-只能执行一次
DROP FUNCTION if exists jsonb_path_match_20231013;

总结

关键知识：

1. 输出控制台日志：raise notice '某个变量的值：%',val;
2. 查看json字段数据类型：jsonb_typeof()
3. 查看json数组长度：json_array_length()
4. 获取JSON对象：->
5. 获取JSON对象的值：->>
6. 在指定路径获取JSON对象：#>
7. 将json数组扩展为对象集合，方便遍历：jsonb_array_elements()
8. 中断循环：exit when <condition>;
9. 清空字符串首尾空格：trim()
10. 变量赋值：:=
11. 类型转换：::
12. json对象设置值：jsonb_set()