【PostgreSQL】JSON データ絞り込み入門 / Introduction to Filtering JSON Data in PostgreSQL

めぐろLT #24 「データベース」で LT した際に使用した発表スライドです。
https://meguro-lt.connpass.com/event/343511/

Transcript

1. 【PostgreSQL】 JSON データ絞り込み入門 〜JSON と JSONB の違い＆便利な演算子をマスターしよう〜 2025/02/17 #めぐろLT 株式会社HRBrain 清水智貴
2. 2 AGENDA • 自己紹介 • 概要・背景 • JSON vs JSONB • クエリの具体例 • 演算子まとめ • まとめ
3. 自己紹介
4. 4 自己紹介 • 清水 智貴 • 人事評価サービスの開発 • バックエンドエンジニア • 東京都 出身 • 最近の趣味 ◦ Podcast聴く ◦ 散歩 • 今回が初のLT！
5. 概要・背景
6. 6 何を伝えたいか • JSON データでも SQL で絞り込み可能 ◦ PostgreSQL に JSON で保存されていても、演算子を使えば柔軟な検索ができる ◦ 具体例で紹介します
7. 7 JSON データの絞り込み方法を調べた背景 • 廃止された機能のデータが JSON に潜んでいる…？ ◦ 過去に開発された機能が廃止され、DB にそのレコードが残っているかもしれない ◦ JSON カラム内に保存されていて、リレーショナルなカラムではない ◦ PostgreSQL での JSON の検索方法を調べることに！
8. 8 JSON データの絞り込み方法を調べた背景 • 廃止された機能のデータが JSON に潜んでいる…？ ◦ 過去に開発された機能が廃止され、DB にそのレコードが残っているかもしれない ◦ JSON カラム内に保存されていて、リレーショナルなカラムではない ◦ PostgreSQL での JSON の検索方法を調べることに！ • この発表で言及しないこと ◦ 設計論（正規化すべきか等）は対象外 ◦ 今回は 「既に JSON で保存されている」 ことが前提 ◦ JSON でデータ保存することの是非や、 正規化との比較は別途検討が必要だが、今回は扱わない
9. JSON と JSONB
10. 10 JSON vs JSONB • PostgreSQL の JSON には2種類の型がある • JSON型 ◦ テキストをそのまま保持するため、空白や改行、キーの順序などを厳密に保ちたい場合に有用 ◦ クエリ内での検索には不向き • JSONB型 ◦ バイナリ形式で保持するため、検索・集計・更新が高速 ◦ 演算子が豊富でインデックスも可能 • 使い分け ◦ JSON: 保管用途やオリジナルのテキストを保ちたいデータ ◦ JSONB: 頻繁に検索や更新を行いたいデータ
11. 11 JSON vs JSONB • PostgreSQL の JSON には2種類の型がある • JSON型 ◦ テキストをそのまま保持するため、空白や改行、キーの順序などを厳密に保ちたい場合に有用 ◦ クエリ内での検索には不向き • JSONB型 ◦ バイナリ形式で保持するため、検索・集計・更新が高速 ◦ 演算子が豊富でインデックスも可能 • 使い分け ◦ JSON: 保管用途やオリジナルのテキストを保ちたいデータ ◦ JSONB: 頻繁に検索や更新を行いたいデータ 今日は JSONB 型を用いて具体例を紹介します！
12. クエリの具体例
13. 13 クエリの具体例 • テーブル構造 • data カラムに、ユーザー情報が JSON 形式で保存されている ◦ name ◦ age ◦ skills ◦ location CREATE TABLE sample_table ( id SERIAL PRIMARY KEY, data JSONB ); INSERT INTO sample_table (data) VALUES ('{"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}}'), ('{"name": "Bob", "age": 30, "skills": ["Java", "JavaScript"], "location": {"city": "Osaka"}}'), ('{"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}}');
14. 14 例1）特定のキーの値を条件に絞り込む • JSONデータ • name が "Alice" のレコードを取得 ◦ ->>: JSONB から指定したキーの値を文字列として取得する演算子 • 結果 INSERT INTO sample_table (data) VALUES ('{"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}}'), ('{"name": "Bob", "age": 30, "skills": ["Java", "JavaScript"], "location": {"city": "Osaka"}}'), ('{"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}}'); SELECT * FROM sample_table WHERE data ->> 'name' = 'Alice'; id data 1 {"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}}
15. 15 例2）数値の条件で絞り込む • JSONデータ • age が 30 より大きいレコードを取得 ◦ (data ->> 'age')::int: JSONB から取得した値を数値型にキャスト • 結果 INSERT INTO sample_table (data) VALUES ('{"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}}'), ('{"name": "Bob", "age": 30, "skills": ["Java", "JavaScript"], "location": {"city": "Osaka"}}'), ('{"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}}'); SELECT * FROM sample_table WHERE (data ->> 'age')::int > 30; id data 3 {"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}}
16. 16 例3）配列の要素を条件に絞り込む • JSONデータ • skills 配列に "Python" が含まれるレコードを取得 ◦ ?: JSONB 配列に特定の値が含まれているかを判定する演算子 • 結果 INSERT INTO sample_table (data) VALUES ('{"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}}'), ('{"name": "Bob", "age": 30, "skills": ["Java", "JavaScript"], "location": {"city": "Osaka"}}'), ('{"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}}'); SELECT * FROM sample_table WHERE data -> 'skills' ? 'Python'; id data 1 {"name": "Alice", "age": 25, "skills": ["Python", "SQL"], "location": {"city": "Tokyo"}} 3 {"name": "Charlie", "age": 35, "skills": ["Python", "Go"], "location": {"city": "Fukuoka"}}
17. 演算子まとめ
18. 18 JSON/JSONBの主な演算子 • 共通で使用可能な演算子 ◦ ->: JSON データから指定したキーの値を JSON 形式で取得 ◦ ->>: JSON データから指定したキーの値を文字列として取得 ◦ #>: ネストされた JSON データから特定のキーを指定して値を JSON 形式で取得 ◦ #>>: ネストされた JSON データから特定のキーを指定して値を文字列形式で取得 • JSONB のみで使用可能な演算子 ◦ ?: JSONB オブジェクトや配列に指定したキーや値が存在するかを判定 ◦ @>: JSONB データが特定の構造を含むかどうかを判定 ◦ ?|: JSONB 配列内のいずれかの要素が指定した条件を満たすかを判定 ◦ &>: JSONB 配列が指定したすべての要素を含むかを判定
19. 19 JSONB データのキャスト • 数値型へのキャスト ◦ ::integer: 整数型へのキャスト。小数点以下は切り捨て。 ◦ ::numeric: 任意精度の数値型へのキャスト。小数点以下の桁数を保持。 ◦ ::int: integer の別名。同じ動作。 • 日付型へのキャスト ◦ ::date: 日付のみを扱う型へのキャスト。例: '2024-01-21' ◦ ::timestamp: 日付と時刻を扱う型へのキャスト。例: '2024-01-21 13:45:30' ◦ ::timestamptz: タイムゾーン付きの日付と時刻を扱う型へのキャスト。例: '2024-01-21 13:45:30+09:00' • 真偽値へのキャスト ◦ ::boolean
20. まとめ
21. • 何を伝えたかったか ◦ JSON に入っているデータでも、PostgreSQL なら SQL で簡単に絞り込みできる • 背景 ◦ 廃止された機能が DB の JSON に残っていないか確認が必要だったから • 内容 ◦ JSON 型と JSONB 型の違い ◦ クエリの具体例 ◦ 演算子 • 感想 ◦ JSONB を使うとスキーマ柔軟＆複雑なクエリが可能 ◦ 問い合わせ対応での本番DB調査などに活躍するかも 21 まとめ
22. • PostgreSQL Documentation - JSON Types • PostgreSQL Documentation - Index Types • 【PostgreSQL】JSON データを SQL クエリで絞り込みする方法 ◦ 私が投稿したQiita記事にもう少し詳細に書いています。 22 参考文献
23. ご清聴ありがとうございました！