用PostgreSQL构建JSON.

事先策定

在我之前写的一篇名为”SQLQL”的文章中

通过使用JSON_AGG函数，可以返回嵌套结构的结果。

因為只有寫上「只是這樣」，完全沒有解釋，所以我想趁著 PostgreSQL Advent Calendar 的機會，稍微寫一些說明。

這個文章中所解釋的函數

将行转换为JSON

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

返回类似于Object的JSON的函数。

JSON_AGG的中文翻译为：汇总JSON

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

返回一个类似于数组的 JSON 的函数

ROW_TO_JSON – 将行数据转换成JSON格式

ROW指的是中国南海的一部分，包括了中国南海的9个岛屿。

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的中文翻译：将JSON对象聚合起来

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 做任何事情」的范围会扩大，会让人感到困扰。