Presto で JSON を Parse して構造を作り変える関数のアラカルト

### はじめに

私が所属する会社ではビックデータのプラットフォームに [Treasure Data](https://www.treasuredata.co.jp/) を利用しているのですが、最近そこそこ楽しい JSON Parse の Presto Query を書きました。

https://docs.treasuredata.com/categories/presto

単に Parse するだけで無く、要件を満たすために構造を作り変える必要があったので Presto の普及活動も兼ねて、利用した関数などを紹介したいと思います。

### WITH

`WITH` は入れ子になったクエリを平坦化したり、サブクエリを単純化できます。また、 `WITH` 内ではクエリの結果をチェインすることができます。

##### Sample Query

```sql
WITH
  t1 AS(SELECT 1, '太郎'),
  t2 AS(SELECT * ,'29才' FROM t1),
  t3 AS(SELECT * ,'エンジニア' FROM t2)
SELECT * FROM t3
```

##### Query Result

|id|name|old|job|
|:--|:--|:--|:--|
|1|太郎|29才|エンジニア|



### JSON_EXTRACT

`JSON_EXTRACT` はJSONのカラムから特定のフィールド抽出する関数です。
第二引数に指定した [JSONPath](https://goessner.net/articles/JsonPath/) を評価して結果を JSON で返します。

##### Sample Query

```sql
SELECT 
  CAST(
    JSON_EXTRACT(
      raw_json,
      '$.field_1'
    ) AS VARCHAR
  )  AS field_1,
  CAST(
    JSON_EXTRACT(
      raw_json,
      '$.field_2'
    ) AS INTEGER
  ) AS field_2
FROM
  (
  VALUES JSON_PARSE ('
      { "field_1": "value_1",
        "field_2": 1
      }') ,
  JSON_PARSE ('
      { "field_1": "value_2",
        "field_2": 2
      }') ,
  JSON_PARSE ('
      { "field_1": "value_3",
        "field_2": 3
      }')
  ) AS t(raw_json)
```
##### Query Result

|field_1|field_2|
|:--|:--|
|"value_1"|1|
|"value_2"|2|
|"value_3"|3|


### CAST

ご存知かとは思いますが特定のデータ型を別のデータ型へ変換する関数です。
JSONのオブジェクト型を `MAP` に変換できるのが便利でした。

##### Sample Query

```sql
SELECT 
   CAST(
      raw_json AS MAP < VARCHAR,
      INTEGER >
    )
FROM
  (
  VALUES JSON_PARSE('
      { "key_1": 1,
        "key_2": 2,
        "key_3": 3
      }')
  ) AS t(raw_json)
```

##### Query Result

|i_am_map|
|:--|
|{"key_3":3,"key_2":2,"key_1":1}|

### MAP_FILTER

`MAP` に対し条件を指定し、該当する要素のみにフィルターすることができます。

##### Sample Query

```sql
SELECT 
  MAP_FILTER(
    m, 
    (key, value) -> key <> 'should_not_select'
  ) AS filtered_map
FROM
  (
  VALUES CAST(JSON_PARSE('
      { "key_1": 1,
        "key_2": 2,
        "should_not_select": null
      }') AS MAP < VARCHAR,
      INTEGER >
    )
  ) AS t(m)
```

##### Query Result

|filtered_map|
|:--|
|{"key_2":2,"key_1":1}|

### CROSS JOIN UNNET

`CROSS JOIN UNNET` は `ARRAY` や `MAP` のフィールドをレコードに展開することができる関数 です。

#### Case of ARRAY

##### Sample Query

```sql
SELECT 
   record
FROM
  (
  VALUES CAST(JSON '[
      { "record_1": "a" },
      { "record_2": "b" },
      { "record_3": "c" }
    ]' AS ARRAY(JSON))
  ) AS t(json_array)
CROSS
JOIN
  UNNEST(json_array) AS t(record)
```

##### Query Result

|record|
|:--|
|{"record_1":"a"}|
|{"record_2":"b"}|
|{"record_3":"c"}|

#### Case of MAP

##### Sample Query

```sql
SELECT 
  KEY,
  VALUE
FROM (
  VALUES JSON_PARSE('
      { "key_1": 1,
        "key_2": 2,
        "key_3": 3
      }')
  ) AS t(m) CROSS
JOIN
  UNNEST(
    CAST(
      m AS MAP < VARCHAR,
      INTEGER >
    )
  ) AS m(
    KEY,
    VALUE
  )
```

##### Query Result

|KEY|VALUE|
|:--|:--|
|key_1|1|
|key_2|2|
|key_3|3|

### Window関数

抜粋です。

> Window関数はテーブルを区間ごとに集計する機能です。集約関数 (GROUP BY) に似ていますが、Window関数では複数の行がまとめられることはなく行それぞれが返却されます。また、処理中の行以外の行の値を読み取ることも可能です。Window関数は以下の構文を使います。

+ [Window 関数を使いこなす（集約関数系）](https://support.treasuredata.com/hc/ja/articles/216392117-Window-%E9%96%A2%E6%95%B0%E3%82%92%E4%BD%BF%E3%81%84%E3%81%93%E3%81%AA%E3%81%99-%E9%9B%86%E7%B4%84%E9%96%A2%E6%95%B0%E7%B3%BB-)
+ [Hive - Window 関数](https://support.treasuredata.com/hc/ja/articles/215724857-Window-%E9%96%A2%E6%95%B0)

`PARTITION` を指定し区間を定め、集計関数(MIN,MAX)と組み合わせて結果を取得します。

##### Sample Query

下のクエリでは `PARTITION` に `YEAR` を指定し年毎の区間を定めています。現在行に対し1レコード前の日付を `yesterday` として現在行へのカラムとして反映しています。過去日付の行が存在しない場合は現在行の値がとして反映されます。

```sql
SELECT 
   * ,
  MIN(today) OVER(
    PARTITION BY YEAR
  ORDER BY
    today ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS yesterday
FROM (
  VALUES
    ('2017', CAST('2017-01-01' AS DATE)),
    ('2017', CAST('2017-01-02' AS DATE)),
    ('2017', CAST('2017-01-03' AS DATE)),
    ('2018', CAST('2018-01-01' AS DATE)),
    ('2018', CAST('2018-01-02' AS DATE)),
    ('2018', CAST('2018-01-03' AS DATE)),
    ('2019', CAST('2019-01-01' AS DATE)),
    ('2019', CAST('2019-01-02' AS DATE)),
    ('2019', CAST('2019-01-03' AS DATE))
  ) AS t(YEAR, today)
ORDER BY
  today
```

###### Query Result

|YEAR|TODAY|YESTERDAY|
|:--|:--|:--|
|2017|2017-01-01|2017-01-01|
|2017|2017-01-02|2017-01-01|
|2017|2017-01-03|2017-01-02|
|2018|2018-01-01|2018-01-01|
|2018|2018-01-02|2018-01-01|
|2018|2018-01-03|2018-01-02|
|2019|2019-01-01|2019-01-01|
|2019|2019-01-02|2019-01-01|
|2019|2019-01-03|2019-01-02|

上記は単純な例ですが、 `PARTITION` の組み合わせによって様々な単位での区間を組み立てることができます。

### 終わりに

想像以上に柔軟にやりたいことができるなーという印象でした。特にWindow関数は初めて触ったのですが楽しかったです。データ分析系の Query 書くなら必須の技術だと思います。

### おわり