[SQL][Google Analytics] NEST されたカラムで複数のキーと値を取得したい

Question

Google Analytics 4 のイベントを BigQuery テーブルに流し込んでいます。
ひとつのキーをもとにデータを取得することはできるんですが、そのキーと同じレコードにある別のキーに格納されている値を取得するにはどうしたらいいのでしょうか？

具体的なイメージで言うと、__記事名でアクセス数ランキングをだして、その補助データとして著者名を別カラムで出したい（著者名は同じレコードの中に別のキーで格納されている）__といったような用途です。

出力したいデータのイメージ

• article_name の閲覧数を集計して cnt を出し、降順に並べる
• どの著者が書いた記事かをわかりやすくするために、author_name カラムを横に出す
• article_name と author_name は同一レコードの同じカラムの中にネストされて入っていて、それを取り出したい

        ↓集計対象       ↓補助データ
+-----+--------------+-------------+-----+
| Row | article_name | author_name | cnt |
+-----+--------------+-------------+-----+
| 1   | My Article A | Author A    | 20  |
| 2   | My Article D | Author A    | 18  |
| 3   | My Article C | Author C    | 11  |
| 4   | My Article B | Author B    | 9   |
  ...
+-----+--------------+-------------+-----+

環境

Google Analytics 4
Google Tag Manager でイベントを設定

このようなテーブルスキーマになっていて、event_params.key の中に article_name author_name などのキーがあり、event_params.value.string_value の中に取得したい値が入っています。

テーブルの中身はこのようになっています。

+-----+------------+-----------------+--------------+------------------+---------------------------------+
| Row | event_date | event_timestamp | event_name   | event_params.key | event_params.value.string_value |
+-----+------------+-----------------+--------------+------------------+---------------------------------+
| 1   | 20201127   | 160394324324231 | view_article | article_name     | My Article A                    |
|     |            |                 |              | author_name      | Author A                        |
|     |            |                 |              | hogehoge key1    | hogehoge value1                 |
|     |            |                 |              | hogehoge key2    | hogehoge value2                 |
| 2   | 20201127   | 160394324324112 | view_article | article_name     | My Article B                    |
|     |            |                 |              | author_name      | Author B                        |
|     |            |                 |              | hogehoge key1    | hogehoge value1                 |
|     |            |                 |              | hogehoge key2    | hogehoge value2                 |
 ...
+-----+------------+-----------------+--------------+------------------+---------------------------------+

試したこと

記事名をもとにしたアクセス数ランキングは出すことができました。

#standardSQL

WITH _data AS (
    SELECT 
        value.string_value AS article_name 
    FROM 
        `my-new-project.analytics_000000000.events_*`, 
        UNNEST(event_params) 
    WHERE 
        key = 'article_name'
        AND event_name = 'view_article'
)

SELECT
    article_name,
    COUNT(*) AS cnt
FROM
    _data
GROUP BY
    1
ORDER BY
    2 DESC

↓結果

+-----+--------------+-----+
| Row | article_name | cnt |
+-----+--------------+-----+
| 1   | My Article A | 20  |
| 2   | My Article D | 18  |
| 3   | My Article C | 11  |
| 4   | My Article B | 9   |
  ...
+-----+--------------+-----+

ここの article_name の横に author_name のカラムを追加したいので、CASE WHEN を使うのがいいのかなと思いました。
しかし、結果としては author_name はすべて null になってしまっているようでした。おそらく別のレコードとして扱われてしまっているのでしょうか。。？

#standardSQL

WITH _data AS (
    SELECT 
        CASE WHEN key = 'article_name' THEN value.string_value
        END AS article_name,
        CASE WHEN key = 'author_name' THEN value.string_value
        END AS author_name
    FROM 
        `my-new-project.analytics_000000000.events_*`, 
        UNNEST(event_params) 
    WHERE 
        event_name = 'view_article'
)

SELECT
    article_name,
    MAX(author_name),
    COUNT(*) AS cnt
FROM
    _data
GROUP BY
    1
ORDER BY
    3 DESC

↓結果

+-----+--------------+-------------+-----+
| Row | article_name | author_name | cnt |
+-----+--------------+-------------+-----+
| 1   | My Article A | null        | 20  |
| 2   | My Article D | null        | 18  |
| 3   | My Article C | null        | 11  |
| 4   | My Article B | null        | 9   |
  ...
+-----+--------------+-------------+-----+

追記：author_name で group by して降順にすると、著者名はちゃんと現れるのですが今度は article_name がすべて null になってしまいました。同じレコードに、article_name と author_name が両立して入っている状態を作れていないのでしょうか・・

---

追記２：各レコードのいずれかのカラムが null になってしまうのは UNNEST してしまってるからなと思い、このようなクエリでただカラムとキーを指定できるかなと試してみました。

SELECT 
    CASE WHEN e.event_params.key = 'article_name' THEN e.event_params.value.string_value
    END AS article_name,
    CASE WHEN e.event_params.key = 'author_name' THEN e.event_params.value.string_value
    END AS author_name
FROM 
    `my-new-project.analytics_000000000.events_*` as e
WHERE 
    event_name = 'view_article'

しかし、BigQuery でこのようなエラーになり、クエリを回せませんでした。

Cannot access field key on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [5:24]

Answer 1

サブクエリの FROM でいきなり UNNEST をつかうこのようなやりかたで解決しました

SELECT 
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'article_name') AS article_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'author_name') AS author_name
FROM 
    `my-new-project.analytics_000000000.events_*`
WHERE 
    event_name = 'view_article'