JOSNデータ型とは

8.14. JSONデータ型
- "このようなデータは、text型として格納することもできますが、"
- "各種JSON固有の関数と演算子もあります"
- "JSONデータ型にはjson型とjsonb型という2種類"
- " jsonb型の重要な利点はインデックスをサポートしていることです。"
  - ぱないの

スキーマレスな何かを入れるときに重宝しそう。基本的には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=#

ん？あとでちゃんとしらべよ

PostgreSQL: Documentation: 9.5: JSON Functions and Operators

UPDATE

How do I modify fields inside the new PostgreSQL JSON datatype? - Stack Overflow

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