Metabase中提取MySQL JSON数据指定字段的方法咨询

核心思路 #

你的JSON是数组类型，需先将数组拆分为单个JSON对象，再提取目标字段。以下是Metabase兼容的MySQL原生写法：

方案1：使用JSON_TABLE（推荐，MySQL 8.0+） #

JSON_TABLE是MySQL官方用于拆分JSON数组的函数，Metabase完全兼容，可直接将数组内的每个对象展开为单行记录并提取字段：

SELECT 
  jt.type,
  jt.rate_period,
  jt.amount
FROM deals p,
     JSON_TABLE(
       p.deal_info,
       '$[*]' COLUMNS(
         type VARCHAR(50) PATH '$.type',
         rate_period VARCHAR(50) PATH '$.rate_period',
         amount DECIMAL(10,2) PATH '$.amount'
       )
     ) jt

方案2：低版本MySQL兼容写法（无JSON_TABLE时） #

若你的MySQL版本低于8.0，可通过->>运算符（MySQL原生语法，Metabase支持）结合数组下标逐个提取，再用UNION ALL合并结果：

-- 提取数组第一条数据
SELECT
  p.deal_info->>'$[0].type' AS type,
  p.deal_info->>'$[0].rate_period' AS rate_period,
  p.deal_info->>'$[0].amount' AS amount
FROM deals p
UNION ALL
-- 提取数组第二条数据
SELECT
  p.deal_info->>'$[1].type' AS type,
  p.deal_info->>'$[1].rate_period' AS rate_period,
  p.deal_info->>'$[1].amount' AS amount
FROM deals p
WHERE JSON_LENGTH(p.deal_info) >= 2

注：此方法需提前知晓数组最大长度，适合固定长度的JSON数组场景。

原SQL失败原因 #

你的原语句存在两处关键问题：

1. 语法错误：多了一个冗余的右括号)，且::json是PostgreSQL的类型转换语法，MySQL无需使用
2. 未处理数组结构：直接提取{}无法定位到数组内的具体对象，必须通过数组下标或拆分函数处理数组层级

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