如何将BigQuery表中JSON字符串列转换为结构化新表？

核心思路 #

利用BigQuery内置的JSON解析函数，将raw_output列中的序列化JSON对象拆分为独立字段，再通过CREATE TABLE AS语句生成结构化新表，满足JOIN等后续操作需求。

方案一：逐字段提取（适合字段较少的场景） #

先通过查询验证解析结果，再创建新表：

验证查询

SELECT
  JSON_EXTRACT_SCALAR(raw_output, '$.client') AS client,
  JSON_EXTRACT_SCALAR(raw_output, '$.c_integration') AS c_integration,
  JSON_EXTRACT(raw_output, '$.idntf') AS idntf,
  CAST(JSON_EXTRACT_SCALAR(raw_output, '$.nf_p') AS FLOAT64) AS nf_p
FROM `your-project.your-dataset.your-raw-table`

创建新表语句

CREATE OR REPLACE TABLE `your-project.your-dataset.your-new-table`
AS
SELECT
  JSON_EXTRACT_SCALAR(raw_output, '$.client') AS client,
  JSON_EXTRACT_SCALAR(raw_output, '$.c_integration') AS c_integration,
  JSON_EXTRACT(raw_output, '$.idntf') AS idntf,
  CAST(JSON_EXTRACT_SCALAR(raw_output, '$.nf_p') AS FLOAT64) AS nf_p
FROM `your-project.your-dataset.your-raw-table`

函数说明：

• JSON_EXTRACT_SCALAR：提取JSON中的字符串类型值，适配client、c_integration这类字段
• JSON_EXTRACT：直接保留布尔类型的原始值，避免字符串转译
• CAST(...) AS FLOAT64：将nf_p的字符串值转换为浮点型，支持后续数值运算

方案二：批量解析（适合字段较多的场景） #

先将整个JSON列解析为STRUCT对象，再直接提取字段，写法更简洁：

创建新表语句

CREATE OR REPLACE TABLE `your-project.your-dataset.your-new-table`
AS
SELECT
  parsed_json.client,
  parsed_json.c_integration,
  parsed_json.idntf,
  parsed_json.nf_p
FROM (
  SELECT PARSE_JSON(raw_output) AS parsed_json
  FROM `your-project.your-dataset.your-raw-table`
)

注意事项：

• 替换语句中your-project.your-dataset.your-raw-table和your-project.your-dataset.your-new-table为实际的项目、数据集及表名
• 若JSON字段存在缺失值，可通过IFNULL函数处理，例如IFNULL(parsed_json.client, 'N/A') AS client

内容的提问来源于stack exchange，提问作者Caio César P. Ricciuti