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

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

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

## 動作確認した環境

```sql
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 として取得する

```sql
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 を取得できます。

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

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

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

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

## JSONB 配列を作成する

- テストデータの準備:

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

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

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

### `jsonb_build_object` + `jsonb_agg`

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

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

<table>
  <tr>
    <th>jsonb_agg</th>
  </tr>
  <tr>
    <td>[{"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}]</td>
  </tr>
</table>

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

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

<table>
  <tr>
    <th>jsonb_agg</th>
  </tr>
  <tr>
    <td>[{"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}]</td>
  </tr>
</table>

## JSONB 配列を展開する

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

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

### `jsonb_array_elements`

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

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

<table>
  <tr>
    <th>key1</th>
    <th>key2</th>
  </tr>
  <tr>
    <td>foo</td>
    <td>20</td>
  </tr>
  <tr>
    <td>bar</td>
    <td>2</td>
  </tr>
</table>

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

### `jsonb_to_recordset`

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

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

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

<table>
  <tr>
    <th>key1</th>
    <th>key2</th>
  </tr>
  <tr>
    <td>foo</td>
    <td>20</td>
  </tr>
  <tr>
    <td>bar</td>
    <td>2</td>
  </tr>
</table>

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

## JSONB 配列 ←→ 配列

- JSONB 配列 → 配列にする:

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

- 配列を JSONB 配列にする

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

# ハマったこと

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

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

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

```sql
-- -> を使った場合
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(*)` ができない

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

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

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