🐘

PostgreSQLのjson/jsonb型

2023/01/04に公開

PostgreSQL

JSON

tech

概要

PostgreSQLにはJSONを扱うためのjson/jsonb型があります。
この記事ではjson/jsonb型の比較や演算子・関数などについて紹介します。

Version

PostgreSQL v14.6

json型 vs jsonb型

json型とjsonb型のふたつがありますが、結論から言えばjsonb型を利用する方が望ましいです。公式ドキュメントでは、JSONキーの順序を保持したいときなどの特殊なケースでなければjsonb型を利用した方がよいとされています。

	json	jsonb
データの格納	○ 入力テキストの正確なコピーで格納する	△ 分解されたBinary形式で格納されるため、json型に比べて少し遅い
データの処理	△ 処理の度にparseが必要	○ parse不要のため高速
subscript	× 使えない	○ 使える
重複key	△ 保持する	○ 最後のkeyのみ保持する
keyの順序	△ 保持する	○ 保持しない
jsonb演算子	× 使えない	○ 使える
JSON関数	△ jsonb型より少ない	○ json型より多い
GIN index	× 使えない	○ 使える

大きな違いはjson型の方がデータの格納が早く、jsonb型はデータの処理が高速である点です。ほかには、後述するsubscriptやjsonb演算子が使えるなどの多数の点においてjsonb型の方が有利です。

構文

演算子	返り値の型	取得対象
json -> text	json	keyのvalue
json ->> text	text	keyのvalue
json -> integer	json	arrayの要素
json ->> integer	text	arrayの要素
json #> text	json	pathのvalue
json #>> text	text	pathのvalue
jsonb[text]	jsonb	keyのvalue

この構文のjsonはjsonbでも置換可能です。その場合は返り値もjsonbになります。

->, ->>

以下は最も単純なjson/jsonb型の表現です。x::typenameは型変換のsyntax sugarで、CAST(x AS typename)と同義です。json/jsonb型はJSON primitive型のstring, number, boolean, nullから変換できます。

select '1'::json;
select '1'::jsonb;

select '"hoge"'::json;
select 'true'::json;
select 'false'::json;
select 'null'::json;

# booleanは小文字のみ可
select 'TRUE'::json;
#=> ERROR:  invalid input syntax for type json

存在しないkeyを指定するとエラーにはならずnullを返します。

select ('{"name":"Bob"}'::json)->'name';       
#=> "Bob"
select ('{"name":"Bob"}'::json)->'address';    
#=> null

Arrayでも存在しない要素数を指定するとnullを返します。

select ('[1, "hoge", null]'::json)->0;     
#=> 1
select ('[1, "hoge", null]'::json)->1;     
#=> "hoge"
select ('[1, "hoge", null]'::json)->2;     
#=> null
select ('[1, "hoge", null]'::json)->-2;    
#=> "hoge"
select ('[1, "hoge", null]'::json)->3;     
#=> null

->>は->と異なりtext型を返します。
pg_typeof関数に結果を渡すと、返り値の型が異なっていることがわかります。

select pg_typeof(('{"address":{"state":"Tokyo"}}'::json)->'address');
#=> json
select pg_typeof(('{"address":{"state":"Tokyo"}}'::json)->>'address');
#=> text

#>, #>>

#>,#>>はArray[text|number]型のpathを指定しvalueを取得します。->, ->>と同じように、#>はjson/jsonb型を返し、#>>はtext型を返します。

select ('{"address":{"state":"Tokyo"}}'::jsonb)#>ARRAY['address'];
select ('{"address":{"state":"Tokyo"}}'::jsonb)#>'{"address"}';
# Arrayのdouble quoteは省略可
select ('{"address":{"state":"Tokyo"}}'::jsonb)#>'{address}';
#=>  {"state": "Tokyo"}

select ('{"address":{"state":"Tokyo"}}'::jsonb)#>'{address,state}';
#=> "Tokyo"

select pg_typeof(('{"address":{"state":"Tokyo"}}'::jsonb)#>'{address}');
#=> jsonb
select pg_typeof(('{"address":{"state":"Tokyo"}}'::jsonb)#>>'{address}');
#=> text

json(b)_extract_path, jsonb_path_query

#>,#>>に似た関数でjsonb_extract_pathとjsonb_path_queryがあります。

jsonb_extract_pathはjsonb型を第一引数にとり、Array[text|number]型のpathを可変長引数で指定して値を取得します。json型を引数にとるjson_extract_path関数も存在します。

select jsonb_extract_path('{"address":{"state":"Tokyo"}}'::jsonb, 'address', 'state');
#=> "Tokyo"
select json_extract_path('[1]'::json, '0');
#=> 1

jsonb_path_queryはjsonpathを第二引数に渡してjsonb型から値を取得します。 jsonpathはJSONデータを効率的に取得するための構文で、jsonpath型が存在します。

select '$.address.state'::jsonpath;
#=>  $."address"."state"(jsonpath)
select jsonb_path_query('{"address":{"state":"Tokyo"}}'::jsonb, '$.address.state');
#=> "Tokyo"

jsonb_path_query関数は存在しますが、json_path_query関数はPostgreSQL v14.6時点において実装されていません。

subscript

PostgreSQL v14から導入されたsubscriptです。JSONデータのアクセス方法としてはおそらく一番馴染み深いでしょう。

select ('{"address":{"state":"Tokyo"}}'::jsonb)['address']['state'];
#=> "Tokyo"
select ('[1, "hoge", null]'::jsonb)[0];
#=> 1

しかし、このsubscriptはjsonb型のみをサポートしており、json型にアクセスしようとするとエラーになります。

select ('{"address":{"state":"Tokyo"}}'::json)['address']['state'];
#=> ERROR:  cannot subscript type json because it does not support subscripting
select ('[1, "hoge", null]'::json)[0];
#=> ERROR:  cannot subscript type json because it does not support subscripting

WHERE句やUPDATE文でも使用できます。

create table json_test (info jsonb);
insert into json_test (info) values ('{"name":"Bob"}'::jsonb);
select info['name'] from json_test;
#=> "Bob"

update json_test set info['name'] = '"Alice"' where info['name'] = '"Bob"';
select info['name'] from json_test;
#=> "Alice"

この時、WHERE句の'"Bob"'はsingle quoteのなかでさらにdouble quoteで括っていることに注意してください。single quoteのみの場合はエラーになります。これまでの構文同様、subscriptはjsonb型を返すために型を合わせる必要があります。

update json_test set info['name'] = '"Alice"' where info['name'] = 'Bob';
#=> ERROR:  invalid input syntax for type json
#=> LINE 1: ...est set info['name'] = '"Alice"' where info['name'] = 'Bob';
#=>                                                                  ^
#=> DETAIL:  Token "Bob" is invalid.
#=> CONTEXT:  JSON data, line 1: Bob

構文まとめ

ここまでの構文をまとめると、下の例はいずれも同じ値を返します。基本的にはjsonb型でsubscriptを使い、適宜ほかの構文を使い分けることになるでしょう。

select ('{"address":{"state":"Tokyo"}}'::jsonb)->'address'->'state';
select ('{"address":{"state":"Tokyo"}}'::jsonb)#>'{address,state}';
select jsonb_extract_path('{"address":{"state":"Tokyo"}}'::jsonb, 'address', 'state');
select jsonb_path_query('{"address":{"state":"Tokyo"}}'::jsonb, '$.address.state');
select ('{"address":{"state":"Tokyo"}}'::jsonb)['address']['state'];

重複key

重複keyが存在する場合、jsonb型は最後のkeyとそのvalueのみを保持します。一方で、json型は重複keyをそのまま保持します。

しかし、本来JSONは重複keyを持つべきではありません。

RFC8259
The names within an object SHOULD be unique.

jqを代表として、おそらくは多くのJSONの実装がそうであるように、重複keyを許容しないjsonb型の仕様が本来あるべき姿です。

select '{"name":"Bob","name":"Alice"}'::jsonb;
#=> {"name": "Alice"}
select '{"name":"Bob","name":"Alice"}'::json;
#=> {"name":"Bob","name":"Alice"}

keyの順序

json型は入力時のkeyの順序を保持しますが、jsonb型は保持しません。こちらもJSONの仕様としてはjsonb型が正しいです。

RFC7517
By default, the order of the JWK values within the array does not imply an order of preference among them, although applications of JWK Sets can choose to assign a meaning to the order for their purposes, if desired.

create table peoples (
    info_json json,
    info_jsonb jsonb
);

insert into peoples (info_json, info_jsonb)
values ('{"name":"Bob","age":30}','{"name":"Bob","age":30}');

select * from peoples;

        info_json        |         info_jsonb
-------------------------+----------------------------
 {"name":"Bob","age":30} | {"age": 30, "name": "Bob"}

jsonb演算子

jsonb型のみサポートしている演算子です。booleanを返す演算子は後述のGIN indexでも利用します。

演算子	返り値の型	説明
jsonb @> jsonb	boolean	左辺が右辺を含むか
jsonb <@ jsonb	boolean	右辺が左辺を含むか
jsonb ? text	boolean	左辺が右辺をトップレベルのkey or 配列要素として含むか
jsonb ?\| text[]	boolean	左辺が右辺のいずれかをトップレベルのkey or 配列要素として含むか
jsonb ?& jsonb	boolean	左辺が右辺のすべてをトップレベルのkey or 配列要素として含むか
jsonb \|\| text[]	jsonb	左辺と右辺を結合する
jsonb - text[]	jsonb	左辺から右辺と一致するkey/valueを削除する
jsonb - integer	jsonb	左辺から右辺のindexの配列要素を削除する
jsonb #- jsonb	jsonb	左辺から右辺のpathのkey/value or 配列要素を削除する
jsonb @? jsonpath	boolean	左辺は右辺のjsonpathの値を返すか
jsonb @@ jsonpath	boolean	左辺は右辺のjsonpathの条件が真であるか

select
    jsonb @> '{"name":"Bob"}'               as "@>",
    '{"name":"Bob"}' <@ jsonb               as "<@",
    jsonb ? 'name'                          as "?",
    jsonb ?| array['name']                  as "?|",
    jsonb ?& array['name','address']        as "?&",
    jsonb['name'] || jsonb['name']          as "||",
    jsonb - 'name'                          as "-",
    jsonb #- '{address,state}'              as "#-",
    jsonb @? '$.address.state'              as "@?",
    jsonb @@ '$.address.state == "Tokyo"'   as "@@"
from  (
    select
        '{"name":"Bob", "address":{"state":"Tokyo"}}'::jsonb as jsonb
    ) as base;

 @> | <@ | ? | ?| | ?& |       ||       |                -                |
----+----+---+----+----+----------------+---------------------------------+
 t  | t  | t | t  | t  | ["Bob", "Bob"] | {"address": {"state": "Tokyo"}} |

               #-               | @? | @@
--------------------------------+----+----
 {"name": "Bob", "address": {}} | t  | t

select '[1, "hoge", null]'::jsonb - 0;
#=> ["hoge", null]
select '[1, "hoge", null]'::jsonb - 3;
#=> [1, "hoge", null]
select '{"name":"Bob"}'::jsonb - 0;
#=> ERROR:  cannot delete from object using integer index

JSON関数

json/jsonb型用の関数の一部のサンプルを紹介します。json/jsonb型の両方に関数が実装されているものもありますが、jsonb型のみの関数も多数存在します。

select
    jsonb_extract_path_text(jsonb, 'address', 'state'),
    jsonb_set(jsonb, '{"name"}', '"Alice"'),
    jsonb_insert(jsonb, '{"address","ZIP"}', '"111-1111"'),
    jsonb_typeof(jsonb)
from  (
    select
        '{"name":"Bob", "address":{"state":"Tokyo"}}'::jsonb as jsonb
    ) as base;

 jsonb_extract_path_text |
-------------------------+
 Tokyo                   |

                    jsonb_set                     |
--------------------------------------------------+
 {"name": "Alice", "address": {"state": "Tokyo"}} |

                           jsonb_insert                            |
-------------------------------------------------------------------+
 {"name": "Bob", "address": {"ZIP": "111-1111", "state": "Tokyo"}} |

 jsonb_typeof
--------------
 object

select
    jsonb_each(jsonb),
    jsonb_each_text(jsonb),
    jsonb_object_keys(jsonb)
from  (
    select
        '{"name":"Bob", "address":{"state":"Tokyo"}}'::jsonb as jsonb
    ) as base;

             jsonb_each             |          jsonb_each_text           |
------------------------------------+------------------------------------+
 (name,"""Bob""")                   | (name,Bob)                         |
 (address,"{""state"": ""Tokyo""}") | (address,"{""state"": ""Tokyo""}") |

 jsonb_object_keys
-------------------
 name
 address

select
    jsonb_array_elements(jsonb),
    jsonb_array_elements_text(jsonb)
from  (
    select
        '["hoge", null, {"a":1,"b":null}]'::jsonb as jsonb
    ) as base;

 jsonb_array_elements | jsonb_array_elements_text
----------------------+---------------------------
 "hoge"               | hoge
 null                 |
 {"a": 1, "b": null}  | {"a": 1, "b": null}

select
    jsonb_array_length(jsonb),
    jsonb_strip_nulls(jsonb)
from  (
    select
        '["hoge", null, {"a":1,"b":null}]'::jsonb as jsonb
    ) as base;

 jsonb_array_length |    jsonb_strip_nulls
--------------------+--------------------------
                  3 | ["hoge", null, {"a": 1}]

GIN index

jsonb型をサポートするindexとしてGIN(Generalized Inverted Index) indexが存在します。GIN indexはjsonb型のkeyやkey/valueのペアを効率的に検索可能にします。GIN indexを作成し、対応する演算子を使用することで利用できます。

以下にサンプルテーブルと適当な10万行のjsonbデータを作成するクエリを示します。

create table gin_index_test (jsonb jsonb);

insert into gin_index_test (jsonb)
select
    jsonb_build_object('a', series::text, 'b', md5(random()::text))
    from generate_Series(1,100000) as series;

select count(1) from gin_index_test;
#=> 100000

select * from gin_index_test limit 1;
#=>  {"a": "1", "b": "ba53cd72db9dc8a540e7b01fb20d9b19"}

まだindexが存在しない状態で実行計画がSeq Scanであることを確認します。

explain analyze select * from gin_index_test where jsonb @> '{"a":"1"}';

 Seq Scan on gin_index_test  (cost=0.00..2500.00 rows=10 width=59) (actual time=0.183..32.693 rows=1 loops=1)
   Filter: (jsonb @> '{"a": "1"}'::jsonb)
   Rows Removed by Filter: 99999
 Planning Time: 1.777 ms
 Execution Time: 32.726 ms
(5 rows)

B-Tree indexを作成してもSeq Scanから変わりません。
そこでGIN indexを作成します。

create index idx_gin_index_test_jsonb__gin on gin_index_test using gin (jsonb);
# 以下のB-Tree indexではSeq Scanのまま
#   create index idx_index_test_jsonb__btree on gin_index_test using btree (jsonb);

 Bitmap Heap Scan on gin_index_test  (cost=28.08..65.52 rows=10 width=59) (actual time=0.060..0.060 rows=1 loops=1)
   Recheck Cond: (jsonb @> '{"a": "1"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_gin_index_test_jsonb__gin  (cost=0.00..28.07 rows=10 width=0) (actual time=0.053..0.054 rows=1 loops=1)
         Index Cond: (jsonb @> '{"a": "1"}'::jsonb)
 Planning Time: 0.298 ms
 Execution Time: 0.085 ms
(7 rows)

Bitmap Index Scanに変わっていることが確認できました。

まとめ

PostgreSQLのjson/jsonb型を紹介しました。json/jsonb型は多数のサポートが実装されているため、JSONデータを扱いたいケースでの活用をオススメします。また、この記事で取り上げた多数の理由の通り、どちらかといえばjsonb型を採用すべきであると言えるでしょう。