BigQuery で json オブジェクト内の array の処理

BigQuery で json オブジェクト内の array を処理する方法のメモ。
まず、BigQuery のテーブルに以下のような json データを登録します。

insert into `test1.tbl1` (json) values ('''
{"type": "search", "uid": "004", "kw": "フライパン", "items": [
{"rank": 1, "id": "001", "title": "商品1"},
{"rank": 2, "id": "002", "title": "商品2"},
{"rank": 3, "id": "003", "title": "商品3"}
]}
''');

insert into `test1.tbl1` (json) values ('''
{"type": "search", "uid": "004", "kw": "鍋", "items": [
{"rank": 1, "id": "002", "title": "商品2"},
{"rank": 2, "id": "003", "title": "商品3"},
{"rank": 3, "id": "004", "title": "商品4"}
]}
''');

上記のデータから、kw と id を抽出するには以下のクエリを実行します。

select
  json_extract_scalar(t1.json, "$.kw") as kw
  , json_value(item, "$.id") as id
from
  `test1.tbl1` as t1
left join
  unnest(json_query_array(json_extract(t1.json, "$.items"))) as item
where
  json_extract_scalar(t1.json, "$.type") = "search"
;

実行結果は以下の通り、kw と id を抽出できています。

行  kw          id
1   フライパン  001
2   フライパン  002
3   フライパン  003
4   鍋          002
5   鍋          003
6   鍋          004