BigQuery中JSON_EXTRACT_SCALAR提取整数返回Null的原因

我已将JSON格式字符串加载至BigQuery，尝试用JSON_EXTRACT_SCALAR提取键值对，其余键都能正常提取，但整数类型的键（比如User.Type）却返回Null，相关查询代码如下：

SELECT *
, JSON_QUERY(json, "$.User") User
, JSON_EXTRACT_SCALAR(REPLACE(JSON_QUERY(json, "$.User"), '"', ''), "$.Key") User_Key
, JSON_EXTRACT_SCALAR(REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(JSON_QUERY(json, "$.User"), '"', ''), r': False', ': false'), r': True', ': true'), "$.IsNew") User_IsNew
, JSON_EXTRACT_SCALAR(REPLACE(JSON_QUERY(json, "$.User"), '"', ''), "$.Type") User_Type
, JSON_EXTRACT_SCALAR(REPLACE(JSON_QUERY(json, "$.User"), '"', ''), "$.Test") User_Test

FROM(
SELECT "{'User': \"{'Key': 'abc123', 'IsNew': False, 'Type': 2, 'Test': '123'}\"}" json
)

问题原因 #

1. JSON_EXTRACT_SCALAR仅能提取字符串类型的JSON值，User.Type对应的是整数2，不属于标量字符串范畴，所以直接调用会返回Null；而User.Test是字符串格式的'123'，因此可以正常提取。
2. 你处理User字段时用REPLACE去掉双引号的操作，破坏了JSON的格式规范性，导致BigQuery无法正确识别字段的类型信息。

解决方案 #

方案1：先修复JSON格式再提取

先把原字符串中的单引号替换成双引号，转换成标准JSON格式，再结合类型转换提取值：

SELECT *
, JSON_QUERY(json_fixed, "$.User") User
, JSON_EXTRACT_SCALAR(json_fixed, "$.User.Key") User_Key
, JSON_EXTRACT_SCALAR(json_fixed, "$.User.IsNew") User_IsNew
, CAST(JSON_EXTRACT(json_fixed, "$.User.Type") AS INT64) User_Type
, JSON_EXTRACT_SCALAR(json_fixed, "$.User.Test") User_Test
FROM(
  SELECT 
    -- 替换单引号为双引号，修复转义问题，生成标准JSON
    REPLACE(REPLACE(json, "'", '"'), '\\"', '"') AS json_fixed
  FROM(
    SELECT "{'User': \"{'Key': 'abc123', 'IsNew': False, 'Type': 2, 'Test': '123'}\"}" json
  )
)

方案2：单独解析嵌套的User字段

如果不需要修复整个JSON字符串，可以先把User字段解析为JSON对象，再提取对应值并转换类型：

SELECT *
, User_json
, JSON_EXTRACT_SCALAR(User_json, "$.Key") User_Key
, JSON_EXTRACT_SCALAR(User_json, "$.IsNew") User_IsNew
, CAST(JSON_EXTRACT(User_json, "$.Type") AS INT64) User_Type
, JSON_EXTRACT_SCALAR(User_json, "$.Test") User_Test
FROM(
  SELECT 
    -- 将User字段内容转换为标准JSON对象
    PARSE_JSON(REPLACE(JSON_QUERY(json, "$.User"), "'", '"')) AS User_json
  FROM(
    SELECT "{'User': \"{'Key': 'abc123', 'IsNew': False, 'Type': 2, 'Test': '123'}\"}" json
  )
)

核心要点 #

• 处理JSON时必须保证是标准JSON格式（键值对用双引号包裹），BigQuery的JSON函数仅支持标准语法。
• 非字符串类型的JSON值（数字、布尔值等），需要先用JSON_EXTRACT提取，再通过CAST转换为对应数据类型，不能直接用JSON_EXTRACT_SCALAR。

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