PostgreSQLでjsonを扱う方法を色々調べてみた

はじめに

私の参画しているプロジェクトではPostgreSQLを使用しています。

先日、業務中にjsonb_object_aggという関数を目にする機会がありました。そういえばPostgreSQLでのjsonの扱いについてあまり知らないなと思ったので、jsonの取得方法や、jsonに関する関数などを調べてみたのでメモとして残しておきます。

jsonとjsonbの違い

PostgreSQLでデータをjson形式で格納する際には、json型とjsonb型の二つがあります。

これら二つの型の違いは、公式ドキュメントによると、以下の通りです。

json型とjsonb型というデータ型は、ほとんど 同一の入力値セットを受け入れます。 現実的に主要な違いは効率です。 jsonデータ型は入力テキストの正確なコピーで格納し、処理関数を実行するたびに再解析する必要があります。 jsonbデータ型では、分解されたバイナリ形式で格納されます。 格納するときには変換のオーバーヘッドのため少し遅くなりますが、処理するときには、全く再解析が必要とされないので大幅に高速化されます。 また jsonb型の重要な利点はインデックスをサポートしていることです。

引用元：8.14. JSONデータ型

jsonb型の方が高速に処理され、インデックスもサポートされていることから、json型でなければならない理由がない限りはjsonb型を使った方が良さそうです。

この記事ではjson型、jsonb型どちらでも良い場合はjsonb型を使用することにします。

また、この記事で使用しているPostgreSQLのバージョンは14.5です。

jsonb型のカラムを作成する

DDL文でjsonb型のカラムを直接作成するときは、型にそのままjsonbと指定します。

CREATE TABLE "user" ("id" SERIAL NOT NULL, "name" character varying NOT NULL, "note" jsonb NOT null, PRIMARY KEY ("id"))

TypeORMでエンティティを作成するときは、以下のように定義します。

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @Column("jsonb")
    note: any

}

jsonb型のカラムから値を取得

普通にselect文を発行すると、jsonがそのまま取得できます。取得される型もjsonb型のままです。

select note from "user";

格納されているデータ
note
---
[{"id":1,"name":"name1"},{"id":2,"name":"name2"},{"id":3,"name":"name3"}]

取得結果
note
---
[{"id":1,"name":"name1"},{"id":2,"name":"name2"},{"id":3,"name":"name3"}]

jsonの配列のうち、特定の要素を取得したい場合は、"->"演算子または"->>"演算子の後ろにインデックス番号を記述します。"->"演算子を使うとjsonb型のまま取得でき、"->>"演算子を使うとtext型として取得できます。

select note->1 from "user";

格納されているデータ
note
---
[{"id":1,"name":"name1"},{"id":2,"name":"name2"},{"id":3,"name":"name3"}]

取得結果
?column?
---
{"id":2,"name":"name2"}

また、配列ではないjsonに対して"->"演算子または"->>"演算子の後ろにjsonのキー名を記述すると、キーに対応する値が取得できます。上と同じように、"->"演算子と"->>"演算子の違いは取得時の型です。

select note->'name' from "user";

格納されているデータ
note
---
{"id":7,"name":"fn6ln6"}

取得結果
?column?
---
"fn6ln6"

idが7のレコードのnameが取得したい場合は、以下のようにして取得できます。

select note->'name' from "user" where (note->>'id') = '7';

これらの演算子はjson型でもjsonb型でもどちらでも使用できます。

jsonを作成する

jsonb型のカラムから値を取得するだけでなく、SQLを使ってjson型またはjsonb型の値を作成することもできます。

select to_jsonb("name") from "user";

格納されているデータ
name
---
fn0
fn28
fn5

取得結果
to_jsonb
---
"fn0"
"fn28"
"fn5"

レコードの各カラムをjson形式にすることもできます。このとき、jsonのキーはf1、f2、…のように自動的に連番が付与されるようです。

select row_to_json(row("id", "name")) from "user";

格納されているデータ
id | name
---
1  | fn0
29 | fn28
6  | fn5

取得結果
row_to_json
---
{"f1":1,"f2":"fn0"}
{"f1":29,"f2":"fn28"}
{"f1":6,"f2":"fn5"}

集約関数

集約関数とは、入力値の集合から単一の結果を計算するものです。

jsonb_agg関数は、全ての入力値をjsonの配列として集約する関数です。以下のSQL文は、id列が5未満のレコードのname列の値を、単一のjson配列に集約します。

select jsonb_agg("name") from "user" where "id" < 5;

格納されているデータ
id | name
---
1  | fn0
2  | fn1
3  | fn2
4  | fn3
5  | fn4

取得結果
jsonb_agg
---
["fn0", "fn1", "fn2", "fn3"]

特定の順番でソートして配列にしたいという場合は、カッコ内にorder by句を指定します。

select jsonb_agg("name" order by "id" desc) from "user" where "id" < 5;

格納されているデータ
id | name
---
1  | fn0
2  | fn1
3  | fn2
4  | fn3
5  | fn4

取得結果
jsonb_agg
---
["fn3", "fn2", "fn1", "fn0"]

json_object_agg関数は、配列ではなくjsonのオブジェクトとして集約する関数です。jsonb_agg関数と異なり、第一引数にキー名、第二引数に値を指定する必要があります。下のSQL文では、id列の先頭にjson_という文字列を付加した文字列をキー名、name列の値を値として設定しています。

select jsonb_object_agg(concat('json_', "id"), "name") from "user" where id < 5;

格納されているデータ
id | name
---
1  | fn0
2  | fn1
3  | fn2
4  | fn3
5  | fn4

取得結果
jsonb_object_agg
---
{"json_1": "fn0", "json_2": "fn1", "json_3": "fn2", "json_4": "fn3"}

集約されたjsonの中で、キーが一番小さいものを取得する場合は、以下のようになります。

select jsonb_object_agg(concat('json_', "id"), "name")->>min(concat('json_', "id")) from "user" where "id" < 5;

これは、以下のSQL文と同じになります。jsonの中から、json_1というキーをもつ値を取得するということになります。

select jsonb_object_agg(concat('json_', "id"), "name")->>'json_1' from "user" where "id" < 5;

これらの集約関数はjsonb型に集約する関数ですが、json型に集約する関数としてjson_agg、json_object_aggという関数もあります。使い方は同じです。

おわりに

json作成関数や集約関数を自分で使う機会は今のところなさそうですが、目にしたときにうろたえないように調べたことをまとめてみました。便利ですが、慣れていない人が見ると少し戸惑ってしまう可能性があるので、使用する際にはあまり技巧的にならないように注意した方が良いかもしれません。