ジェイソン記念日なのでJSON_TABLEを試してみる

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77441/ddd4c0f3-0662-c46e-15e6-3bd9c2838a45.jpeg" width=50%>

この記事が投稿される日は2024年12月13日の金曜日です。
1年に最低一回は13日の金曜日が訪れますが、12月13日が金曜日なのは稀です。
今回は、そんな日付の話ではなく、JSONをデータベースで扱うために少し勉強したことを書いていきます。

ちなみに、知っていると思うが、無駄に補足しておきます。
 * 映画「13日の金曜日」に登場するのは「ジェイソン(Jason Voorhees)」
 * 関東にあるスーパーは「[ジェーソン](https://jason.co.jp/ir/about_jason/)」
 * 家電量販店は「[ジョーシン](https://www.joshin.co.jp/ja/index.html)」
 * 今回扱うデータはJSON(JavaScript Object Notation)


# 最近のデータベースはJSONが扱える

最近のデータベースではJSONデータを扱えるようになっています。


MySQLは5.7からですね。

https://dev.mysql.com/doc/refman/5.7/en/json.html

Oracleは12.1からですね。

https://docs.oracle.com/cd/E57425_01/121/ADXDB/json.htm

PostgreSQLは12.4からですね。

https://www.postgresql.jp/document/12/html/release-12.html

**最近と言いましたが、結構前からでしたね。**
自分が仕事でJSONデータを使うことが、あまりなく、少しトレンドに乗ることもなかったので、自分自身が最近まで知らなかったというのが正しいでしょうね。

**それではアカン！**
新しい機能などは、仕事で使うことがなくても、試すことはした方が良い！ということで試してみました。

# Qiita APIを叩く

今回、アドベントカレンダーを会社で盛り上げるために、[Qiita API](https://qiita.com/api/v2/docs)を叩いて、投稿されたらSlackに通知するというのを仕込みました。

<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77441/fc1461c3-3048-f046-926b-da322645e077.png width=70%>

Qiita APIの戻り値はJSONなので、これを、そのまま保存して試してみようとしました。


# データベースの詳細

使用したデータベースはPostgreSQL 17.2 です。
テーブルは2つ用意しました。

 * 記事を保存するテーブル「qiita_article」
 * Likeを保存するテーブル「qiita_like」

## qiita_article
|        列         |           タイプ            | 照合順序 | Null 値を許容 | デフォルト
|-------------------|-----------------------------|----------|---------------|------------|
| id                | character varying(30)       |          | not null      |
| title             | character varying(1024)     |          |               |
| user_id           | character varying(256)      |          |               |
| likes_count       | integer                     |          |               |
| aricle_created_at | timestamp without time zone |          |               |
| created_at        | timestamp without time zone |          |               |
| json_data         | jsonb                       |          |               |

インデックス:
    "qiita_article_pkey" PRIMARY KEY, btree (id)

## qiita_like

|        列         |           タイプ            | 照合順序 | Null 値を許容 | デフォルト|
|-------------------|-----------------------------|----------|---------------|------------|
| id                | character varying(30)       |          | not null      |
| permanent_id      | integer                     |          | not null      |
| user_id           | character varying(256)      |          |               |
| aricle_created_at | timestamp without time zone |          |               |
| created_at        | timestamp without time zone |          |               |
| json_data         | jsonb                       |          |               |

# JSONを検索してみよう！

9.15. JSON関数と演算子を参考に2つの検索方法を試してみます。

 1. 「->>」JSON オブジェクトフィールドをtextとして取得する
 1. 「@>」左のJSON値はその中に右の値を包含する

https://www.postgresql.jp/docs/9.4/functions-json.html

この演算子を用いて、自分がいいねをした日時をJSONデータから引っ張り出すSQL作成します。

JSON オブジェクトフィールドをtextとして検索するはこちらです。
```SQL
SELECT json_data->'created_at' FROM qiita_like WHERE (json_data->'user'->>'id') = 'sapi_kawahara';
```


左のJSON値はその中に右の値を包含する方法で検索するはこちらです。
```SQL
SELECT json_data->'created_at' FROM qiita_like WHERE json_data @> '{ "user": { "id": "sapi_kawahara" }}'::jsonb;
```

どちらも、こんな結果が出ます。

```
          ?column?
-----------------------------
 "2023-12-25T07:40:08+09:00"
 "2023-12-25T07:39:50+09:00"
 "2023-12-24T08:08:24+09:00"
 "2023-12-24T08:08:33+09:00"
 "2023-12-23T09:36:11+09:00"
 "2023-12-23T18:08:08+09:00"
 "2023-12-22T08:50:20+09:00"
 "2023-12-20T09:36:29+09:00"
 "2023-12-19T10:13:18+09:00"
 "2023-12-21T09:02:57+09:00"
 "2023-12-19T10:13:32+09:00"
 ```

# パフォーマンスも見てみたい

検索できたら次はパフォーマンスを見たくなりますね。
計測するためにEXPLAINを実行してみましょう。

「->>」JSON オブジェクトフィールドをtextとして取得する計測はこちらです。

```SQL
EXPLAIN ANALYZE SELECT json_data->'created_at' FROM qiita_like WHERE (json_data->'user'->>'id') = 'sapi_kawahara';
```

結果

```TEXT
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on qiita_like  (cost=0.00..569.70 rows=28 width=32) (actual time=0.079..3.438 rows=72 loops=1)
   Filter: (((json_data -> 'user'::text) ->> 'id'::text) = 'sapi_kawahara'::text)
   Rows Removed by Filter: 5614
 Planning Time: 0.050 ms
 Execution Time: 3.456 ms
(5 行)
```


「@>」左のJSON値はその中に右の値を包含する方法での計測はこちです。

```SQL
EXPLAIN ANALYZE SELECT json_data->'created_at' FROM qiita_like WHERE json_data @> '{ "user": { "id": "sapi_kawahara" }}'::JSONB;
```

結果

```TEXT
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on qiita_like  (cost=0.00..541.31 rows=57 width=32) (actual time=0.077..5.028 rows=72 loops=1)
   Filter: (json_data @> '{"user": {"id": "sapi_kawahara"}}'::jsonb)
   Rows Removed by Filter: 5614
 Planning Time: 0.143 ms
 Execution Time: 5.046 ms
(5 行)
```

3秒から5秒かかるんですね。
早くはないですね。

参考までにindexなしの通常データでも計測してみました。

```SQL
EXPLAIN ANALYZE SELECT aricle_created_at FROM qiita_like WHERE user_id = 'sapi_kawahara';
```


```TEXT
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on qiita_like  (cost=0.00..541.16 rows=73 width=8) (actual time=0.047..1.507 rows=72 loops=1)
   Filter: ((user_id)::text = 'sapi_kawahara'::text)
   Rows Removed by Filter: 5614
 Planning Time: 0.056 ms
 Execution Time: 1.523 ms
(5 行)
```

2秒弱なので、indexなしのJSONの検索は、indexなしの通常データより遅いということが判別できました。

# indexは作れないの？

作れるみたい。

https://www.postgresql.jp/docs/9.4/datatype-json.html#JSON-INDEXING

作ってみます。

```SQL
CREATE INDEX idx_gin_qiita_like ON qiita_like USING GIN (json_data);
```

ちなみに通常indexも作っておきます。

```SQL
CREATE INDEX idx_qiita_like ON qiita_like (user_id);
```

なお、「->>」JSON オブジェクトフィールドをtextとして取得する方のSQLは、indexが作成できないので計測はしません。
「@>」左のJSON値はその中に右の値を包含する方法はindexが効くので計測してみます。

```SQL
EXPLAIN ANALYZE SELECT json_data->'created_at' FROM qiita_like WHERE json_data @> '{ "user": { "id": "sapi_kawahara" }}'::JSONB;
```

結果

```TEXT
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on qiita_like  (cost=30.52..192.46 rows=57 width=32) (actual time=0.150..0.268 rows=72 loops=1)
   Recheck Cond: (json_data @> '{"user": {"id": "sapi_kawahara"}}'::jsonb)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_gin_qiita_like  (cost=0.00..30.50 rows=57 width=0) (actual time=0.127..0.127 rows=72 loops=1)
         Index Cond: (json_data @> '{"user": {"id": "sapi_kawahara"}}'::jsonb)
 Planning Time: 0.251 ms
 Execution Time: 0.291 ms
(7 行)
```

お？実行ルールが変わった！indexを見るようになった！そして早くなった！

次に通常の検索も計測してみます。


```SQL
EXPLAIN ANALYZE SELECT aricle_created_at FROM qiita_like WHERE user_id = 'sapi_kawahara';
```


```TEXT
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on qiita_like  (cost=4.85..200.17 rows=73 width=8) (actual time=0.088..0.366 rows=72 loops=1)
   Recheck Cond: ((user_id)::text = 'sapi_kawahara'::text)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_qiita_like  (cost=0.00..4.83 rows=73 width=0) (actual time=0.058..0.059 rows=72 loops=1)
         Index Cond: ((user_id)::text = 'sapi_kawahara'::text)
 Planning Time: 0.111 ms
 Execution Time: 0.395 ms
(7 行)
```

これも当然のように、実行ルールが変わってindexを見るようになって早くなった！


# まとめ

今回はリレーションなどは加味してないけど、単一データベースならJSONを使ったデータベースも悪くないと感じました。
用途さえはっきりしていれば、こういうのもアリかな？

それと、誤差もあると思うけど、今回の結果は下のようになり、indexを付けたJSONの包含する方法の検索が健闘しました。


|順位|index|検索方法                |実行時間                 |
|---|-----|-----------------------|------------------------|
|1  |◯    |JSONを包含（@>）         |Execution Time: 0.291 ms|
|2  |◯    |通常の検索               |Execution Time: 0.395 ms|
|3  |✕    |通常の検索　             |Execution Time: 1.523 ms|
|4  |✕    |JSONのフィールドを抽出(->)|Execution Time: 3.456 ms|
|5  |✕    |JSONを包含（@>)         |Execution Time: 5.046 ms|


最後に、検索にjsonb演算子を使うのは楽しかった。
今までのSQLの知識と異なるので新たな勉強になった。
ときどき、新しいことを勉強するのも良いですね。