在BigQuery中展开数组/JSON：根据键获取值

问题根源 #

你的fields字段是JSON数组字符串，而非单个JSON对象，所以直接用$.字段名的路径查询会返回空；UNNEST报错是因为未将字符串类型的JSON数组转换为SQL可识别的数组类型。

方法一：解析数组后UNNEST+条件提取（通用多数SQL引擎） #

先将JSON字符串转为数组，再展开（UNNEST），最后通过CASE语句提取指定name对应的value：

BigQuery 示例 #

SELECT
  id,
  MAX(CASE WHEN item.name = 'Nome da Empresa' THEN item.value END) AS nome_da_empresa,
  MAX(CASE WHEN item.name = 'Email Representante' THEN item.value END) AS email_representante,
  MAX(CASE WHEN item.name = 'Valor total da guia' THEN item.value END) AS valor_total_guia
FROM mytable,
UNNEST(JSON_EXTRACT_ARRAY(fields)) AS item
GROUP BY id

PostgreSQL 示例 #

SELECT
  id,
  MAX(CASE WHEN item->>'name' = 'Nome da Empresa' THEN item->>'value' END) AS nome_da_empresa,
  MAX(CASE WHEN item->>'name' = 'Email Representante' THEN item->>'value' END) AS email_representante
FROM mytable,
jsonb_array_elements(fields::jsonb) AS item
GROUP BY id

MySQL 示例 #

SELECT
  t.id,
  MAX(CASE WHEN j.name = 'Nome da Empresa' THEN j.value END) AS nome_da_empresa,
  MAX(CASE WHEN j.name = 'Email Representante' THEN j.value END) AS email_representante
FROM mytable t,
JSON_TABLE(
  t.fields,
  '$[*]' COLUMNS(
    name VARCHAR(255) PATH '$.name',
    value VARCHAR(255) PATH '$.value'
  )
) j
GROUP BY t.id

方法二：直接用JSON路径过滤提取（部分引擎支持） #

如果你的SQL引擎支持JSON路径过滤（如BigQuery），可以不用UNNEST，直接通过路径定位目标元素：

SELECT
  id,
  -- 提取Nome da Empresa的值
  JSON_EXTRACT_SCALAR(fields, """$[*].value ? (@.name == 'Nome da Empresa')""") AS nome_da_empresa,
  -- 提取Email Representante的值
  JSON_EXTRACT_SCALAR(fields, """$[*].value ? (@.name == 'Email Representante')""") AS email_representante
FROM mytable

你之前查询的错误原因 #

1. 查询1错误：json_extract_scalar(fields,'$.Email Representante')的路径是针对单个JSON对象的，但fields是数组，路径不匹配，所以返回空值。
2. 查询2错误：fields是字符串类型，不是SQL可直接识别的数组类型，必须先通过JSON_EXTRACT_ARRAY（或对应引擎的解析函数）转换为数组后才能UNNEST。

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