PostgreSQL合并JSONB对象及按任务类型动态查字段的技术问询

1. 如何在PostgreSQL中合并两个JSONB对象？ #

PostgreSQL提供了几种实用的方式来合并JSONB对象，你可以根据版本和需求来选择：

方法1：使用||操作符（PostgreSQL 9.5+） #

这是最常用的合并方式，后面的对象会覆盖前面对象的重复键，适合简单的合并场景：

-- 示例：合并两个JSONB对象，重复键"b"被后面的值覆盖
SELECT '{"a": 1, "b": 2}'::jsonb || '{"b": 3, "c": 4}'::jsonb;
-- 结果：{"a": 1, "b": 3, "c": 4}

方法2：使用jsonb_merge（PostgreSQL 12+） #

如果需要合并嵌套的JSONB对象（而非直接替换整个嵌套键），这个函数会更合适：

-- 示例：合并嵌套对象，保留两个嵌套键
SELECT jsonb_merge('{"a": {"x": 1}}'::jsonb, '{"a": {"y": 2}}'::jsonb);
-- 结果：{"a": {"x": 1, "y": 2}}
-- 对比||操作符的结果：{"a": {"y": 2}}（直接替换整个"a"键）

方法3：处理空值的合并 #

如果其中一个对象可能为null，可以用COALESCE确保合并正常执行：

SELECT COALESCE(null::jsonb, '{}'::jsonb) || '{"a": 1}'::jsonb;
-- 结果：{"a": 1}

2. 修正你的PL/pgSQL任务查询函数 #

你的思路没问题，但代码里有几个语法错误和逻辑小问题，咱们一步步修正优化：

原代码的主要问题 #

• SQL里的等于判断是=，不是==
• 错误地在SELECT列表里用case语句赋值，不符合PL/pgSQL语法
• 合并JSONB用了错误的+操作符，正确的是||
• 字段名拼写错误：descrtiption → description
• 没有处理_task为null的情况（比如task_type不是email时）

修正后的完整函数 #

CREATE OR REPLACE FUNCTION crm.task_select(_task_uid bigint) 
RETURNS jsonb 
LANGUAGE plpgsql 
AS $function$
DECLARE
    _data jsonb;
    _task jsonb;
    _task_type text;
BEGIN
    -- 第一步：查询基础任务数据，同时获取task_type
    SELECT 
        jsonb_build_object(
            'id', task.task_uid,
            'action_date', task.action_date,
            'description', task.description, -- 修正拼写错误
            'subject_data', sub.subject_data,
            'task_type', dtask.task_type,
            'task_name', dtask.name,
            'task_label', dtask.label,
            'created_by', lbadmin.user_name_reverse(crea.role_uid),
            'updated_by', lbadmin.user_name_reverse(upda.role_uid),
            'created', task.created,
            'updated', task.updated
        ) AS base_data,
        dtask.task_type
    INTO _data, _task_type
    FROM crm.tasks "task"
    LEFT JOIN abook2.subjects "sub" ON task.subject_uid = sub.subject_uid
    LEFT JOIN crm.def_tasks "dtask" ON task.def_task_uid = dtask.def_task_uid
    LEFT JOIN lbadmin.users_user "crea" ON task.insert_user_uid = crea.role_uid
    LEFT JOIN lbadmin.users_user "upda" ON task.update_user_uid = upda.role_uid
    WHERE task.task_uid = _task_uid
      AND task.org_uid IS NULL
      AND task.card_uid IS NULL;

    -- 第二步：根据task_type调用对应的数据查询函数
    IF _task_type = 'email' THEN
        -- 如果selectEmail返回的是json，记得转成jsonb：::jsonb
        SELECT crm.selectEmail(_task_uid) INTO _task;
    -- 可以继续添加其他task_type的分支
    -- ELSIF _task_type = 'phone' THEN
    --     SELECT crm.selectPhone(_task_uid) INTO _task;
    END IF;

    -- 第三步：合并基础数据和类型专属数据，处理_task为null的情况
    _data := COALESCE(_task, '{}'::jsonb) || _data;

    RETURN _data;
END;
$function$;

优化说明 #

1. 用jsonb_build_object构造JSON：比直接将行转为JSON更灵活，能精准控制返回的键名和值
2. 单独获取task_type：用变量存储类型后，通过IF分支判断更清晰，后续维护也更方便
3. 处理空值：用COALESCE(_task, '{}'::jsonb)确保即使没有类型专属数据，合并也不会出错
4. 修正语法错误：统一SQL的等于判断、字段拼写，使用正确的JSONB合并操作符

如果后续需要支持更多task类型，直接添加ELSIF分支即可，扩展性很强。

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