如何在SELECT查询中移除指定JSON字段？原查询未生效求助

你的details字段存储的JSON数据如下：

{"display_data": {"_template": "activity/mr/updated.tpl","user_name": "User","id": 16808554},"display_data_title": {"_template": "activity/event_title/mr/updated.tpl","is_task": false,"id": 16808554}}

你需要在SELECT查询中移除display_data->_template字段以及整个display_data_title字段，但原查询无法达到预期效果，原查询语句：

SELECT e.id
     e.details,
    e.details - '{ "display_data", "_template" }' AS new_details
FROM events e
     JOIN event_sub_types est ON (est.id = e.event_sub_type_id)
WHERE e.id= 1 AND
      e.property_id IN (7) AND
      e.event_type_id IN (2) 
ORDER BY e.id DESC

针对不同数据库的正确解决方法 #

PostgreSQL #

PostgreSQL里，-操作符仅能删除JSON顶层键，要处理嵌套字段得结合jsonb_set和-操作符来实现：

SELECT 
    e.id,
    e.details,
    jsonb_set(
        e.details::jsonb - 'display_data_title',
        '{display_data}',
        (e.details::jsonb -> 'display_data') - '_template'
    ) AS new_details
FROM events e
JOIN event_sub_types est ON est.id = e.event_sub_type_id
WHERE e.id = 1 
  AND e.property_id IN (7) 
  AND e.event_type_id IN (2) 
ORDER BY e.id DESC

操作逻辑：

1. 先把details转为jsonb类型（json类型不支持修改操作）
2. 用- 'display_data_title'直接删除顶层的display_data_title键
3. 通过jsonb_set修改display_data字段，将原display_data去除_template后替换回去

MySQL #

MySQL可以直接用JSON_REMOVE函数处理嵌套路径，支持一次性删除多个目标字段：

SELECT 
    e.id,
    e.details,
    JSON_REMOVE(
        JSON_REMOVE(e.details, '$.display_data_title'),
        '$.display_data._template'
    ) AS new_details
FROM events e
JOIN event_sub_types est ON est.id = e.event_sub_type_id
WHERE e.id = 1 
  AND e.property_id IN (7) 
  AND e.event_type_id IN (2) 
ORDER BY e.id DESC

操作逻辑：

• 外层JSON_REMOVE删除顶层的display_data_title字段
• 内层JSON_REMOVE删除display_data下的_template字段
• 也可以合并成一个JSON_REMOVE调用，用逗号分隔多个路径：JSON_REMOVE(e.details, '$.display_data_title', '$.display_data._template')

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