最近のWEBシステムのAPIを設計・実装していると、JSONを扱う機会が増えてきた気がします。
JSONをデータベースで効率よく管理したいと思い以下の内容をまとめました。
- JSONデータ型の種類とその説明
- JSONデータ型のテーブル作成方法
- JSONをインサートする
これを読めばPostgreSQLで基本的なJSONの操作ができるようになると思います。
JSONとは
{ }で囲い、{“Key” : “値”} のような形式のテキストになります。
以下サンプルです。
{ "name": "田中", "age": 29 }name は田中、ageは29と読み解くことができます。
JSONデータ型の種類
JSONデータ型は json型とjsonb型 の二種類あります。
json型
jsonデータ型は入力値の正確なコピーで格納します。
つまり、JSONテキストに無意味な空白などあった場合でもそのままDBに保存するということですね。
JSONを処理する際にいちいち解析処理をし直さないといけないため、読み取り時の性能はあまりよくないと言われています。
jsonb型
jsonbデータ型では、入力値を分解し解析処理を行ってから格納します。
データを取り出す際に解析処理を行わずに済むため性能効率が上がります。 ただ、初回の登録時は解析してから登録するため少し時間がかかります。
また jsonb型はインデックスをサポートしています。これはアプリケーションにとって大きなメリットですね.
使い分け
これは私の見解ですが、JSONをDBに登録のみする場合（電文内容をログとして登録する場合など）はjson型で、アプリケーションから参照など行う場合はjsonb型になるのかなと思います。
迷ったらjsonb型でいいと思います。
JSONデータ型のテーブル作成
実際にJSONデータ型のカラムをもったテーブルを作成していきたいと思います。
json型、jsonb型のカラム作成
json型とjsonb型のカラムを保持したテーブルを作成したいと思います。
列 | 型 | Null 値を許容 | デフォルト
------------+---------+---------------+-----------------------------------------
no | integer | not null | nextval('json_sample_no_seq'::regclass)
json_data | json | |
jsonb_data | jsonb | |
インデックス:
"json_sample_pkey" PRIMARY KEY, btree (no)
コマンドは以下の通りです。
CREATE TABLE json_sample( no serial not null, json_data json, jsonb_data jsonb, primary key(no) );
json型はjsonと宣言、jsonb型はjsonbと宣言するだけですね。
これでjson型の定義のjson_date、jsonb型の定義のjsonb_dataのカラムを持ったテーブルjson_sampleの作成ができました。
JSONデータの登録
では実際に作成したカラムにデータを登録していきましょう。
JSONのINSERT文
実際にJSONテキストをインサート文に記載して登録します。
以下のように記述すれば登録可能です。
INSERT INTO json_sample(
json_data,
jsonb_data
)VALUES(
'{"name" : "田中", "age" : "29"}',
'{"name" : "田中", "age" : "29"}'
);json_build_object を使用したINSERT文
json_build_objectはJSONオブジェクトに変換してくれる関数で非常に便利ですので、ぜひ覚えておいてください。
INSERT INTO json_sample(
json_data,
jsonb_data
)VALUES(
json_build_object('name', '田中', 'age', '29'),
json_build_object('name', '田中', 'age', '29')
);json_build_objectを使用しても、JSONテキストをそのまま記載しても結果は同じですのでどちらを使用してもかまいません。
登録されたデータを確認する
さきほど登録したデータをSELECT文で参照してみましょう。
db01=# select * from json_sample;
no | json_data | jsonb_data
----+---------------------------------+-------------------------------
3 | {"name" : "田中", "age" : "29"} | {"age": "29", "name": "田中"}しっかりJSONデータが格納されてますよね。
json_dataとjsonb_dateでキー順が逆になっているのに気づきましたか？
これは最初のjsob型の説明で述べましたが「入力値を分解し解析処理を行ってから格納する」という性質があるため、記述順まで保持しないんですよね。
以上です。これからJSONを扱う方は是非試しに構築してみてください。
【参考にしたページ】
・PostgreSQL 10.5文書
https://www.postgresql.jp/document/10/html/datatype-json.html
・Json演算子
https://www.postgresql.jp/document/9.4/html/functions-json.html
【関連ページ】
・PostgreSQLのダウンロードとインストール
https://yu-report.com/entry/postgreSQL
・PostgreSQLのデータベース作成とテーブル作成
https://yu-report.com/entry/postgresqlMakeTbl
・PostgreSQLの基本コマンド
https://yu-report.com/entry/postgresqlCommand
コメント