Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
7
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

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';
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
7
Help us understand the problem. What are the problem?