如何在Google BigQuery中提取列中的$revenue与$price值

这里有两种可靠的方法帮你提取目标值：

方法一：利用JSON解析（推荐） #

你的列内容是标准JSON格式，用BigQuery的JSON函数比正则更稳定，完全不需要关心键在字符串中的位置：

• 提取$revenue：使用JSON_EXTRACT_SCALAR函数，注意路径里的$需要转义（因为JSON路径中$是根节点标识）
• 提取$price：同理操作

SQL示例 #

WITH sample_data AS (
  SELECT '{"utm_medium":"direct","utm_amplitude_user_id":"1580904318308","$quantity":1,"Locale":"English","$revenue":56.49,"Source":"App","utm_date":"2020-02-05","AppVersion":"Mac","$price":56.49,"utm_initial_medium":"direct"}' AS event_data
  UNION ALL
  SELECT '{"utm_initial_source":"none","utm_medium":"direct","utm_amplitude_user_id":"1580904318308","$quantity":1,"Locale":"English","$revenue":56.49,"Source":"App","utm_date":"2020-02-05","AppVersion":"Mac","$price":56.49,"utm_source":"none","Device":"Desktop"}' AS event_data
)
SELECT
  -- 提取字符串格式的数值
  JSON_EXTRACT_SCALAR(event_data, '$.\\$revenue') AS revenue_str,
  JSON_EXTRACT_SCALAR(event_data, '$.\\$price') AS price_str,
  -- 转成数值类型（如NUMERIC）
  CAST(JSON_EXTRACT_SCALAR(event_data, '$.\\$revenue') AS NUMERIC) AS revenue_num,
  CAST(JSON_EXTRACT_SCALAR(event_data, '$.\\$price') AS NUMERIC) AS price_num
FROM sample_data;

方法二：使用正则表达式提取 #

如果必须用正则，可以通过匹配键名后的数值来提取，适配键在任意位置的情况：

• 匹配$revenue的正则：'"\\$revenue":(\d+\.\d+)'，捕获组1就是对应数值
• 匹配$price的正则：'"\\$price":(\d+\.\d+)'

SQL示例 #

WITH sample_data AS (
  SELECT '{"utm_medium":"direct","utm_amplitude_user_id":"1580904318308","$quantity":1,"Locale":"English","$revenue":56.49,"Source":"App","utm_date":"2020-02-05","AppVersion":"Mac","$price":56.49,"utm_initial_medium":"direct"}' AS event_data
  UNION ALL
  SELECT '{"utm_initial_source":"none","utm_medium":"direct","utm_amplitude_user_id":"1580904318308","$quantity":1,"Locale":"English","$revenue":56.49,"Source":"App","utm_date":"2020-02-05","AppVersion":"Mac","$price":56.49,"utm_source":"none","Device":"Desktop"}' AS event_data
)
SELECT
  -- 提取字符串格式的数值
  REGEX_EXTRACT(event_data, '"\\$revenue":(\d+\.\d+)') AS revenue_str,
  REGEX_EXTRACT(event_data, '"\\$price":(\d+\.\d+)') AS price_str,
  -- 转成数值类型（如NUMERIC）
  CAST(REGEX_EXTRACT(event_data, '"\\$revenue":(\d+\.\d+)') AS NUMERIC) AS revenue_num,
  CAST(REGEX_EXTRACT(event_data, '"\\$price":(\d+\.\d+)') AS NUMERIC) AS price_num
FROM sample_data;

注：如果数值可能是整数（如$revenue:100），可以把正则调整为'"\\$revenue":(\d+(\.\d+)?)'，兼容整数和小数情况。

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