如何在BigQuery中提取嵌套Array/STRUCT结构JSON字段的Stripe拒绝码

首先明确你提供的JSON字段结构：根节点下errors为数组类型，每个数组元素对应一笔错误，当错误的gateway字段值为stripe时，stripe对象下的decline_code即为你需要提取的Stripe拒绝码。
你之前报错大概率是因为没有先将字符串类型的JSON字段解析为对应SQL引擎支持的JSON类型，或是UNNEST时指定的数组路径错误，不需要硬套UNNEST，如果你只需要提取每笔记录的第一笔Stripe错误，直接按层级访问即可。

下面给出主流SQL引擎的实现代码：

BigQuery 实现 #

单条错误无需展开的写法（性能更高） #

假设你的表名为payment_logs，存储JSON字符串的字段名为error_msg：

SELECT 
  -- 提取stripe节点下的拒绝码
  JSON_VALUE(error_msg, '$.errors[0].stripe.decline_code') AS stripe_decline_code
FROM `your_project.your_dataset.payment_logs`
-- 过滤只有Stripe报错的记录
WHERE JSON_VALUE(error_msg, '$.errors[0].gateway') = 'stripe'

多错误需要全部展开的写法（用UNNEST） #

如果单条记录的errors数组可能存在多笔Stripe错误，需要全部提取：

SELECT 
  JSON_VALUE(error_item, '$.stripe.decline_code') AS stripe_decline_code
FROM `your_project.your_dataset.payment_logs`,
UNNEST(JSON_QUERY_ARRAY(error_msg, '$.errors')) AS error_item
WHERE JSON_VALUE(error_item, '$.gateway') = 'stripe'

PostgreSQL 实现 #

单条错误无需展开的写法 #

SELECT 
  (error_msg::jsonb -> 'errors' -> 0 -> 'stripe' ->> 'decline_code') AS stripe_decline_code
FROM payment_logs
WHERE error_msg::jsonb -> 'errors' -> 0 ->> 'gateway' = 'stripe'

多错误需要全部展开的写法（用UNNEST） #

SELECT 
  (error_item -> 'stripe' ->> 'decline_code') AS stripe_decline_code
FROM payment_logs,
jsonb_array_elements(error_msg::jsonb -> 'errors') AS error_item
WHERE error_item ->> 'gateway' = 'stripe'

常见报错排查 #

• 访问JSON属性前必须先将STRING类型转成对应引擎的JSON/JSONB类型，否则会报类型不匹配错误
• UNNEST只能作用于数组类型，所以必须先通过JSON_QUERY_ARRAY（BigQuery）或jsonb_array_elements（PostgreSQL）提取出errors数组再展开
• 注意层级不要写错，stripe是对象不是数组，不需要加索引访问

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