PostgreSQL如何更新JSON字段中嵌套数组元素的指定键值

根据你使用的数据库类型不同，JSON字段的修改函数存在差异，以下是主流数据库可直接运行的实现语句：

MySQL 5.7+/8.0+ #

适配当前示例（houses数组仅1个元素） #

直接通过JSON路径定位数组下标修改即可，执行以下更新语句：

UPDATE HOUSE
SET SALE = JSON_SET(
  SALE,
  '$.houses[0].priceRange',
  JSON_OBJECT('fromAmount', '100', 'toAmount', '1000')
);

语句逻辑：直接定位到houses数组第1个元素（JSON数组下标从0开始）的priceRange字段，将原有null值替换为指定的JSON对象。

通用场景（houses数组存在多个元素） #

如果数组包含多个房源对象，需要批量替换所有对象中值为null的priceRange字段，使用以下语句：

-- 注意将语句中的id替换为HOUSE表实际的主键字段名
UPDATE HOUSE h
INNER JOIN (
  SELECT
    id,
    JSON_OBJECT(
      'houses',
      JSON_ARRAYAGG(
        JSON_SET(
          single_house,
          '$.priceRange',
          IF(
            single_house->>'$.priceRange' IS NULL,
            JSON_OBJECT('fromAmount', '100', 'toAmount', '1000'),
            single_house->'$.priceRange'
          )
        )
      )
    ) AS updated_sale
  FROM HOUSE,
  JSON_TABLE(
    SALE->'$.houses',
    '$[*]' COLUMNS (single_house JSON PATH '$')
  ) AS house_list
  GROUP BY id
) tmp ON h.id = tmp.id
SET h.SALE = tmp.updated_sale;

该逻辑会先把houses数组拆解为逐行的单个房源对象，判断每个对象的priceRange是否为null，是则替换为目标值，否则保留原有值，最后将处理后的对象重新组装为数组更新回原字段。

PostgreSQL #

使用jsonb_set函数处理，单元素场景语句：

UPDATE HOUSE
SET SALE = jsonb_set(
  SALE::jsonb,
  '{houses,0,priceRange}',
  '{"fromAmount":"100","toAmount":"1000"}'::jsonb
)::json;

SQL Server #

使用JSON_MODIFY函数处理，注意需要用JSON_QUERY包裹JSON对象避免被转义为字符串：

UPDATE HOUSE
SET SALE = JSON_MODIFY(
  SALE,
  '$.houses[0].priceRange',
  JSON_QUERY('{"fromAmount":"100","toAmount":"1000"}')
);

执行更新前建议先通过SELECT语句预览替换结果，避免误改数据，例如MySQL下可以先执行：

SELECT JSON_SET(
  SALE,
  '$.houses[0].priceRange',
  JSON_OBJECT('fromAmount', '100', 'toAmount', '1000')
) AS result FROM HOUSE;

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