PostgreSQL 10如何更新存储JSON结构的text字段，为null的earliestDate2赋值数组首个日期

PostgreSQL 10 对应更新语句实现 #

假设存储JSON结构的text字段名为json_field，所属表名为your_table，请替换为实际业务的真实字段名和表名后使用：

预览验证语句（建议先执行验证结果，避免脏数据）

SELECT 
  json_field AS 原值,
  jsonb_set(
    json_field::jsonb,
    '{earliestDate2}',
    to_jsonb((json_field::jsonb -> 'earliestDate' -> 0 ->> 'date')::text)
  )::text AS 预期更新值
FROM your_table
WHERE 
  -- 仅匹配earliestDate2为null的记录
  (json_field::jsonb ->> 'earliestDate2') IS NULL
  -- 过滤earliestDate为非空数组、且第一个元素存在date值的记录，避免执行异常
  AND jsonb_array_length(json_field::jsonb -> 'earliestDate') > 0
  AND (json_field::jsonb -> 'earliestDate' -> 0 ? 'date');

正式更新语句

UPDATE your_table
SET json_field = jsonb_set(
    json_field::jsonb,
    '{earliestDate2}',
    to_jsonb((json_field::jsonb -> 'earliestDate' -> 0 ->> 'date')::text)
)::text
WHERE 
  (json_field::jsonb ->> 'earliestDate2') IS NULL
  AND jsonb_array_length(json_field::jsonb -> 'earliestDate') > 0
  AND (json_field::jsonb -> 'earliestDate' -> 0 ? 'date');

逻辑说明 #

• 由于存储JSON的字段是text类型，需要先强转成jsonb类型才能调用PostgreSQL内置的JSON操作函数，处理完成后再转回text类型存回原字段
• jsonb_set是JSONB结构的属性修改函数，入参依次为原JSONB对象、要修改的属性路径、新的属性值
• WHERE条件做了多层校验，避免earliestDate为空数组、第一个元素无date字段的记录执行时报错

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