PostgreSQL 9.5 JSONB機能強化
来月のOSC北海道での発表資料作成の一環(あと、転用可能なら来週末のPostgreSQL Unconference穴埋め用)として、PostgreSQL 9.5のJSONB機能について、ちょいと調べてみた。
What's new in PostgreSQL 9.5
まだ、beta版リリース前ってことで、リリースノートはもちろん出てないけど、What's new in PostgreSQL 9.5 - PostgreSQL wikiに、PostgreSQL 9.5で取り込まれそうな(まだ確定ではない・・・はず)機能がリストアップされていたので見てみると、JSONBについても幾つかの演算子や関数が記載されていた。
ざっと見た感じだと、性能向上とか格納形式の変更というものではなく(それは9.6以降なのかな？PGCon 2015のセッションにもあったみたいだけど)、JSONB文書の部分更新のための演算子や、関数のようだ。あとは、見た目をわかりやすくするためのpretty関数かな。
PostgreSQL 9.5-devel(一週間くらい前のsnapshotだけど)の動作環境があるので動作を確認してみた。
9.5追加のJSONB演算子/関数
追加されたのは以下の演算子と関数
- 連結演算子 || ：これはJSONBに、要素あるいは配列を追加するというもの。
- 除去演算子 - ：これは存在するキーを削除するというもの。
- 存在しないキーを指定した場合は、何もしない。エラーにはならないことに注意。
- 指定したキーの値がリテラルでなくJSONB構造だった場合も容赦なく削除する。ヤバイ。
- 除去演算子 - ：右辺にtextでなくtextを指定する。textにはパス、または配列要素番号を指定する。
- 指定の仕方がちょっと感覚的イケてない気もするけど仕方ないのか。
- 置換関数 jsonb_replace()：演算子でなく関数で書きたい人向けｗ
- 第1引数はJSONBデータ、第2引数は置換したいパス、第3引数は置換されるJSONBデータ。
- 整形関数 jsonb_pretty() ：要素単位に行を分けて見やすくしてくれる。
- 階層があった場合も階層を意識した表示をしてくれる。
実行例
連結演算子 ||
- || 演算子で要素を追加する
test=# SELECT '{"name":"nuko", "age":17, "gender":"Male"}'::jsonb || '{"location":"Yokohama"}';
?column?
-----------------------------------------------------------------------
{"age": 17, "name": "nuko", "gender": "Male", "location": "Yokohama"}
(1 row)
- 既に存在するキーを追加しようとする場合は値が置換される。
test=# SELECT '{"name":"nuko", "age":17, "gender":"Male"}'::jsonb || '{"age":25}';
?column?
-----------------------------------------------
{"age": 25, "name": "nuko", "gender": "Male"}
(1 row)
- 配列ももちろん連結できます。
test=# SELECT '{"name":"nuko", "age":17, "gender":"Male"}'::jsonb || '{"hobby":["ramen","PostgreSQL"]}';
?column?
---------------------------------------------------------------------------------
{"age": 17, "name": "nuko", "hobby": ["ramen", "PostgreSQL"], "gender": "Male"}
(1 row)
- リテラル値を構造に置換する例
test=# SELECT '{"name":"nuko", "age":17, "gender":"Male"}'::jsonb || '{"name":{"first":"nuko","last":"yokohama"}}';
?column?
------------------------------------------------------------------------------
{"age": 17, "name": {"last": "yokohama", "first": "nuko"}, "gender": "Male"}
(1 row)
除去演算子 -
- locationを削除する例
test=# SELECT '{"name":"nuko", "age":17, "gender":"Male", "location":"Yokohama"}'::jsonb - 'location';
?column?
-----------------------------------------------
{"age": 17, "name": "nuko", "gender": "Male"}
(1 row)
- 階層をもつnameを削除する例
test=# SELECT '{"name":{"first":"nuko", "last":"yokohama"}, "age":17, "gender":"Male"}'::jsonb - 'name';
?column?
-------------------------------
{"age": 17, "gender": "Male"}
(1 row)
- (バグ？) この版だと削除がうまくいかないケースがあった。
- 開発中のバグっぽいレポートってhackers-mlに出すのかしらん。
test=# SELECT '{"name":{"first":"nuko", "last":"yokohama"}, "age":17, "gender":"Male"}'::jsonb - 'age';
ERROR: unknown type of jsonb container to convert
test=#
- nameの下のlast要素を削除する例
test=# SELECT '{"name":{"first":"nuko", "last":"yokohama"}, "age":17, "gender":"Male"}'::jsonb - '{name,last}'::text[];
?column?
----------------------------------------------------------
{"age": 17, "name": {"first": "nuko"}, "gender": "Male"}
(1 row)
- hobbyの2番目(0相対で1)を削除する例
test=# SELECT '{"name":"nuko", "age":17, "hobby":["ramen","PostgreSQL","cat"]}'::jsonb - '{hobby,1}'::text[];
?column?
--------------------------------------------------------
{"age": 17, "name": "nuko", "hobby": ["ramen", "cat"]}
(1 row)
- 配列要素番号に負数を与えると末端からの0相対位置になる。-1を指定するとにゃんこが消える(´・ω・｀)
test=# SELECT '{"name":"nuko", "age":17, "hobby":["ramen","PostgreSQL","cat"]}'::jsonb - '{hobby,-1}'::text[];
?column?
---------------------------------------------------------------
{"age": 17, "name": "nuko", "hobby": ["ramen", "PostgreSQL"]}
(1 row)
置換関数 jsonb_replace()
- ageを25に置換する。
test=# SELECT jsonb_replace('{"name":"nuko", "age":17, "hobby":["ramen","PostgreSQL","cat"]}'::jsonb,
'{age}', '25');
jsonb_replace
----------------------------------------------------------------------
{"age": 25, "name": "nuko", "hobby": ["ramen", "PostgreSQL", "cat"]}
(1 row)
- nameを階層構造をもったJSONBに置換する。
test=# SELECT jsonb_replace('{"name":"nuko", "age":17}'::jsonb,
'{name}', '{"first":"nuko", "last":"yokohama"}');
jsonb_replace
------------------------------------------------------------
{"age": 17, "name": {"last": "yokohama", "first": "nuko"}}
(1 row)※あれ・・・今気づいたけど、第2階層以降ってキー名のソート順序が変わってたりするのか？今後、要調査。
- キーが存在しない場合には何もしない。
test=# SELECT jsonb_replace('{"name":"nuko", "age":17, "hobby":["ramen","PostgreSQL","cat"]}'::jsonb,
'{hoge}', '25');
jsonb_replace
----------------------------------------------------------------------
{"age": 17, "name": "nuko", "hobby": ["ramen", "PostgreSQL", "cat"]}
(1 row)
整形出力関数 jsonb_pretty()
- 整形しない例と整形した例。階層や配列が合った場合にインデントを付与しているっぽい。
test=# SELECT '{"name":{"first":"nuko", "last":"yokohama"}, "age":17, "hobby":["ramen","PostgreSQL","cat"]}'::jsonb;
jsonb
-----------------------------------------------------------------------------------------------------
{"age": 17, "name": {"last": "yokohama", "first": "nuko"}, "hobby": ["ramen", "PostgreSQL", "cat"]}
(1 row)
test=# SELECT jsonb_pretty('{"name":{"first":"nuko", "last":"yokohama"}, "age":17, "hobby":["ramen","PostgreSQL","cat"]}'::jsonb);
jsonb_pretty
-----------------------------
{ +
"age": 17, +
"name": { +
"last": "yokohama",+
"first": "nuko" +
}, +
"hobby": [ +
"ramen", +
"PostgreSQL", +
"cat" +
] +
}
(1 row)
