BigQuery中如何实现JSON标量值的提取与自动类型转换？

你的代码无效的核心原因是CASE语句中不同分支返回的类型不统一（NULL、FLOAT64、BOOL、STRING、JSON），BigQuery会自动将所有结果转换为最宽泛的兼容类型（此处为STRING），导致数值、布尔类型被强制转为字符串，达不到预期效果。

以下是两种可行的实现方案：

方案1：仅处理标量类型（null/number/boolean/string） #

如果只需要提取标量值并转为对应SQL原生类型，可以去掉非标量分支，确保返回类型统一为可兼容的原生类型：

WITH base AS (
  SELECT JSON '{"foo": {"unknown":123}}' AS col
)
SELECT
  CASE JSON_TYPE(col, '$.foo.unknown')
    WHEN 'null' THEN NULL
    WHEN 'number' THEN LAX_INT64(col, '$.foo.unknown') -- 如需浮点类型可改用LAX_FLOAT64
    WHEN 'boolean' THEN LAX_BOOL(col, '$.foo.unknown')
    WHEN 'string' THEN LAX_STRING(col, '$.foo.unknown')
  END AS output
FROM base

说明： #

• JSON_TYPE(col, '$.foo.unknown')直接从原始JSON中判断目标路径的类型，无需提前提取子JSON
• LAX_*系列函数会自动处理路径不存在的情况，返回NULL（如果需要严格校验可改用SAFE_*或不带前缀的函数）

方案2：兼容标量与非标量类型 #

如果需要同时处理标量（转原生类型）和非标量（保留JSON格式），可以拆分两个字段分别存储：

WITH base AS (
  SELECT JSON '{"foo": {"unknown": {"nested": "value"}}}' AS col
)
SELECT
  -- 存储标量值（自动转换为对应SQL类型）
  CASE JSON_TYPE(col, '$.foo.unknown')
    WHEN 'null' THEN NULL
    WHEN 'number' THEN LAX_FLOAT64(col, '$.foo.unknown')
    WHEN 'boolean' THEN LAX_BOOL(col, '$.foo.unknown')
    WHEN 'string' THEN LAX_STRING(col, '$.foo.unknown')
  END AS scalar_output,
  -- 存储非标量JSON（数组/对象）
  CASE WHEN JSON_TYPE(col, '$.foo.unknown') IN ('array', 'object')
    THEN JSON_QUERY(col, '$.foo.unknown')
    ELSE NULL
  END AS json_output
FROM base

说明： #

• scalar_output字段会根据JSON类型返回对应的SQL原生类型（如INT64、BOOL、STRING）
• json_output字段仅存储数组或对象类型的JSON片段，保持原始格式

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