Go to Qiita Advent Calendar 2024 Top
LoginSignup
45
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Systemi（株式会社システムアイ）Advent Calendar 2024

Day 13
@sapi_kawahara(カワハラ)in株式会社システムアイ

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

Last updated at Posted at 2024-12-12

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

ちなみに、知っていると思うが、無駄に補足しておきます。

  • 映画「13日の金曜日」に登場するのは「ジェイソン(Jason Voorhees)」
  • 関東にあるスーパーは「ジェーソン」
  • 家電量販店は「ジョーシン」
  • 今回扱うデータはJSON(JavaScript Object Notation)

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

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

MySQLは5.7からですね。

Oracleは12.1からですね。

PostgreSQLは12.4からですね。

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

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

Qiita APIを叩く

今回、アドベントカレンダーを会社で盛り上げるために、Qiita APIを叩いて、投稿されたらSlackに通知するというのを仕込みました。

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として取得する
  2. 「@>」左のJSON値はその中に右の値を包含する

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

JSON オブジェクトフィールドをtextとして検索するはこちらです。

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

左のJSON値はその中に右の値を包含する方法で検索するはこちらです。

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として取得する計測はこちらです。

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

結果

                                               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値はその中に右の値を包含する方法での計測はこちです。

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

結果

                                               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なしの通常データでも計測してみました。

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

                                               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は作れないの？

作れるみたい。

作ってみます。

CREATE INDEX idx_gin_qiita_like ON qiita_like USING GIN (json_data);

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

CREATE INDEX idx_qiita_like ON qiita_like (user_id);

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

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

結果

                                                          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を見るようになった！そして早くなった！

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

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

                                                       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の知識と異なるので新たな勉強になった。
ときどき、新しいことを勉強するのも良いですね。

45
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
45
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?