JSON_EXTRACT_ARRAY

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