PostgreSQL中SELECT查询调用函数处理JSON列的问题

问题核心 #

JSON类型是不可变的，无法直接通过price_tier->>'amount' = ...这种赋值方式修改内部字段。必须拆解数组元素，逐个处理后重新组装成新的JSON数组。

函数实现（JSON版本） #

以下是针对JSON类型的函数实现，遍历输入数组的每个对象，将amount字段值乘以指定汇率后替换原字段，最后返回处理后的JSON数组：

CREATE OR REPLACE FUNCTION apply_foreign_exchange(
    p_price_tiers JSON,
    p_conversion_rate NUMERIC
) RETURNS JSON AS $$
DECLARE
    v_processed_tier JSON;
    v_result JSON[] := '{}'::JSON[];
BEGIN
    -- 遍历输入JSON数组的每个元素
    FOR v_processed_tier IN SELECT json_array_elements(p_price_tiers) LOOP
        -- 计算新的amount值并替换原字段
        v_processed_tier := json_set(
            v_processed_tier,
            '{amount}',
            to_json((v_processed_tier->>'amount')::NUMERIC * p_conversion_rate)
        );
        -- 将处理后的元素加入结果数组
        v_result := array_append(v_result, v_processed_tier);
    END LOOP;
    -- 将数组转换为JSON格式返回
    RETURN array_to_json(v_result);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

函数实现（JSONB版本，推荐） #

如果你的字段是JSONB类型（PostgreSQL推荐的二进制JSON存储格式，性能更优），可以使用以下版本：

CREATE OR REPLACE FUNCTION apply_foreign_exchange(
    p_price_tiers JSONB,
    p_conversion_rate NUMERIC
) RETURNS JSONB AS $$
DECLARE
    v_processed_tier JSONB;
    v_result JSONB[] := '{}'::JSONB[];
BEGIN
    FOR v_processed_tier IN SELECT jsonb_array_elements(p_price_tiers) LOOP
        v_processed_tier := jsonb_set(
            v_processed_tier,
            '{amount}',
            to_jsonb((v_processed_tier->>'amount')::NUMERIC * p_conversion_rate)
        );
        v_result := array_append(v_result, v_processed_tier);
    END LOOP;
    RETURN array_to_jsonb(v_result);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

测试与调用 #

测试函数

SELECT apply_foreign_exchange(
    '[{"untilQuantity":600,"amount":999},{"untilQuantity":800,"amount":99},{"amount":9}]'::JSON,
    1.5
) AS "Charge Price Tiers";

输出结果：

[{"untilQuantity":600,"amount":1498.5},{"untilQuantity":800,"amount":148.5},{"amount":13.5}]

业务场景调用

你可以直接在SELECT语句中按需求调用：

SELECT 
    -- 其他字段
    apply_foreign_exchange(chrg.price_tiers, currency_conversion.conversion_rate) AS "Charge Price Tiers"
FROM 
    -- 你的表名
    your_table chrg
JOIN 
    currency_conversion ON -- 关联条件

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