PostgreSQLメモ：JSON, JSONB型の登録・検索・更新

JSON, JSONB型を検索・更新するときにいつも手間取るので、メモ_φ(･_･

バージョン

PostgreSQL 9.6.4

テーブル作成

CREATE TABLE hoge (
    id SERIAL,
    value_json JSON,
    value_jsonb JSONB 
);

INSERT

json_build_objectを使うと便利！！

INSERT INTO hoge (
    value_json,
    value_jsonb
) VALUES (
    json_build_object('aaa', 111, 'bbb', 222, 'ccc', json_build_object('AAA', 333, 'BBB', 444, 'CCC', json_build_object('abc', 'xxx', 'bcd', 'yyy'))),
    json_build_object('aaa', 111, 'bbb', 222, 'ccc', json_build_object('AAA', 333, 'BBB', 444, 'CCC', json_build_object('abc', 'xxx', 'bcd', 'yyy')))
);

select * from hoge;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : 100, "bcd" : 200}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": 100, "bcd": 200}}}

SELECT

1. キーを指定して完全一致で検索する

->>を使うとTEXT型による完全一致で検索ができる

SELECT * FROM hoge WHERE value_json->>'aaa' = '111';
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : 100, "bcd" : 200}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": 100, "bcd": 200}}}

SELECT * FROM hoge WHERE value_jsonb->>'aaa' = '111';
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : 100, "bcd" : 200}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": 100, "bcd": 200}}}

ネストしている階層にアクセスする場合は最下層以外は->でアクセスする

SELECT * FROM hoge WHERE value_json->'ccc'->>'AAA' = '333';
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

SELECT * FROM hoge WHERE value_jsonb->'ccc'->>'AAA' = '333';
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

2. キーを指定して値の大小比較で検索する

INT型にCASTして比較する

SELECT * FROM hoge WHERE (value_json->>'aaa')::INT > 1;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

SELECT * FROM hoge WHERE (value_jsonb->>'aaa')::INT > 1;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

3. JSON要素を比較して検索する

TEXT型にCASTして検索する（JSON型はOKだがJSONB型はJSONB→JSONへのCASTが必要）

SELECT * FROM hoge WHERE value_json->'ccc'->>'CCC' = (json_build_object('abc', 'xxx', 'bcd', 'yyy'))::TEXT;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

SELECT * FROM hoge WHERE value_jsonb->'ccc'->>'CCC' = (json_build_object('abc', 'xxx', 'bcd', 'yyy'))::TEXT;
(0 rows)

SELECT * FROM hoge WHERE value_jsonb->'ccc'->>'CCC' = (json_build_object('abc', 'xxx', 'bcd', 'yyy')::JSONB)::TEXT;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

JSONB型は@>を使うとよい（JSON型では使用不可. また、最下層でも->を使用している点に注意）

SELECT * FROM hoge WHERE value_json->'ccc'->'CCC' @> json_build_object('abc', 'xxx', 'bcd', 'yyy');
ERROR:  operator does not exist: json @> json
LINE 1: SELECT * FROM hoge WHERE value_json->'ccc'->'CCC' @> json_bu...
                                                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

SELECT * FROM hoge WHERE value_jsonb->'ccc'->'CCC' @> (json_build_object('abc', 'xxx', 'bcd', 'yyy'))::jsonb;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

UPDATE

JSON要素の一部を変更する

JSONBでは||を使って元のJSONBの値と変更したい要素をマージする

（PostgreSQL 9.5以上が条件のよう 参考：https://www.postgresql.jp/document/9.5/html/functions-json.html）

UPDATE
    hoge
SET
    value_jsonb = value_jsonb::jsonb || json_build_object(
        'bbb', 999
    )::jsonb
WHERE
    id = 1;

UPDATE 1

select * from hoge;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 999, "ccc": {"AAA": 333, "BBB": 444, "CCC": {"abc": "xxx", "bcd": "yyy"}}}

一部要素を削除したい場合は#-を使用する
（こちらもPostgreSQL 9.5以上が条件のよう 参考：https://www.postgresql.jp/document/9.5/html/functions-json.html）

UPDATE
    hoge
SET
    value_jsonb = value_jsonb #- '{ccc, BBB}'
WHERE
    id = 1;

UPDATE 1

select * from hoge;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
id          | 1
value_json  | {"aaa" : 111, "bbb" : 222, "ccc" : {"AAA" : 333, "BBB" : 444, "CCC" : {"abc" : "xxx", "bcd" : "yyy"}}}
value_jsonb | {"aaa": 111, "bbb": 222, "ccc": {"AAA": 333, "CCC": {"abc": "xxx", "bcd": "yyy"}}}