postgresqlでJSONBを扱う

JSONBの概要と機能を調べた際の一時的なメモ。

JSONB

ドキュメントによると

There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format

JSONは、テキストとして保存され、 decomposed binary formatとして保存されるという違いがあるようです。 (decomposed binary formatの詳細は調べられてない。)

Faster Operations with the JSONB Data Type in PostgreSQLではJSONに対するJSONBの利点と欠点が以下のようにまとめられています。

Benefit

• より効率的
• 処理の高速化
• インデックスのサポート
• よりシンプルなスキーマデザイン

Drawbacks

• 少し入力（Insert?)が遅い
• テーブルのフットプリントが大きく、よりdisk spaceが必要
• 特定のクエリ（特にaggregation）が遅い

テーブルの作成

# CREATE TABLE test (dummy_id serial NOT NULL, data jsonb);

INSERT

# INSERT INTO test VALUES (1, '{"some_key":"some_value"}');
INSERT 0 1

SELECT

JSON Operator

9.15. JSON Functions and Operators

SELECT(キー要素)

# INSERT INTO test VALUES (1, '{"some_key":"some_value"}');
INSERT 0 1
# SELECT data->'some_key' AS some_key FROM test;
   some_key
--------------
 "some_value"
(1 row)

SELECT(配列要素)

# INSERT INTO test VALUES (1, '["a","b","c","d","e"]');
INSERT 0 1
# SELECT data->1 FROM test;
 ?column?
----------
 "b"
(1 row)

SELECT（特定の値を含むRECORDを調べる）

# INSERT INTO test VALUES (1, '["a","b","c"]');
INSERT 0 1
# SELECT * FROM test WHERE data ? 'a';
 dummy_id |      data
----------+-----------------
        1 | ["a", "b", "c"]
(1 row)

SELECT（特定の値の全て/いずれかを含むRECORDを調べる）

# INSERT INTO test VALUES (1, '["a","b","c"]');
INSERT 0 1
# SELECT * FROM test WHERE data ?| array['a', 'b'];
 dummy_id |      data
----------+-----------------
        1 | ["a", "b", "c"]
(1 row)
# SELECT * FROM test WHERE data ?& array['a', 'b'];
 dummy_id |      data
----------+-----------------
        1 | ["a", "b", "c"]
(1 row)

CREATE INDEX

The default GIN operator class for jsonb supports queries with the @>, ?, ?& and ?| operators. 

• 特定のJSONを含んでいるか/含まれているか
• 特定の値がキーまたは要素に含まれているか
• キーまたは要素を含んでいるか

# CREATE INDEX idx_some_key ON test USING gin ((data->'some_key'));
CREATE INDEX

json_opsとjson_path_opsがあり、デフォルトはjson_ops.

配列の中の特定の要素から検索する。

select dataset_item_id, attributes from dataset_items, jsonb_array_elements(dataset_items.attributes->'classification') element WHERE element->>'label' = 'cat';