JSON_EXTRACT_ARRAY

4724 ワード
BigQuery BigQuery テキストリンク

JSON_EXTRACT_ARRAY

便利なやつが現れた

例1

SELECT
  JSON_EXTRACT_ARRAY("[{'a': 1, 'b': 2}, {'a': 3, 'b': 4}]")
行 f0_
1 {"a":1,"b":2}
{"a":3,"b":4}

ざっくりいうと、JSONの中の配列を扱えるようになった。
ただ、これだけだとイマイチぱっとしない。

例2

WITH data AS (
  SELECT 
    *
  FROM
    UNNEST([
        STRUCT(1 AS user_id, "[{'a': 1, 'b': 2}, {'a': 3, 'b': 4}]" AS json_data),
        (2, "[{'a': 5, 'b': 6}, {'a': 7, 'b': 8}, {'a': 9, 'b': 10}]")
    ])
)

SELECT
  *
FROM
  data
行 user_id json_data
1 1 [{'a': 1, 'b': 2}, {'a': 3, 'b': 4}]
2 2 [{'a': 5, 'b': 6}, {'a': 7, 'b': 8}, {'a': 9, 'b': 10}]

こんなデータがあったとして、それぞれのjson_dataのbの値を抜き出したい場合にこういった書き方ができる

SELECT
  user_id,
  JSON_EXTRACT(ele, '$.b') AS b_value
FROM
  data,
  UNNEST(JSON_EXTRACT_ARRAY(json_data)) AS ele
行 user_id b_value
1 1 2
2 1 4
3 2 6
4 2 8
5 2 10

こんな感じでJSON_EXTRACT_ARRAYとUNNESTとJSON_EXTRACT(JSON_EXTRACT_SCALAR)を使うことで恐らくどんな形のJSONデータであろうが中身を引っ張ってこれるようになった。

応用次第でいろんな事ができる（気がする）

SELECT
  SUM(CAST(JSON_EXTRACT(ele, '$.a') AS INT64)) AS a_sum,
  SUM(CAST(JSON_EXTRACT(ele, '$.b') AS INT64)) AS b_sum
FROM
  data,
  UNNEST(JSON_EXTRACT_ARRAY(json_data)) AS ele
行 a_sum b_sum
1 25 30
iPhone開発のObjective-C学習(1)Objective-Cにおける@classと#importの問題