PostgreSQL批量重命名JSON对象数组属性且不新增null值实现方法

问题场景 #

使用PostgreSQL存储JSON数组类型数据，表结构如下：

CREATE TABLE public.json_objects(id serial primary key, objects text);

表内示例数据：

INSERT INTO public.json_objects (objects) VALUES 
  ('[{"name":"Ivan"}]'), 
  ('[{"name":"Petr"}, {"surname":"Petrov"}]'), -- 原示例此处存在语法错误，已补全缺失的大括号
  ('[{"form":"OOO"}, {"city":"Kizema"}]');

需求为全局替换所有JSON数据中的属性名：name改为first name，surname改为second name。
原有实现逻辑在目标属性不存在时，会给JSON对象新增值为null的对应字段，不符合预期，原有SQL如下：

WITH updated_table AS (SELECT id, jsonb_agg(new_field_json) as new_fields_json 
FROM (SELECT id, jsonb_array_elements(json_objects.objects::jsonb) - 'name' || jsonb_build_object('first name', jsonb_array_elements(json_objects.objects::jsonb) -> 'name') new_field_json FROM public.json_objects) r group by id) UPDATE public.json_objects SET objects = updated_table.new_fields_json FROM updated_table where json_objects.id = updated_table.id

问题原因 #

原有逻辑无论原JSON对象是否存在name/surname键，都会通过jsonb_build_object生成对应键：键不存在时取值为null，最终会被拼接到结果对象中，产生多余的null字段。

可用方案 #

方案1：快速修复（适合业务中无刻意存储null值字段的场景） #

在拼接完单个JSON对象后，用jsonb_strip_nulls移除所有值为null的键即可，调整后SQL：

WITH updated_table AS (
  SELECT 
    id, 
    jsonb_agg(
      jsonb_strip_nulls(
        jsonb_array_elements(objects::jsonb) - 'name' - 'surname' 
        || jsonb_build_object('first name', jsonb_array_elements(objects::jsonb) -> 'name')
        || jsonb_build_object('second name', jsonb_array_elements(objects::jsonb) -> 'surname')
      )
    ) as new_fields_json 
  FROM public.json_objects
  GROUP BY id
) 
UPDATE public.json_objects 
SET objects = updated_table.new_fields_json 
FROM updated_table 
WHERE json_objects.id = updated_table.id;

方案2：严谨条件判断（适合业务中允许字段值为null的场景） #

如果业务本身存在JSON字段值为null的合法数据，不希望被jsonb_strip_nulls误删，可以通过?操作符判断键是否存在，仅当键存在时才做键名替换：

WITH parsed_data AS (
  SELECT 
    id,
    elem,
    CASE WHEN elem ? 'name' THEN jsonb_build_object('first name', elem->'name') ELSE '{}'::jsonb END
    || CASE WHEN elem ? 'surname' THEN jsonb_build_object('second name', elem->'surname') ELSE '{}'::jsonb END
    || (elem - 'name' - 'surname') AS new_elem
  FROM public.json_objects,
  jsonb_array_elements(objects::jsonb) AS elem
),
updated_table AS (
  SELECT id, jsonb_agg(new_elem) AS new_fields_json
  FROM parsed_data
  GROUP BY id
)
UPDATE public.json_objects
SET objects = new_fields_json
FROM updated_table
WHERE json_objects.id = updated_table.id;

执行结果 #

两种方案执行后，表内数据均符合预期，不会新增多余null字段：

• id=1：[{"first name": "Ivan"}]
• id=2：[{"first name": "Petr"}, {"second name": "Petrov"}]
• id=3：[{"form": "OOO"}, {"city": "Kizema"}]

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