如何在BigQuery中用Python UDF或json_extract查询JSON数据？

我的表结构特殊：当total_order_items_quantity=1时，event_properties下的商品字段（如item_brand、item_id等）为单值；当total_order_items_quantity>=2时，这些字段是数组。我想把数据展开为每行对应一个商品的格式，但直接用unnest(json_extract_array(event_properties,'$.item_brand'))会因为字段类型不统一（同时存在单值和数组）导致查询失败，该怎么实现？

表结构示例 #

当total_order_items_quantity = 1时： #

{
  "user_id": "694520",
  "event_properties": {
    "item_brand": "P.A.M.",
    "item_color": "CAROLINA BLUE",
    "item_discount_price": "45000",
    "item_discount_rate": "0",
    "item_gender": "M",
    "item_id": "137194",
    "item_name": "A+ SS TEE",
    "item_price": "45000",
    "item_size": "XL",
    "total_order_items_quantity": 1
  }
}

当total_order_items_quantity >= 2时： #

{
  "user_id": "694520",
  "event_properties": {
    "item_brand": [
      "NIKE",
      "NIKE",
      "NIKE",
      "NIKE",
      "NIKE",
      "NIKE",
      "NIKE",
      "NIKE",
      "NIKE"
    ],
    "item_color": [
      "BAROQUE BROWN VELVET BROWN BAROQUE BROWN",
      "BAROQUE BROWN VELVET BROWN BAROQUE BROWN",
      "BAROQUE BROWN VELVET BROWN BAROQUE BROWN",
      "BAROQUE BROWN VELVET BROWN BAROQUE BROWN",
      "FLASH/WHITE-ARGON BLUE-FLASH",
      "FLASH/WHITE-ARGON BLUE-FLASH",
      "FLASH/WHITE-ARGON BLUE-FLASH",
      "FLASH/WHITE-ARGON BLUE-FLASH",
      "FLASH/WHITE-ARGON BLUE-FLASH"
    ],
    "item_discount_price": [
      88960,
      88960,
      88960,
      88960,
      95360,
      95360,
      95360,
      95360,
      95360
    ],
    "item_discount_rate": [
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0
    ],
    "item_gender": [
      "M",
      "M",
      "M",
      "M",
      "U",
      "U",
      "U",
      "U",
      "U"
    ],
    "item_id": [
      "140312",
      "140312",
      "140312",
      "140312",
      "141028",
      "141028",
      "141028",
      "141028",
      "141028"
    ],
    "item_name": [
      "DUNK LOW RETRO PRM",
      "DUNK LOW RETRO PRM",
      "DUNK LOW RETRO PRM",
      "DUNK LOW RETRO PRM",
      "DUNK LOW RETRO QS",
      "DUNK LOW RETRO QS",
      "DUNK LOW RETRO QS",
      "DUNK LOW RETRO QS",
      "DUNK LOW RETRO QS"
    ],
    "item_price": [
      111200,
      111200,
      111200,
      111200,
      119200,
      119200,
      119200,
      119200,
      119200
    ],
    "item_size": [
      "285",
      "285",
      "290",
      "290",
      "230",
      "230",
      "230",
      "230",
      "230"
    ],
    "total_order_items_quantity": 9
  }
}

目标输出格式 #

核心思路是先把所有单值字段统一转成数组类型，再用unnest/explode展开。不同SQL引擎的函数语法略有差异，下面给出几种常见场景的实现：

1. BigQuery SQL #

利用IF判断字段类型，把单值包装成数组，再通过偏移量确保多字段展开时位置对应：

SELECT
  user_id,
  item_brand,
  item_discount_price,
  item_discount_rate
FROM
  your_table,
  UNNEST(
    IF(
      total_order_items_quantity = 1,
      [JSON_VALUE(event_properties, '$.item_brand')],
      JSON_EXTRACT_ARRAY(event_properties, '$.item_brand')
    )
  ) AS item_brand WITH OFFSET pos,
  UNNEST(
    IF(
      total_order_items_quantity = 1,
      [JSON_VALUE(event_properties, '$.item_discount_price')],
      JSON_EXTRACT_ARRAY(event_properties, '$.item_discount_price')
    )
  ) AS item_discount_price WITH OFFSET pos1,
  UNNEST(
    IF(
      total_order_items_quantity = 1,
      [JSON_VALUE(event_properties, '$.item_discount_rate')],
      JSON_EXTRACT_ARRAY(event_properties, '$.item_discount_rate')
    )
  ) AS item_discount_rate WITH OFFSET pos2
WHERE pos = pos1 AND pos1 = pos2

2. Spark SQL #

用CASE WHEN统一转数组后，通过posexplode绑定偏移量避免字段错位：

SELECT
  user_id,
  item_brand,
  item_discount_price,
  item_discount_rate
FROM (
  SELECT
    user_id,
    CASE
      WHEN total_order_items_quantity = 1 THEN array(get_json_object(event_properties, '$.item_brand'))
      ELSE get_json_object(event_properties, '$.item_brand')
    END AS item_brand_arr,
    CASE
      WHEN total_order_items_quantity = 1 THEN array(get_json_object(event_properties, '$.item_discount_price'))
      ELSE get_json_object(event_properties, '$.item_discount_price')
    END AS item_discount_price_arr,
    CASE
      WHEN total_order_items_quantity = 1 THEN array(get_json_object(event_properties, '$.item_discount_rate'))
      ELSE get_json_object(event_properties, '$.item_discount_rate')
    END AS item_discount_rate_arr
  FROM your_table
) t
LATERAL VIEW posexplode(item_brand_arr) exploded_brand AS pos, item_brand
LATERAL VIEW posexplode(item_discount_price_arr) exploded_price AS pos, item_discount_price
LATERAL VIEW posexplode(item_discount_rate_arr) exploded_rate AS pos, item_discount_rate
WHERE exploded_brand.pos = exploded_price.pos AND exploded_price.pos = exploded_rate.pos

3. Hive SQL #

逻辑与Spark一致，通过CASE WHEN将单值转数组后展开：

SELECT
  user_id,
  item_brand,
  item_discount_price,
  item_discount_rate
FROM (
  SELECT
    user_id,
    CASE
      WHEN total_order_items_quantity = 1 THEN array(json_extract_scalar(event_properties, '$.item_brand'))
      ELSE json_extract(event_properties, '$.item_brand')
    END AS item_brand_arr,
    CASE
      WHEN total_order_items_quantity = 1 THEN array(json_extract_scalar(event_properties, '$.item_discount_price'))
      ELSE json_extract(event_properties, '$.item_discount_price')
    END AS item_discount_price_arr,
    CASE
      WHEN total_order_items_quantity = 1 THEN array(json_extract_scalar(event_properties, '$.item_discount_rate'))
      ELSE json_extract(event_properties, '$.item_discount_rate')
    END AS item_discount_rate_arr
  FROM your_table
) t
LATERAL VIEW explode(item_brand_arr) exploded_brand AS item_brand
LATERAL VIEW explode(item_discount_price_arr) exploded_price AS item_discount_price
LATERAL VIEW explode(item_discount_rate_arr) exploded_rate AS item_discount_rate
-- 若数组长度一致可省略偏移量判断，需严格对应则需结合pos字段处理

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