如何在BigQuery中用SQL提取JSON数组结构体的指定键值

问题场景 #

运行查询得到结果结构如下：

[{
  "polarity": "0.0",
  "magnitude": "2.0",
  "score": "0.5",
  "entities": [{
    "name": "Taubenkot",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.150263"
  }, ...（其余实体省略）]
}]

其中entities字段为ARRAY<STRUCT>类型，尝试提取type字段时出现以下报错：

错误写法1：使用JSON_VALUE #

select JSON_VALUE(entities, '$.type') AS type from gcnlapi limit 1

报错信息：

No matching signature for function JSON_VALUE for argument types: ARRAY<STRUCT<name STRING, type STRING, mid STRING, ...>>, STRING. Supported signatures: JSON_VALUE(STRING, [STRING]); JSON_VALUE(JSON, [STRING]) at [3:8]

错误写法2：直接访问字段 #

select entities.type AS type from gcnlapi limit 1

报错信息：

Cannot access field type on a value with type ARRAY<STRUCT<name STRING, type STRING, mid STRING, ...>> at [5:17]

正确解法 #

由于entities是数组类型，不能直接访问内部结构体字段，需通过以下方式处理：

1. 展开数组，获取每个实体的type（多行输出） #

使用UNNEST函数将数组展开为单行实体，再访问type字段：

select entity.type AS type
from gcnlapi,
unnest(entities) as entity
limit 10

此写法会将每个实体的type单独输出一行，原数据中的每个实体对应一条结果记录。

2. 保留数组结构，提取所有type组成新数组 #

若需将所有实体的type保留为数组形式，可使用ARRAY构造函数结合UNNEST：

select array(select type from unnest(entities)) as entity_types
from gcnlapi
limit 1

输出的entity_types字段为字符串数组，包含原entities中所有实体的type值。

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