(この記事はmixi advent calendar5日目の投稿です。)

年内にリリース予定のPostgreSQL9.5ではjsonb型のデータを操作する関数が拡充され、より自由自在にJSONを扱うことが可能となります。この記事ではPostgreSQL9.5を用い、REST APIのJSON生成部分を SQLのみで 実現するサンプルコードを紹介することで、「なにこれ、こんなことできるならORM使う必要なくなるじゃん」みたいな感じで皆さんに驚いてもらうことを目的としています。[1]

以下、次に示すスキーマを持つブログサービスのREST APIを題材として話を進めます。

create table "User" (
  id varchar(10),
  name varchar(20)
);

create table "Post" (
  id int,
  user_id varchar(10),
  content text
);

create table "Reply" (
  id int,
  user_id varchar(10),
  post_id int,
  message text
);

insert into "User" (id, name) values('hoge', 'ほげ'),('foo', 'ふう'),('bar', 'ばあ');
insert into "Post" (user_id, content) values(1, 'hoge', '今日は天下寿司を食べた');
insert into "Post" (user_id, content) values(2, 'foo', 'ぬるぽ');
insert into "Reply" (user_id, post_id, message) values('foo', 1, 'いいね！'),('bar', 1, '炙りサーモンある');
insert into "Reply" (user_id, post_id, message) values('bar', 2, 'ガッ');

まずはじめに、ユーザーのIDを指定してオブジェクトを取得するエンドポイント/users/:idを作ります。

select to_jsonb("User") as "/users/:id"
from "User"
where id = $1
;

このクエリは、特定のidを持つユーザーのJSON Objectを生成するクエリです。to_jsonbは様々な型の上に定義されていますが、row型を引数として渡すと(このクエリではテーブルの参照であるUserという識別子を渡しているためrow型を渡していることになります)、カラム名をキーとしたJSON Object(jsonb型)を返します。プリペアドステートメント引数$1にhogeを代入してクエリを実行した結果が以下になります。

 {"id": "hoge", "name": "ほげ"}

期待する結果になってますね！

次に、すべてのユーザーのリストを取得するエンドポイント/usersを作ります。Userオブジェクトの配列を取得するクエリは次のようになります。

select jsonb_set('{}'::jsonb, '{users}', jsonb_agg("user")) as "/users"
from (
  select to_jsonb("User") as "user"
  from "User"
) t
;

先ほど紹介したto_jsonbでUserレコードをjsonb型に変換した後、それを集約関数jsonb_aggに渡してArrayにしています。さらにそれをusersをキーとするObjectで包んでいます。

結果は下。ちゃんとデータがネストされてる！

{"users": [{"id": "hoge", "name": "ほげ"}, {"id": "foo", "name": "ふう"}, {"id": "bar", "name": "ばあ"}]}

では次に、すべての記事のリストを取得する/postsを作ります。その際、オブジェクトにはuser_idではなく、Userオブジェクトそのものをネストして含めたいとします。クエリはこんな感じになります。

select jsonb_set('{}'::jsonb, '{posts}', jsonb_agg("post")) as "/posts"
from (
  select jsonb_set(to_jsonb("Post") - 'user_id', '{user}', to_jsonb("User")) as "post"
  from "Post"
     , "User"
  where "Post"."user_id" = "User"."id"
) t
;

to_jsonb("Post") - 'user_id'はPostをObject化した後user_idキーを取り除いています。それをjsonb_set関数に渡してuserをキーとしてUserのオブジェクトを値として追加しています。

結果は下。超便利。

 {"posts": [{"id": 1, "user": {"id": "hoge", "name": "ほげ"}, "content": "今日は天下寿司を食べた"}, {"id": 2, "user": {"id": "foo", "name": "ふう"}, "content": "ぬるぽ"}]}

さらにPostオブジェクトに、Replyオブジェクトの配列を持つrepliesフィールドを追加したいとします。クエリはこうなります。

select jsonb_set('{}'::jsonb,
                 '{posts}',
                 jsonb_agg(jsonb_set("post" - 'user_id',
                                     '{user}',
                                     to_jsonb("User")))) as "/posts"
from (
  select jsonb_set(to_jsonb("Post"), '{replies}', jsonb_agg("Reply")) as "post"
       , "Post"
  from "Post"
     , "Reply"
  where "Reply"."post_id" = "Post"."id"
  group by "Post"
) t
   , "User"
where ("Post")."user_id" = "User"."id"
;

group by "Post"でPostテーブルのレコードを１つの値として扱っているのがミソです。こういうふうに構造をネストさせるクエリが自在に書けるのがPostgresの素晴らしいところ。

結果は以下。

{
  "posts": [
    {
      "id": 1,
      "user": {
        "id": "hoge",
        "name": "ほげ"
      },
      "content": "今日は天下寿司を食べた",
      "replies": [
        {
          "id": 2,
          "message": "炙りサーモンある",
          "post_id": 1,
          "user_id": "bar"
        },
        {
          "id": 1,
          "message": "いいね！",
          "post_id": 1,
          "user_id": "foo"
        }
      ]
    },
    {
      "id": 2,
      "user": {
        "id": "foo",
        "name": "ふう"
      },
      "content": "ぬるぽ",
      "replies": [
        {
          "id": 3,
          "message": "ガッ",
          "post_id": 2,
          "user_id": "bar"
        }
      ]
    }
  ]
}

(結果は見やすいようにprettifyしています。)

ついでに、集約関数を使ってみたいので、postオブジェクトに返信の数を表すreply_countというフィールドを追加してみます。 7行目を以下のように変更します。

-  select jsonb_set(to_jsonb("Post"), '{replies}', jsonb_agg("Reply")) as "post"
+  select jsonb_set(jsonb_set(to_jsonb("Post"), '{replies}', jsonb_agg("Reply")), '{reply_count}', to_jsonb(count("Reply"."id"))) as "post"

実行結果は以下です。

{
  "posts": [
    {
      "id": 1,
      "user": {
        "id": "hoge",
        "name": "ほげ"
      },
      "content": "今日は天下寿司を食べた",
      "replies": [
        {
          "id": 2,
          "message": "炙りサーモンある",
          "post_id": 1,
          "user_id": "bar"
        },
        {
          "id": 1,
          "message": "いいね！",
          "post_id": 1,
          "user_id": "foo"
        }
      ],
      "reply_count": 2
    },
    {
      "id": 2,
      "user": {
        "id": "foo",
        "name": "ふう"
      },
      "content": "ぬるぽ",
      "replies": [
        {
          "id": 3,
          "message": "ガッ",
          "post_id": 2,
          "user_id": "bar"
        }
      ],
      "reply_count": 1
    }
  ]
}

SQL15行でここまで出来る、すごい！

まとめ

レコードをJSONに変換する場合はto_jsonbを使う
- 行を１つの値(ROW型)として扱えるところにPostgresの妙がある
Objectに値を追加する場合はjsonb_set、削除する場合は-を使う
複数のJSONB型を配列にするには集約関数jsonb_aggを使う

[1] ここでのORMとは、クエリを部品化して合成可能にする機能ではなく実行結果をModelと呼ばれるオブジェクトに移し替える機能のことを指しています。(例えばActiveRecordなど。)

以下はポエムなので適当に読み流して下さい。

RDBSのクエリって、保存された正規化されたテーブルをJOINして(=つまり非正規化して)データを見たい形/使いたい形に加工するためのものなので(参考:データベースの三層モデル) このスライドでも言及されてるけど、第一非正規なデータを返すことだって出来ていいはず。なぜ我々は正規化されたテーブルからjsonを作るのにRailsをかます必要があるのか？というのは以前から疑問に思っていたので、Postgres9.5のJSON関数に出会った時は強く感動した。

REST APIを書くときにサーバーサイドMVCは必要ないよね、っていうのは以前から思っていて、DBから取得したデータをわざわざModelオブジェクトにmappingして、クライアントに返すときにはシリアライズしているのは無駄に思える。RDBSから結果がHashのArrayで帰ってくるなら、それを引数として受け取って、データを操作する関数を書けばそのほうがシンプルにすむのに。そしてそれをそのままJSONに変換するだけでいいじゃん。

そういうわけで、JSON APIを書こうとした時に、データの加工という本来のDBの役割をPostgresはしっかり果たしてくれるわけです。ネストしたデータ構造だって自在に組み立てれるんです。不要なレイヤを挟まずにアーキテクチャをシンプルに保てるというのは素晴らしいことだと思うのです。

あと、Postgresのクエリの表現力はすごいというのを伝えたい。MySQL5.7にもJSON対応が入ったけど、上で紹介したようなことは出来ない。Postgresにはrow型が存在しているおかげでネストしたJSONを取得するクエリがシンプルに書ける。この記事では紹介出来てないけど、他にも多様なJSON操作関数があって、それが実現できているのはひとえにクエリの表現力のおかげ。LTした時のスライドで紹介したけど、ネストしたJSONをそのままjsonb型に突っ込んで、JSON内部のArrayに対して集約関数を実行するみたいなこともPostgreだからできる。

パフォーマンスの部分でMySQLにかなわない部分はあるとは思うけど、RDBの正当な進化の結果として現代的なJSONの機能を手にしており、ソースも常に読みやすくメンテナンスされており、Postgresはすごい。

以上、駄文失礼しました。

FOOBAR's Blog

PostgreSQL9.5の強力なJSON関数で、ネストした構造を持つJSONを自在に組み立てる

まとめ