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

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

## バージョン
PostgreSQL 9.6.4

## テーブル作成

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

## INSERT

`json_build_object`を使うと便利！！

```sql
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型による完全一致で検索ができる

```sql
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}}}
```

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

```sql
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して比較する

```sql
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が必要）

```sql
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型では使用不可. また、最下層でも`->`を使用している点に注意）

```sql
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）

```sql
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）

```sql
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"}}}
```