如何在BigQuery中使用通配符搜索嵌套JSON数据？

两种匹配方式及示例 #

1. 全局通配符匹配（匹配JSON任意字段内容）

如果不需要指定具体字段，直接在search()函数的搜索字符串中使用%通配符即可，这种写法能利用已创建的搜索索引，匹配所有包含以指定前缀开头内容的JSON记录。

示例代码：

with x as (
    select to_json('{"roleArn":"arn:iam::6789","roleSessionName":"abc123"}') as logrec
    union all
    select to_json('{"roleArn":"arn:iam::5678","roleSessionName":"abc124"}') as logrec
)
select * from x where search(logrec,'abc12%');

2. 精准匹配指定JSON字段

若要限定搜索JSON中的特定字段（比如示例中的roleSessionName），可以使用字段名:通配符字符串的语法，这种方式能更精准地利用索引，避免匹配无关字段的内容。

示例代码：

with x as (
    select to_json('{"roleArn":"arn:iam::6789","roleSessionName":"abc123"}') as logrec
    union all
    select to_json('{"roleArn":"arn:iam::5678","roleSessionName":"abc124"}') as logrec
    union all
    select to_json('{"roleArn":"arn:iam::abc125","roleSessionName":"xyz789"}') as logrec
)
select * from x where search(logrec,'roleSessionName:abc12%');

关键注意事项 #

• 确认logrec列已正确创建搜索索引，否则search()函数可能无法发挥索引优势，转为全表扫描。
• %用于匹配任意长度的字符（包括零个），若需匹配单个任意字符，可使用_通配符。

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