読者です 読者をやめる 読者になる 読者になる

postgresのJSONカラムに触ってみた

Database postgres

準備

まずメモリ上にDB用の領域を確保。MacOSXではhdiutilというコマンドでディスクイメージを作成するのがお手軽。Linuxなら普通にtmpfsでどうぞ。

$ hdiutil attach -nomount ram://256000
/dev/disk2
$ newfs_hfs /dev/disk2
Initialized /dev/rdisk2 as a 125 MB case-insensitive HFS Plus volume
$ mkdir /tmp/tmpfs
$ mount -t hfs /dev/disk2 /tmp/tmpfs
$ mkdir /tmp/tmpfs/postgres

次にpsqlの対話シェルでテスト用のロール、テーブルスペース、DBを作成

$ psql postgres

postgres=# CREATE USER test;
postgres=# CREATE TABLESPACE test LOCATION '/tmp/tmpfs/postgres';
postgres=# CREATE DATABASE test TABLESPACE test;

あらためてtestユーザでpsqlにログインしてSQLを発行する

$ psql test -U test -W

JSON

JSONを保存できる型はJSON型とJSONB型、2つある理由は良く分からん。

test=> CREATE TABLE test1 (id SERIAL PRIMARY KEY, data JSON);
CREATE TABLE
test=> INSERT INTO test1 (data) VALUES ('{"a": 1, "b":"nyan","c":[1,2,3]}');
INSERT 0 1
test=> INSERT INTO test1 (data) VALUES ('{"a": 2, "b":"wang","c":[1,2,3]}');
INSERT 0 1
test=> INSERT INTO test1 (data) VALUES ('{"a": 2, "b":{"x":"hoge", "y":"fuga"}}');
INSERT 0 1
test=> SELECT * FROM test1;
 id |                  data
----+----------------------------------------
  1 | {"a": 1, "b":"nyan","c":[1,2,3]}
  2 | {"a": 2, "b":"wang","c":[1,2,3]}
  3 | {"a": 2, "b":{"x":"hoge", "y":"fuga"}}
(3 rows)

test=> SELECT data->'b' FROM test1;
         ?column?
--------------------------
 "nyan"
 "wang"
 {"x":"hoge", "y":"fuga"}
(3 rows)

test=> SELECT data->'b'->'x' FROM test1;
 ?column?
----------


 "hoge"
(3 rows)

JSONB型

プロパティを対象に演算したり、値にインデックスを張ったりできるらしい。

test=> CREATE TABLE test2 (id SERIAL PRIMARY KEY, data JSONB);
CREATE TABLE
test=> INSERT INTO test2 (data) VALUES ('{"a": 1, "b":"nyan","c":[1,2,3]}');
INSERT 0 1
test=> INSERT INTO test2 (data) VALUES ('{"a": 2, "b":"wang","c":[1,2,3]}');
INSERT 0 1
test=> INSERT INTO test2 (data) VALUES ('{"a": 2, "b":{"x":"hoge", "y":"fuga"}}');
INSERT 0 1
test=> SELECT * FROM test2 WHERE data ? 'c';
 id |                 data
----+---------------------------------------
  1 | {"a": 1, "b": "nyan", "c": [1, 2, 3]}
  2 | {"a": 2, "b": "wang", "c": [1, 2, 3]}
(2 rows)

test=> SELECT * FROM test2 WHERE data->'b' ? 'x';
 id |                   data
----+-------------------------------------------
  3 | {"a": 2, "b": {"x": "hoge", "y": "fuga"}}
(1 row)

JSONB型には @> 演算子が使える。指定のキーバリューが含まれていれば真となる。

test=> SELECT * FROM test2 WHERE data @> '{"a":1}';
 id |                 data
----+---------------------------------------
  1 | {"a": 1, "b": "nyan", "c": [1, 2, 3]}
(1 row)

test=> SELECT * FROM test2 WHERE data @> '{"c":[1]}';
 id |                 data
----+---------------------------------------
  1 | {"a": 1, "b": "nyan", "c": [1, 2, 3]}
  2 | {"a": 2, "b": "wang", "c": [1, 2, 3]}
(2 rows)

test=> SELECT * FROM test2 WHERE data @> '{"a":1, "b":"nyan"}';
 id |                 data
----+---------------------------------------
  1 | {"a": 1, "b": "nyan", "c": [1, 2, 3]}
(1 row)

test=> SELECT * FROM test2 WHERE data @> '{"b":{"x":"hoge"}}';
 id |                   data
----+-------------------------------------------
  3 | {"a": 2, "b": {"x": "hoge", "y": "fuga"}}
(1 row)

mongoDBのようにJSONに対して複雑な条件のクエリを書いたり、JSONの一部だけ更新といったことはできないようだ。

PostgreSQL: Documentation: 9.4: JSON Types