PostgreSQL で JSON を組み立てる

## まえおき
 
先日書いた [SQLQL](https://qiita.com/yancya/items/4b7979d83cbf6af9b819) という記事の中で

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

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

## この記事で説明する関数

### ROW_TO_JSON

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

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

### JSON_AGG

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

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

## ROW_TO_JSON

### ROW とは

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

```sql
SELECT ROW(1,2,3,4)
```

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

```sql
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 が返って来ます

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

### 型の付与

#### 型の定義

- PostgreSQL では[複合型](https://www.postgresql.jp/document/9.6/html/rowtypes.html)という名前付きのタプルを表す型を定義する事ができます

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

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

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

#### 特定の型へのキャスト

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

```sql
 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 が返って来ます

```sql
  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 で一発で取得する事ができたりします

### 例

```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)
```

```json
[
  {
    "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 でやりたくなる」の範囲が拡大してしまって困る