PostgreSQL按条件移除JSON嵌套数组元素的SQL查询实现

针对你的public.config表结构及嵌套JSON需求，以下是根据QuestType和子元素QuestId移除RotationQuestsList中指定项的SQL实现：

场景说明 #

需要从payload->QuestsCommon数组里，找到QuestType为Daily的对象，再删除其RotationQuestsList中QuestId为1的元素。

基于JSON类型的UPDATE语句 #

如果你的payload字段是json类型，使用以下语句（记得替换你的配置ID为实际记录ID）：

UPDATE public.config
SET payload = (
  SELECT json_build_object(
    'QuestsCommon',
    json_agg(
      CASE
        WHEN q.quest_type = 'Daily' THEN
          json_build_object(
            'QuestType', q.quest_type,
            'RotationQuestsList', json_agg(rql)
          )
        ELSE q.original_obj
      END
    )
  )
  FROM (
    SELECT
      (elem->>'QuestType') AS quest_type,
      elem AS original_obj,
      json_array_elements(elem->'RotationQuestsList') AS rql
    FROM public.config, json_array_elements(payload->'QuestsCommon') AS elem
    WHERE id = '你的配置ID'
  ) q
  WHERE NOT (q.quest_type = 'Daily' AND rql->>'QuestId' = '1')
  GROUP BY q.quest_type, q.original_obj
)
WHERE id = '你的配置ID';

逻辑解释 #

1. 用json_array_elements拆解QuestsCommon数组，获取每个任务对象及对应的RotationQuestsList子元素
2. 通过CASE分支处理目标QuestType：对Daily类型的任务，重新聚合RotationQuestsList并过滤掉QuestId=1的元素；其他类型任务保持原结构
3. 最后用json_agg和json_build_object重新组装完整的payloadJSON结构

更高效的JSONB版本（推荐） #

如果可以将payload字段类型改为jsonb（PostgreSQL对jsonb的操作更高效且功能更丰富），可以使用更简洁的写法：

UPDATE public.config
SET payload = payload::jsonb #- array['QuestsCommon', idx::text, 'RotationQuestsList', r_idx::text]::text[]
FROM (
  SELECT
    id,
    idx,
    (SELECT idx FROM jsonb_array_elements(payload::jsonb->'QuestsCommon'->idx->'RotationQuestsList') WITH ORDINALITY arr(elem, idx) WHERE elem->>'QuestId' = '1') AS r_idx
  FROM public.config, jsonb_array_elements(payload::jsonb->'QuestsCommon') WITH ORDINALITY arr(elem, idx)
  WHERE elem->>'QuestType' = 'Daily' AND id = '你的配置ID'
) sub
WHERE public.config.id = sub.id AND sub.r_idx IS NOT NULL;

逻辑解释 #

1. 使用jsonb_array_elements WITH ORDINALITY获取数组元素的索引位置
2. 先定位到QuestType=Daily的对象在QuestsCommon中的索引idx，再找到QuestId=1的元素在RotationQuestsList中的索引r_idx
3. 通过#-操作符删除指定路径的JSON元素，路径由数组['QuestsCommon', idx, 'RotationQuestsList', r_idx]指定

注意事项 #

• 必须指定id条件定位目标记录，避免误更新全表
• 建议先执行对应的SELECT语句验证结果，确认无误后再执行UPDATE
• 如果需要批量处理多条记录，可以调整WHERE条件或结合循环逻辑实现

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