PrestoSQL如何从VARCHAR类型存储的JSON数组中提取merchant的data值

报错原因 #

报错是因为你操作的字段是VARCHAR字符串类型，不能直接调用JSON或数组处理函数，必须先转为对应数据库支持的JSON类型，同时要先修复存储内容里的JSON语法错误。

修复前置要求 #

你提供的原始存储内容存在JSON语法错误，需要先清洗数据保证合法性：

• 第一个对象的"type": "0缺少末尾的双引号，应为"type": "0"
• 第二个对象的"config": x不是合法JSON值，需改为合法的字符串/数字/null

不同数据库实现示例 #

PostgreSQL

SELECT data_value
FROM your_table,
-- 先转jsonb，展开外层数组
jsonb_array_elements(your_column::jsonb) outer_arr,
-- 提取config字段转jsonb，展开config数组
jsonb_array_elements((outer_arr -> 'config')::jsonb) config_arr,
-- 提取data数组展开为单行值
jsonb_array_elements_text(config_arr -> 'merchant' -> 'data') data_value
-- 过滤只取type为new的项，避免无效结构报错
WHERE outer_arr ->> 'type' = 'new';

执行后输出结果：

data_value
----------
10
12
23

MySQL 8.0+

SELECT dt.data_value
FROM your_table t
JOIN JSON_TABLE(
  -- 转JSON类型，提取target数据数组
  CAST(JSON_EXTRACT(t.your_column, '$[*]?(@.type == "new").config[0].merchant.data') AS JSON),
  '$[*]' COLUMNS(data_value INT PATH '$')
) dt;

通用逻辑

所有支持JSON操作的数据库都遵循以下流程：

1. 清洗VARCHAR字段内容为合法JSON格式
2. 显式将VARCHAR字段转为JSON类型
3. 按路径外层数组 → type为new的元素 → config数组第0项 → merchant → data数组定位到目标数组
4. 调用对应数据库的数组展开函数，将数组拆分为单行输出

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