如何在Presto中过滤VARCHAR类型JSON列，仅保留值大于0的键？

MySQL 实现方案 #

1. 将VARCHAR类型的JSON字符串转为JSON类型，通过JSON_TABLE拆解键值对
2. 筛选出值大于0的条目
3. 用JSON_OBJECT_AGG重新聚合成目标JSON格式

示例SQL：

SELECT 
  JSON_OBJECT_AGG(j.key, j.value) AS filtered_json
FROM 
  your_table,
  JSON_TABLE(
    CAST(your_json_column AS JSON),
    '$.*' COLUMNS(
      `key` VARCHAR(255) PATH '$',
      `value` INT PATH '$'
    )
  ) AS j
WHERE 
  j.value > 0
GROUP BY 
  your_table.id; -- 按表主键分组，确保每条记录对应一个过滤后的JSON

PostgreSQL 实现方案 #

1. 将VARCHAR转为jsonb类型，用jsonb_each拆解键值对
2. 过滤值大于0的条目
3. 用jsonb_object_agg聚合结果

示例SQL：

SELECT 
  jsonb_object_agg(j.key, j.value) AS filtered_json
FROM 
  your_table,
  jsonb_each(your_json_column::jsonb) AS j(key, value)
WHERE 
  (j.value::int) > 0
GROUP BY 
  your_table.id;

注意事项 #

• 替换your_table为你的表名，your_json_column为存储JSON的VARCHAR列名，id为表的主键或唯一标识列
• 若JSON中的值是字符串形式的数字，需先做类型转换再判断大小

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