JOSNデータ型とは
スキーマレスな何かを入れるときに重宝しそう。基本的にはjsonb
が良いっぽい。
やってみる
% psql --version psql (PostgreSQL) 9.5.0 % createdb hoge createdb: could not connect to database template1: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
あ、server動いてなかった
% postgres -D /usr/local/var/postgres LOG: database system was shut down at 2016-04-20 01:17:51 JST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started
で、
% createdb hoge % psql hoge psql (9.5.0) Type "help" for help. hoge=# \d No relations found. hoge=# CREATE TABLE foos ( hoge(# name TEXT, hoge(# opt_1 JSON, hoge(# opt_2 JSONB hoge(# ); CREATE TABLE hoge=# \d foos Table "public.foos" Column | Type | Modifiers --------+-------+----------- name | text | opt_1 | json | opt_2 | jsonb | hoge=#
テーブルはつくれた
INSERT
hoge=# INSERT INTO foos VALUES hoge-# ('otiai10', '{"ほげ": "ふが", "ぴよ": 100}', '{"ほげ": "ふが", "おっぱい": true}'), hoge-# ('otiai11', '{"spam": 0.001, "ham": [3, 5, 8, 11]}', '{"toto": {"pippo": {"aaa": "xxx", "bbb": 100}}}') hoge-# ; INSERT 0 2 hoge=#
おお
hoge=# SELECT * FROM foos; name | opt_1 | opt_2 ---------+---------------------------------------+------------------------------------------------- otiai10 | {"ほげ": "ふが", "ぴよ": 100} | {"ほげ": "ふが", "おっぱい": true} otiai11 | {"spam": 0.001, "ham": [3, 5, 8, 11]} | {"toto": {"pippo": {"aaa": "xxx", "bbb": 100}}} (2 rows) hoge=#
おお、jsonb
型だと標準出力にちゃんと表示されないかと思ったけどそうではないのか。アプリケーション的にマジで特殊な使い方*1をしない限りは、運用的にもjsonb
型でよさそう。そういえば、オブジェクトキーの重複をテストしてなかった。
hoge=# INSERT INTO foos VALUES hoge-# ('otiai12', '{"x": 100, "x": 200}', '{"x": 100, "x": 200}') hoge-# ; INSERT 0 1 hoge=# SELECT * FROM foos WHERE name = 'otiai12'; name | opt_1 | opt_2 ---------+----------------------+------------ otiai12 | {"x": 100, "x": 200} | {"x": 200} (1 row) hoge=#
このように、jsonb
型では最適化されて入ってるのがわかる。
READ
まずは全部
hoge=# SELECT opt_2::json FROM foos; opt_2 ------------------------------------------------- {"ほげ": "ふが", "おっぱい": true} {"toto": {"pippo": {"aaa": "xxx", "bbb": 100}}} {"x": 200} (3 rows)
検索
hoge=# SELECT opt_2::json FROM foos WHERE opt_2 -> 'おっぱい' ? 'おおきい'; opt_2 ------- (0 rows) hoge=# SELECT opt_2::json FROM foos WHERE opt_2 ->'おっぱい' = true; ERROR: operator does not exist: jsonb = boolean LINE 1: ...ELECT opt_2::json FROM foos WHERE opt_2 ->'おっぱい' = true; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. hoge=# SELECT opt_2::json FROM foos WHERE opt_2 ->'おっぱい' = 'true'; opt_2 ------------------------------------ {"ほげ": "ふが", "おっぱい": true} (1 row) hoge=#
ん?あとでちゃんとしらべよ
UPDATE
hoge=# UPDATE foos SET opt_2 = jsonb_set(opt_2, '{"おっぱい"}', '"おおきい"', true); UPDATE 3 hoge=# SELECT * FROM foos; name | opt_1 | opt_2 ---------+---------------------------------------+------------------------------------------------------------------------- otiai10 | {"ほげ": "ふが", "ぴよ": 100} | {"ほげ": "ふが", "おっぱい": "おおきい"} otiai11 | {"spam": 0.001, "ham": [3, 5, 8, 11]} | {"toto": {"pippo": {"aaa": "xxx", "bbb": 100}}, "おっぱい": "おおきい"} otiai12 | {"x": 100, "x": 200} | {"x": 200, "おっぱい": "おおきい"} (3 rows)
いいね
DELETE
hoge=# UPDATE foos SET opt_1 = json_object_delete_keys(opt_1, 'spam'); ERROR: function json_object_delete_keys(json, unknown) does not exist LINE 1: UPDATE foos SET opt_1 = json_object_delete_keys(opt_1, spam... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. hoge=# UPDATE foos SET opt_1 = opt_1 - 'spam'); ERROR: syntax error at or near ")" LINE 1: UPDATE foos SET opt_1 = opt_1 - 'spam'); ^ hoge=# UPDATE foos SET opt_1 = opt_1 - 'spam'; ERROR: operator does not exist: json - unknown LINE 1: UPDATE foos SET opt_1 = opt_1 - 'spam'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. hoge=# UPDATE foos SET opt_1 = opt_1::jsonb - 'spam'; UPDATE 3
雑感
- とりあえず触っただけ
JOIN
とかしだしたら、それふつうにRDBでやろう?ってはなしな気もする- あるいはミドルウェアの設計考え直したほうがいい気がする
- スキーマレスなサムシングを、若干のCRUDとともに使うなら、すばらしいソリューションなんじゃなかろーか、と感じた
DRYな備忘録として