如何在PostgreSQL中原地修改jsonb，调整结构优化查询

需求说明 #

当前user_tracking表的item_json字段存储格式为单键值对（键为动作类型，值为坐标字符串），需要统一调整为{"action": "动作名", "value": "坐标"}的标准结构，以优化后续查询效率。

验证转换结果（可选） #

先执行查询确认转换后的结构是否符合预期：

SELECT 
  id,
  date,
  jsonb_build_object(
    'action', (jsonb_each_text(item_json)).key,
    'value', (jsonb_each_text(item_json)).value
  ) AS new_item_json
FROM user_tracking;

执行全表更新 #

直接运行以下语句完成所有数据的结构修正：

UPDATE user_tracking
SET item_json = jsonb_build_object(
  'action', (jsonb_each_text(item_json)).key,
  'value', (jsonb_each_text(item_json)).value
);

大数据量表分批更新（可选） #

如果表数据量较大，为避免长时间锁表影响业务，可按id分批次执行更新：

WITH batch AS (
  SELECT id FROM user_tracking WHERE id BETWEEN 1 AND 1000 -- 根据实际数据调整批次范围
)
UPDATE user_tracking ut
SET item_json = jsonb_build_object(
  'action', (jsonb_each_text(ut.item_json)).key,
  'value', (jsonb_each_text(ut.item_json)).value
)
FROM batch b
WHERE ut.id = b.id;

核心逻辑说明 #

• jsonb_each_text(item_json)：将原jsonb对象拆分为键值对记录，由于每条数据的item_json仅包含一个键值对，可精准提取动作名称（key）和坐标值（value）。
• jsonb_build_object：用提取出的内容构造符合要求的标准jsonb结构。

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