PostgreSQL で JSON を組み立てる

まえおき

先日書いた SQLQL という記事の中で

JSON_AGG 関数を使う事で、入れ子構造の結果を返すことができている

とだけ書いていて全く説明していなかったので PostgreSQL アドベントカレンダーの機会に少し説明を書いておこうかなという次第です

この記事で説明する関数

ROW_TO_JSON

{"id":1,"name":"taro"}

みたいな Object っぽい JSON を返す関数

JSON_AGG

[{"id":1,"name":"taro"},{"id":2,"name":"jiro"}]

みたいな Array になってる JSON を返す関数

ROW_TO_JSON

ROW とは

• ROW_TO_JSON の ROW は行と言ってもいいですが、実態としてはタプル（組）の事だと思っていいと思います
• マニュアルには「複合値」という名前で登場します
• 下のような式で生成できます

SELECT ROW(1,2,3,4)

• 式の中に2つ以上のフィールドがある場合には、ROWキーワードは実際には省略することができます
• フィールドが 1 つだけだと、単一の値になってしまいます

SELECT (1,2,3,4); --> (1,2,3,4)
SELECT (1); --> 1
SELECT ROW(1); --> (1)

ROW_TO_JSON の簡単な例

• この例のように無名のタプルを ROW_TO_JSON に食わせると、それぞれの値について f1, f2, f3, f4 のような連番の名前が自動的に与えられた Object の JSON が返って来ます

SELECT ROW_TO_JSON(ROW(1,2,3,4));
--           row_to_json
-- -------------------------------
--  {"f1":1,"f2":2,"f3":3,"f4":4}
-- (1 row)

型の付与

型の定義

• PostgreSQL では複合型という名前付きのタプルを表す型を定義する事ができます

CREATE TYPE hoge_numbers as (
            n1 integer
          , n2 integer
          , n3 integer
          , n4 integer);

• なお、テーブルを生成する時には、テーブルの行型を表現するために、テーブル名と同じ名前の複合型も自動的に生成されます

CREATE TABLE hoge_numbers(
             n1 integer
           , n2 integer
           , n3 integer
           , n4 integer);

特定の型へのキャスト

• 型の組み合わせが合っていれば、(1,2,3,4)::hoge_numbers といった感じで無名のタプルを名前付きの型へとキャストする事ができます
• 名前付きの型にキャストされたタプルを ROW_TO_JSON の引数として与えると、ちゃんと各項目に意図した名前がついた Object の JSON が返って来ます

 SELECT ROW_TO_JSON((1,2,3,4)::hoge_numbers);
--           row_to_json
-- -------------------------------
--  {"n1":1,"n2":2,"n3":3,"n4":4}
-- (1 row)

JSON_AGG

AGG とは

• AGG は aggregate の略だと思いますが、要するに SQL における SUM() とか COUNT()の仲間と考えて差し支えありません
• 単体でも使えますが、SELECT する中で、一部の値にだけ適用したい場合には GROUP BY と組み合わせる必要があります

例

• この例では WITH で作ったインラインの "user" テーブルの行をそのまま JSON_AGG() の引数に入れる事で、各行に ROW_TO_JSON() を適用した結果を Array に aggregate したような JSON が返って来ます

  WITH "user"("id", "name") AS (
VALUES (1, 'taro')
     , (2, 'jiro'))
     
SELECT JSON_AGG("user")
  FROM "user";
  
--            json_agg
-- ---------------------------
--  [{"id":1,"name":"taro"}, +
--   {"id":2,"name":"jiro"}]
-- (1 row)

応用

• ROW_TO_JSON() と JSON_AGG() とを組み合わせると、複雑にネストした構造を持つオブジェクトを SQL で一発で取得する事ができたりします

例

    WITH "user"("id", "name") AS (
  VALUES (1, 'taro')
       , (2, 'jiro')
       , (3, 'sabu')
       , (4, 'siro'))

       , "friendship"("from", "to") AS (
  VALUES (1, 2), (1, 3), (1, 4)
       , (2, 3)
       , (3, 1), (3, 2), (3, 4))

       , "t" AS (
  SELECT "me"."id"
       , "me"."name"
       , ARRAY_AGG("friend") AS friends
    FROM "user" AS "me"
    JOIN "friendship" ON "me"."id" = "from"
    JOIN "user" AS "friend" ON "friend"."id" = "to"
GROUP BY 1, 2)

  SELECT JSON_AGG("t") AS "data"
    FROM "t"
;
-- data
-- --------------------------------------------------------------
-- [{"id":1,"name":"taro","friends":[{"id":2,"name":"jiro"},   +
-- {"id":3,"name":"sabu"},                                    +
-- {"id":4,"name":"siro"}]},                                  +
-- {"id":2,"name":"jiro","friends":[{"id":3,"name":"sabu"}]}, +
-- {"id":3,"name":"sabu","friends":[{"id":1,"name":"taro"},   +
-- {"id":2,"name":"jiro"},                                    +
-- {"id":4,"name":"siro"}]}]
-- (1 row)

[
  {
    "id":1,
    "name":"taro",
    "friends":[
      {"id":2,"name":"jiro"},
      {"id":3,"name":"sabu"},
      {"id":4,"name":"siro"}
    ]
  },
  {
    "id":2,
    "name":"jiro",
    "friends":[
      {"id":3,"name":"sabu"}
    ]
  },
  {
    "id":3,
    "name":"sabu",
    "friends":[
      {"id":1,"name":"taro"},
      {"id":2,"name":"jiro"},
      {"id":4,"name":"siro"}
    ]
  }
]

まとめ

わからんけど、こういう道具があると「なんでも SQL でやりたくなる」の範囲が拡大してしまって困る