PostgreSQL JSONB

今回はJSONBをやります。JSONB型はJSONをバイナリ形式で格納するので、格納時はJSONより重いですが、取得時は大幅に早くなります。
JSONB型ならインデックスも付けられます。

PostgreSQLのver9.3でJSONに対応、ver9.4でJSONBに対応、ver9.5で部分更新に対応します。このページをすべて試すには
ver9.5に上げる必要があります。バージョンを確認するには以下のSQLを実行して下さい。

				
SELECT version();

JSONとは

JSONはJavaScriptのオブジェクトをテキスト形式で表現したものです。

JavaScriptオブジェクト	テキスト形式
文字列	"AAA"
数字	123
boolean	true または false
配列	["AAA","BBB","CCC"]
オブジェクト	{"key1":"value1","key2":"value2"}

Ajaxの通信に使ったり、データをLocalStorageに格納するときに使ったり、色々使えます。

テーブル作成

まずはテーブルを作ります。

				
CREATE TABLE tbl
(
  key SERIAL NOT NULL,
  value JSONB,
  CONSTRAINT tbl_pkey PRIMARY KEY (key)
)

JSONB型を指定している箇所をJSONとするとJSON型で作れます。

データ登録

次にデータを登録します。

pgAdminでは、そのまま登録できます。
テーブルを右クリックして、データビュー→すべての列を表示を選択します。

直接文字を打ち込み登録します。

配列は[ ]で囲みます。[要素,要素,要素]
マップは{ }で囲み、キーとバリューは:で繋げます。{キー:バリュー,キー:バリュー,キー:バリュー}

文字列はダブルクォーテーションで囲まないとエラーになります。

SQLでも登録できます。

				
INSERT INTO tbl(value) values('"ddd"'::JSONB);
INSERT INTO tbl(value) values('567'::JSONB);
INSERT INTO tbl(value) values('["ghi","jkh"]'::JSONB);
INSERT INTO tbl(value) values('{"aaa":"ddd","eee":"fff"}'::JSONB);
INSERT INTO tbl(value) values('{"aaa":{"bbb":"ccc"}}'::JSONB);

::JSONBでJSONB型にキャストしています。JSON型にキャストする場合は::JSONとして下さい。

データ取得

データを取得してみます。テーブルは以下のような状態です。

key	value
1	123
2	"abc"
3	["def"]
4	{"aaa": "bbb"}
5	"ddd"
6	567
7	["ghi", "jkh"]
8	{"aaa": "ddd","eee": "fff"}
9	{"aaa": {"bbb": "ccc"}}

単純なSELECT

				
select * from tbl where value = '123' or value = '"abc"'

key	value
1	123
2	"abc"

JSONB値が含まれるか(左に右が含まれる)

				
select * from tbl where value @> '"abc"' or value @> '123' or value @> '{"eee":"fff"}' or value @> '["ghi"]'

key	value
1	123
2	"abc"
7	["ghi", "jkh"]
8	{"aaa": "ddd", "eee": "fff"}

JSONB値が含まれるか(右に左が含まれる)

				
select * from tbl where value <@ '{"eee": "fff","abc": "def","aaa": "ddd"}' or value <@ '["aaa","def","jkh"]'

key	value
3	["def"]
8	{"aaa": "ddd", "eee": "fff"}

キーが存在するか

				
select * from tbl where value ? 'aaa' or value ? 'abc'

key	value
2	"abc"
4	{"aaa": "bbb"}
9	{"aaa": {"bbb": "ccc"}}
8	{"aaa": "ddd", "eee": "fff"}

キーがひとつでも存在するか

				
select * from tbl where value ?| array['aaa', 'abc']

key	value
2	"abc"
4	{"aaa": "bbb"}
9	{"aaa": {"bbb": "ccc"}}
8	{"aaa": "ddd", "eee": "fff"}

キーがすべて存在するか

				
select * from tbl where value ?& array['aaa','eee']

key	value
8	{"aaa": "ddd", "eee": "fff"}

データ更新(部分更新)

変更前

key	value
8	{"aaa": "ddd", "eee": "fff"}

				
update tbl set value = value || '{"eee":"xxx","ggg":"hhh","iii":{"jjj":"kkk"}}' where value @> '{"aaa":"ddd"}'

||は文字列を連結するときに使用しますが、JSONBでは同じキーは更新され、同じキーがなければ追加されます。
今回の例では、eeeは同じキーなので更新され、それ以外は新規に追加されます。

変更後①

key	value
8	{"aaa": "ddd", "eee": "xxx", "ggg": "hhh", "iii": {"jjj": "kkk"}}

				
update tbl set value = value - 'ggg' where value @> '{"aaa":"ddd"}'

-は数値をマイナスするときに使用しますが、JSONでは指定キーを削除します。

変更後②

key	value
8	{"aaa": "ddd", "eee": "xxx", "iii": {"jjj": "kkk"}}

				
update tbl set value = value #- '{iii,jjj}' where value @> '{"aaa":"ddd"}

#-のように#を付けるとマップの中にマップがあるとき中のマップのキーを指定して削除できます。

変更後③

key	value
8	{"aaa": "ddd", "eee": "xxx", "iii": {}}

ページのトップへ戻る