求助：将BigQuery表中JSON字符串转为行列的无函数SQL方案

针对你的需求，我们可以用BigQuery原生JSON函数来处理嵌套结构，以下分两种场景给出方案：

场景1：JSON结构固定（已知所有键） #

如果你的JSON结构相对固定，直接逐层提取字段并展开数组即可，这种方式性能最优，也完全适配Tableau：

-- 替换成你的表名和JSON列名
SELECT
  -- 顶层标量字段
  JSON_EXTRACT_SCALAR(json_column, '$.participant_id') AS participant_id,
  JSON_EXTRACT_SCALAR(json_column, '$.demog_work_schedule') AS demog_work_schedule,
  JSON_EXTRACT_SCALAR(json_column, '$.Comments') AS Comments,
  -- 展开rd_visit_focus_area数组（LEFT JOIN保留无数组的行）
  focus_area AS rd_visit_focus_area,
  -- 提取嵌套health对象的字段
  JSON_EXTRACT_SCALAR(json_column, '$.health.center') AS health_center,
  JSON_EXTRACT_SCALAR(json_column, '$.health.height') AS health_height,
  JSON_EXTRACT_SCALAR(json_column, '$.health.weight') AS health_weight,
  -- 展开health下的conditions数组
  condition AS health_condition
FROM `your-project.your-dataset.your-table`
LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.rd_visit_focus_area')) AS focus_area
LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.health.conditions')) AS condition

场景2：JSON结构不固定（层级/键名有差异） #

如果你的JSON结构多变，需要动态提取所有键值对，可以用BigQuery的JSON_KEYS和递归解析来实现：

WITH raw_data AS (
  SELECT json_column AS raw_json FROM `your-project.your-dataset.your-table`
),
-- 提取顶层所有键和对应值
top_level AS (
  SELECT
    raw_json,
    JSON_KEYS(raw_json)[OFFSET(pos)] AS key,
    JSON_PATH_QUERY(raw_json, CONCAT('$[', pos, ']')) AS value
  FROM raw_data,
  UNNEST(JSON_PATH_EXTRACT_ARRAY(raw_json, '$.*')) WITH OFFSET pos
),
-- 区分值类型并处理
parsed_top AS (
  SELECT
    raw_json,
    key,
    JSON_TYPE(value) AS value_type,
    -- 提取标量值
    IF(JSON_TYPE(value) = 'STRING', JSON_EXTRACT_SCALAR(value, '$'), NULL) AS scalar_value,
    -- 提取数组
    IF(JSON_TYPE(value) = 'ARRAY', JSON_EXTRACT_ARRAY(value, '$'), NULL) AS array_value,
    -- 保留嵌套对象
    IF(JSON_TYPE(value) = 'OBJECT', value, NULL) AS nested_obj
  FROM top_level
),
-- 解析嵌套对象的键值
parsed_nested AS (
  SELECT
    pt.raw_json,
    CONCAT(pt.key, '.', nk.key) AS full_key,
    nk.scalar_value,
    nk.array_value
  FROM parsed_top pt,
  UNNEST(JSON_PATH_EXTRACT_ARRAY(pt.nested_obj, '$.*')) WITH OFFSET pos,
  UNNEST([STRUCT(
    JSON_KEYS(pt.nested_obj)[OFFSET(pos)] AS key,
    JSON_PATH_QUERY(pt.nested_obj, CONCAT('$[', pos, ']')) AS value
  )]) nk,
  UNNEST([STRUCT(
    IF(JSON_TYPE(nk.value) = 'STRING', JSON_EXTRACT_SCALAR(nk.value, '$'), NULL) AS scalar_value,
    IF(JSON_TYPE(nk.value) = 'ARRAY', JSON_EXTRACT_ARRAY(nk.value, '$'), NULL) AS array_value
  )])
  WHERE pt.nested_obj IS NOT NULL
)
-- 合并顶层和嵌套层的结果
SELECT raw_json, key, scalar_value, array_value FROM parsed_top WHERE value_type IN ('STRING', 'ARRAY')
UNION ALL
SELECT raw_json, full_key AS key, scalar_value, array_value FROM parsed_nested
-- 若需展开数组，可在此基础上再UNNEST array_value字段

关键函数说明 #

• JSON_EXTRACT_SCALAR: 提取字符串、数字等标量JSON值
• JSON_EXTRACT_ARRAY: 将JSON数组转为BigQuery数组，配合UNNEST展开为多行
• JSON_KEYS: 原生函数，直接提取JSON对象的所有键名
• JSON_TYPE: 判断JSON值的类型（STRING/ARRAY/OBJECT），用于分支处理
• JSON_PATH_QUERY: 根据路径提取JSON值，适配动态键名场景

Tableau适配提示 #

1. 优先用场景1的固定结构方案，返回扁平化结果，Tableau更容易处理
2. 可以把SQL保存为BigQuery视图，直接在Tableau中连接视图，避免在Tableau中编写复杂SQL
3. 若用场景2的动态方案，建议在BigQuery中预先展开数组，再导入Tableau，避免嵌套结构导致的解析问题

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