PostgreSQL中从JSONB列移除指定JSON对象的方法求助

你的核心问题是json_workouts为JSONB数组类型，而之前的操作均针对JSON对象设计，因此无法生效。以下是两种可行的解决方法：

方法1：展开数组过滤后重新聚合 #

通过jsonb_array_elements将数组拆分为单个元素，过滤掉目标对象后，再用jsonb_agg重新聚合成数组：

UPDATE my_users
SET json_workouts = COALESCE(
  (SELECT jsonb_agg(elem)
   FROM jsonb_array_elements(json_workouts) elem
   WHERE elem <> '{"name": "vmi2", "path": "efefe\\feff\\fefefe", "type": "Gym"}'::jsonb),
  '[]'::jsonb
)
WHERE username = 'c';

• COALESCE用于处理过滤后数组为空的场景，确保最终值是空数组而非null
• 需保证目标对象的JSON格式与数组中元素完全匹配，包括转义字符（如路径中的反斜杠）

方法2：使用JSON路径查询（PostgreSQL 12+） #

若你的PostgreSQL版本为12或更高，可使用jsonb_path_query_array直接过滤数组，写法更简洁：

UPDATE my_users
SET json_workouts = jsonb_path_query_array(
  json_workouts,
  '$[*] ? (@ != {"name": "vmi2", "path": "efefe\\feff\\fefefe", "type": "Gym"})'
)
WHERE username = 'c';

原语句无效原因说明 #

• 第一个DELETE语句：DELETE FROM my_users是删除整个用户记录，而非修改JSONB数组。同时json_workouts ->> 'name'无法从数组中提取元素的name字段——数组本身没有name键，必须遍历数组元素才能访问其内部属性。
• 第二个UPDATE语句：json_workouts - 'name'用于删除JSON对象中的指定键，但你的json_workouts是数组类型，该操作对数组不生效。

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