BigQuery中Shopify order_line表properties字段键值对转列方案咨询

核心问题说明 #

FiveTran同步的Shopify properties字段为字符串类型，通常存在多余转义符导致无法直接被JSON函数识别，需要先做格式清洗再解析。

完整查询语句 #

WITH cleaned_order_line AS (
  SELECT
    order_line_id,
    -- 清洗properties字符串为合法JSON数组：去除多余转义符、首尾多余引号
    SAFE.PARSE_JSON(
      REGEXP_REPLACE(
        REGEXP_REPLACE(properties, r'^"|"$', ''), -- 去除首尾多余的双引号包裹
        r'\\"', '"' -- 替换转义的双引号为正常双引号
      )
    ) AS properties_json
  FROM `你的项目名.你的数据集名.order_line` -- 替换为你自己的表路径
)
SELECT
  order_line_id,
  -- 逐个提取目标字段，不存在则返回NULL
  MAX(IF(prop.name = 'shipping_interval_unit_type', prop.value, NULL)) AS shipping_interval_unit_type,
  MAX(IF(prop.name = 'charge_on_day_of_week', prop.value, NULL)) AS charge_on_day_of_week,
  MAX(IF(prop.name = 'charge_interval_frequency', prop.value, NULL)) AS charge_interval_frequency,
  MAX(IF(prop.name = 'charge_on_day_of_month', prop.value, NULL)) AS charge_on_day_of_month,
  MAX(IF(prop.name = 'subscription_id', prop.value, NULL)) AS subscription_id,
  MAX(IF(prop.name = 'number_charges_until_expiration', prop.value, NULL)) AS number_charges_until_expiration,
  MAX(IF(prop.name = 'shipping_interval_frequency', prop.value, NULL)) AS shipping_interval_frequency
FROM cleaned_order_line,
UNNEST(IFNULL(properties_json, [])) AS prop -- 避免空数组报错
GROUP BY order_line_id

关键逻辑说明 #

• 使用SAFE.PARSE_JSON替代直接JSON函数，解析失败时会返回NULL不会中断查询
• 用REGEXP_REPLACE处理FiveTran同步时产生的多余转义字符，适配Shopify导出的properties格式
• 采用条件聚合+GROUP BY order_line_id的方式，保证每个order_line_id只输出一行，无需额外JOIN操作
• 目标字段不存在时自动返回NULL，兼容properties中缺少对应name的场景

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