在BigQuery中提取通配符键的技术实现方案咨询

针对你描述的场景，核心是处理JSON字段中动态命名的键，可以通过BigQuery的JSON函数结合UNNEST实现拆解，同时保留空值行。

完整SQL实现（desc为字符串类型） #

如果你的desc列是字符串格式的JSON，用以下代码：

WITH original_data AS (
  SELECT 
    'btjap' AS id,
    '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' AS desc
  UNION ALL
  SELECT 
    'fxlol' AS id,
    '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' AS desc
  UNION ALL
  SELECT 
    'nretx' AS id,
    '{"val":{}}' AS desc
)
SELECT
  id,
  IFNULL(code, '') AS code,
  IFNULL(JSON_EXTRACT_SCALAR(desc, CONCAT('$.val.', code, '.key')), '') AS key
FROM original_data
LEFT JOIN UNNEST(
  -- 空对象时返回空数组，避免丢失原行
  IF(JSON_EXTRACT(desc, '$.val') = '{}', [], JSON_OBJECT_KEYS(JSON_EXTRACT(desc, '$.val')))
) AS code

简化版本（desc为JSON类型） #

如果desc列已经是BigQuery的JSON类型，代码可以更简洁：

WITH original_data AS (
  SELECT 
    'btjap' AS id,
    JSON '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' AS desc
  UNION ALL
  SELECT 
    'fxlol' AS id,
    JSON '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' AS desc
  UNION ALL
  SELECT 
    'nretx' AS id,
    JSON '{"val":{}}' AS desc
)
SELECT
  id,
  IFNULL(code, '') AS code,
  IFNULL(desc.val[code].key, '') AS key
FROM original_data
LEFT JOIN UNNEST(
  IF(desc.val = JSON '{}', [], JSON_OBJECT_KEYS(desc.val))
) AS code

关键逻辑说明 #

• JSON_OBJECT_KEYS：提取val对象下的所有动态键，返回数组格式
• UNNEST：将数组拆分为多行，实现一对多的展开
• LEFT JOIN：确保val为空对象的行（如nretx）不会被过滤，保留原id并返回空的code和key
• IF判断：处理空对象场景，避免UNNEST空数组时丢失原行
• JSON_EXTRACT_SCALAR/直接引用：根据动态键提取对应的key值，用IFNULL转为空字符串符合预期输出

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