トッカンソフトウェア

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": {}}




ページのトップへ戻る