PostgreSQL の JSONB でよく使われる操作をまとめてみた

社内で PostgreSQL の JSONB 型が使われることが多いです。

JSONB 型は便利ですがハマりやすくもあると感じました。よく使う操作をまとめてみます。

動作確認した環境

SELECT version();

PostgreSQL 12.8 (Debian 12.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

基本操作

• ->: 指定したキーの値を JSONB として取得する
• ->>: 指定したキーの値を TEXT として取得する
• #>: パスで指定したキーの値を JSONB として取得する
• #>>: パスで指定したキーの値を TEXT として取得する

SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)->'key1'; -- "hoge"
SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)->'key2'->0; -- "a"

SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)->>'key1'; -- hoge
SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)->'key2'->>0; -- a

SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)#>'{key1}'; -- "hoge"
SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)#>'{key2,0}'; -- "a"

SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)#>>'{key1}'; -- hoge
SELECT ('{"key1": "hoge", "key2": ["a", "b"]}'::JSONB)#>>'{key2,0}'; -- a

JSON にキーを追加する、書き換える

jsonb_set で指定したキーの値を書き換えた JSONB を取得できます。

-- キーの追加
SELECT
  jsonb_set(
    '{"key1": "hoge", "key2": ["a", "b"]}'::JSONB
    ,'{key3}'
    ,'1'
  )
;

{"key1": "hoge", "key2": ["a", "b"], "key3": 1}

-- キーの値の変更
SELECT
  jsonb_set(
    '{"key1": "hoge", "key2": ["a", "b"]}'::JSONB
    ,'{key2}'
    ,'1'
  )
;

{"key1": "hoge", "key2": 1}

JSONB 配列を作成する

• テストデータの準備:

DROP TABLE IF EXISTS t_sample;
CREATE TABLE t_sample AS
SELECT
  gen_random_uuid() AS uuid
  ,generate_series(1, 10) AS value
;

SELECT * FROM t_sample;

uuid	value
faa0c7a3-8c4e-4d06-ad2f-e1ccb8ef2425	1
f30851d4-60e3-444d-b540-be2dc61d2ba9	2
79f1e731-a93b-4139-aa42-7d2006aab4dd	3
bc930450-0eef-4b10-a285-d2d0bc065f7f	4
d79ea66b-8872-485c-98df-151cc5ddc84e	5
802afc33-ff4b-4e66-9f59-176e4331e364	6
6f23b3e2-318d-49e1-a518-cee0ec2c0d9e	7
3069ea78-02b0-4e1c-b3a7-6ee41e9e2ee6	8
aa2ce439-01ad-4589-b1ba-80f32685fe8b	9
05b23675-89e6-48f3-bfba-acc24ca1b40a	10

CREATE TABLE ... AS とすると SELECT した内容でテーブルを作成でき、動作確認に便利です。

jsonb_build_object + jsonb_agg

JSONB 配列を作成したい場合、jsonb_build_object と jsonb_agg を使うことでやりたいことができそうです。

-- jsonb_agg と jsonb_build_object でレコードをまとめる
SELECT
  jsonb_agg(
    jsonb_build_object(
      'uuid', t1.uuid
      ,'value', t1.value
    )
  )
FROM
  t_sample AS t1
;

jsonb_agg

[{"uuid": "faa0c7a3-8c4e-4d06-ad2f-e1ccb8ef2425", "value": 1}, {"uuid": "f30851d4-60e3-444d-b540-be2dc61d2ba9", "value": 2}, {"uuid": "79f1e731-a93b-4139-aa42-7d2006aab4dd", "value": 3}, {"uuid": "bc930450-0eef-4b10-a285-d2d0bc065f7f", "value": 4}, {"uuid": "d79ea66b-8872-485c-98df-151cc5ddc84e", "value": 5}, {"uuid": "802afc33-ff4b-4e66-9f59-176e4331e364", "value": 6}, {"uuid": "6f23b3e2-318d-49e1-a518-cee0ec2c0d9e", "value": 7}, {"uuid": "3069ea78-02b0-4e1c-b3a7-6ee41e9e2ee6", "value": 8}, {"uuid": "aa2ce439-01ad-4589-b1ba-80f32685fe8b", "value": 9}, {"uuid": "05b23675-89e6-48f3-bfba-acc24ca1b40a", "value": 10}]

jsonb_agg と 一緒に FILTER、ORDER BY が利用できて便利です。

-- value が偶数のレコードだけ value の降順にして JSONB 配列にする
SELECT
  jsonb_agg(
    jsonb_build_object(
      'uuid', t1.uuid
      ,'value', t1.value
    )
    ORDER BY t1.value DESC
  ) FILTER (WHERE t1.value % 2 = 0)
FROM
  t_sample AS t1
;

jsonb_agg
[{"uuid": "05b23675-89e6-48f3-bfba-acc24ca1b40a", "value": 10}, {"uuid": "3069ea78-02b0-4e1c-b3a7-6ee41e9e2ee6", "value": 8}, {"uuid": "802afc33-ff4b-4e66-9f59-176e4331e364", "value": 6}, {"uuid": "bc930450-0eef-4b10-a285-d2d0bc065f7f", "value": 4}, {"uuid": "f30851d4-60e3-444d-b540-be2dc61d2ba9", "value": 2}]

JSONB 配列を展開する

ストアドファクションに引数で JSONB 配列を渡して、データを一括で INSERT する、みたいなことをよくおこないます。

JSONB 配列の展開に便利な関数は以下です。

jsonb_array_elements

jsonb_array_elements を利用すると、JSONB 配列の各要素を順番に JSONB として取り出すことができます。

SELECT
  t1->>'key1' AS key1
  ,(t1->>'key2')::NUMERIC AS key2
FROM
  jsonb_array_elements('
    [
      {"key1": "foo", "key2": 20},
      {"key1": "bar", "key2": 2}
    ]
  ') AS t1
;

key1	key2
foo	20
bar	2

t1->>'key1' でアクセスしているように、t1 が JSONB になってしまうことが（個人的に）少しわかりにくく感じました。

jsonb_to_recordset

一方で、jsonb_to_recordset を利用すると、JSONB 配列をそのままテーブルのように扱って各要素を取り出すことができます。

以下では文字列を TEXT 型、数値を NUMERIC として取り出しています。

SELECT
  t1.key1
  ,t1.key2
FROM
  jsonb_to_recordset('
    [
      {"key1": "foo", "key2": 20},
      {"key1": "bar", "key2": 2}
    ]
  ') AS t1(
    key1 TEXT
    ,key2 NUMERIC
  )
;

key1	key2
foo	20
bar	2

AS 以降でキー名と型を指定する必要があります。
FROM 句にテーブルを指定したのと同じような感じでSELECT 句を書くことができます。
毎回 ->>'key' のような指定をしなくて済むので、jsonb_array_elements よりも積極的に使いたいと個人的に感じる方法です。

JSONB 配列 ←→ 配列

• JSONB 配列 → 配列にする:

SELECT ARRAY(SELECT jsonb_array_elements_text('["a", "b", "c"]'::JSONB));

• 配列を JSONB 配列にする

SELECT to_jsonb(ARRAY['a', 'b', 'c']);

ハマったこと

JSONB 型を使ってハマったことを書いてみました。

-> と ->> の挙動の違い

値があるかどうか判定するときに注意が必要です。

-- -> を使った場合
SELECT '{"key1": "value1", "key2": null}'::JSONB->'key2' IS NULL; -- FALSE（注意）
SELECT '{"key1": "value1", "key2": null}'::JSONB->'key3' IS NULL; -- TRUE
SELECT '{"key1": "value1", "key2": null}'::JSONB->>'key2' IS NULL; -- TRUE
SELECT '{"key1": "value1", "key2": null}'::JSONB->>'key3' IS NULL; -- TRUE

null のときとキーがないときを同じものとして判定したいときは、->> を利用するのがよさそうです。

to_jsonb(*) ができない

SELECT to_jsonb(*) FROM t_sample; -- エラーになる

ERROR:  function to_jsonb() does not exist
LINE 1: SELECT to_jsonb(*) FROM t_sample;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT to_jsonb(t1.*) FROM t_sample AS t1; -- うまくいく

to_jsonb
{"uuid": "af90648c-8508-42c9-bd12-5c85234b0771", "value": 1}
{"uuid": "6515b3b5-70d0-437c-a6b4-cea8e1eb66f4", "value": 2}
{"uuid": "a02bdf0a-a15f-4a88-ac02-b35cbbca51bc", "value": 3}
{"uuid": "8778a595-26a4-4f31-9070-98c6014b1438", "value": 4}
{"uuid": "b94ce2bc-efaa-4b0d-a207-2078083a1500", "value": 5}
{"uuid": "f146f2a2-68b3-4de4-b646-0d73955cdebc", "value": 6}
{"uuid": "8ecb62da-4e63-406d-a96c-91d608dda1da", "value": 7}
{"uuid": "6d55e4a9-6db7-4b53-ad31-874ac05dbcb0", "value": 8}
{"uuid": "ca0a0598-637d-415b-959f-404f988d7aba", "value": 9}
{"uuid": "19e973c4-0b62-4bde-9de9-9e0fa7b6169b", "value": 10}