如何用LIKE运算符在SQL中筛选Google Patents的RECORD类型CPC数据？

问题背景 #

从Google Patents公共数据集的patents-public-data.patents.publications表提取数据时，cpc字段为STRUCT数组类型（包含code、inventive等字段），需要筛选出至少一个CPC代码以C01%开头的记录，但直接访问cpc.code会触发报错：

google.api_core.exceptions.BadRequest: 400 Cannot access field code on a value with type ARRAY<STRUCT<code STRING, inventive BOOL, first BOOL, ...>>

正确SQL写法 #

方法1：使用EXISTS子查询（推荐，性能更优）

通过UNNEST展开数组，检查是否存在符合条件的CPC条目：

SELECT *
FROM `patents-public-data.patents.publications`
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(cpc) AS cpc_entry
  WHERE cpc_entry.code LIKE 'C01%'
)

方法2：使用数组转换匹配

将数组中的code字段拼接成字符串后匹配（适合简单场景，性能略逊于方法1）：

SELECT *
FROM `patents-public-data.patents.publications`
WHERE ARRAY_TO_STRING(ARRAY(SELECT code FROM UNNEST(cpc)), ',') LIKE '%C01%'

报错原因说明 #

cpc是数组类型，无法直接通过cpc.code访问数组内每个STRUCT的字段，必须先通过UNNEST将数组拆分为独立行，才能访问每个STRUCT中的code字段进行条件判断。

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