如何用PostgreSQL原生操作替代Python代码实现JSONB字段高效更新

方案1：单条UPDATE语句（推荐，性能最优） #

全程只需1次数据库交互，无需将JSONB字段读取到应用层处理，完全规避原方案两次IO+序列化反序列化的性能损耗，SQL如下：

UPDATE employee
SET time_log = CASE
    -- 判断日期key是否存在
    WHEN COALESCE(time_log, '{}'::jsonb) ? %s THEN
        -- 存在则将打卡数据追加到对应数组
        jsonb_set(
            COALESCE(time_log, '{}'::jsonb),
            ARRAY[%s],
            time_log -> %s || %s::jsonb
        )
    ELSE
        -- 不存在则新增key，值为包含当前打卡数据的单元素数组
        jsonb_set(
            COALESCE(time_log, '{}'::jsonb),
            ARRAY[%s],
            to_jsonb(ARRAY[%s::jsonb])
        )
END
WHERE pk = %s;

参数说明（按占位符顺序）：

• 第1/2/3/5个占位符：目标日期字符串，例如 '2021-08-13'
• 第4/6个占位符：打卡数据JSON对象，例如 {"clock_in": "2021-08-15T04:16:41.307Z", "clock_out": "2021-08-14T21:16:46.714-07:00"}
• 第7个占位符：要更新的员工主键ID

方案2：封装为PostgreSQL存储函数 #

如果需要多次调用，可以封装为PG原生函数，调用更简洁：

CREATE OR REPLACE FUNCTION update_employee_time_log(
    p_pk INT, 
    p_dt TEXT, 
    p_clock_data JSONB
) RETURNS VOID AS $$
BEGIN
    UPDATE employee
    SET time_log = CASE
        WHEN COALESCE(time_log, '{}'::jsonb) ? p_dt THEN
            jsonb_set(
                COALESCE(time_log, '{}'::jsonb),
                ARRAY[p_dt],
                time_log -> p_dt || p_clock_data
            )
        ELSE
            jsonb_set(
                COALESCE(time_log, '{}'::jsonb),
                ARRAY[p_dt],
                to_jsonb(ARRAY[p_clock_data])
            )
    END
    WHERE pk = p_pk;
END;
$$ LANGUAGE plpgsql VOLATILE;

调用方式：

SELECT update_employee_time_log(
    1001,
    '2021-08-13',
    '{"clock_in": "2021-08-15T04:16:41.307Z", "clock_out": "2021-08-14T21:16:46.714-07:00"}'::jsonb
);

Django ORM适配代码 #

替换你原有的Python代码即可直接使用：

from django.db import connection

def insert_data(pk, data):
    dt = data.pop("date")
    with connection.cursor() as cursor:
        cursor.execute("""
            UPDATE employee
            SET time_log = CASE
                WHEN COALESCE(time_log, '{}'::jsonb) ? %s THEN
                    jsonb_set(COALESCE(time_log, '{}'::jsonb), ARRAY[%s], time_log -> %s || %s::jsonb)
                ELSE
                    jsonb_set(COALESCE(time_log, '{}'::jsonb), ARRAY[%s], to_jsonb(ARRAY[%s::jsonb]))
            END
            WHERE pk = %s
        """, [dt, dt, dt, data, dt, data, pk])

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