postgresql(13)でjson_aggをfilterする方法

postgresql(13)でjson_aggをfilterする

json_aggとjson_build_objectを利用して、postgresqlでjson化したデータをSELECTしようとした時にハマったのでメモ書き。

TL;DR

FILTERを利用してrelation先が存在がない場合に、nullを返却する。
返されたnullをCOALESCEで'[]'::jsonを利用してjsonのarray型で返却をする。

0. 想定テーブル

table.sql

CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE teams (
    team_id UUID PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);


CREATE TABLE team_users(
    team_id UUID NOT NULL,
    user_id UUID NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(user_id),
    FOREIGN KEY(team_id) REFERENCES teams(team_id),
    PRIMARY KEY(team_id, user_id)
);
-- Indexはサボった

1. 脳死で書いたSQL

team_usersが存在しない場合に、{"user_id" : null, "user_name" : null}として返却される。

not-working.sql

SELECT
    t.name,
    t.team_id,
    JSON_AGG(json_build_object(
        'user_id', tu.user_id,
        'user_name', u.user_name
    )) as users
FROM teams t
    LEFT OUTER JOIN team_users tu ON t.team_id = tu.user_id
        LEFT JOIN users u ON u.user_id = tu.user_id
WHERE t.team_id = 'b4d27fd8-8ad1-4468-8df9-6bb2279b96da'
    GROUP BY t.team_id;

return.json

[
  {
    "name": "team_1",
    "team_id": "b4d27fd8-8ad1-4468-8df9-6bb2279b96da",
    "users": [{"user_id" : null, "user_name" : null}]
  }
]

2. COALESCE使えばいいんだろ。と思ってダメだった

null => COALESCEだよなと思ったけど、そもそもjsonのkey-valueが入っているので、使えない。

not-good.sql

SELECT
    t.name,
    t.team_id,
    COALESCE(JSON_AGG(json_build_object(
        'user_id', tu.user_id,
        'user_name', u.name
    )), '[]'::json) as users
FROM teams t
    LEFT OUTER JOIN team_users tu ON t.team_id = tu.user_id
        LEFT JOIN users u ON u.user_id = tu.user_id
WHERE t.team_id = 'b4d27fd8-8ad1-4468-8df9-6bb2279b96da'
    GROUP BY t.team_id;

result.json

[
  {
    "name": "team_1",
    "team_id": "b4d27fd8-8ad1-4468-8df9-6bb2279b96da",
    "users": [{"user_id" : null, "user_name" : null}]
  }
]

3. FILTERを利用してきれいにする

with-filter.sql

SELECT
    t.name,
    t.team_id,
    COALESCE(JSON_AGG(json_build_object(
        'user_id', tu.user_id,
        'user_name', u.name
    )) FILTER(WHERE tu.user_id IS NOT NULL), '[]'::json) as users
FROM teams t
    LEFT OUTER JOIN team_users tu ON t.team_id = tu.user_id
        LEFT JOIN users u ON u.user_id = tu.user_id
WHERE t.team_id = 'b4d27fd8-8ad1-4468-8df9-6bb2279b96da'
    GROUP BY t.team_id;

result.json

[
  {
    "name": "team_1",
    "team_id": "b4d27fd8-8ad1-4468-8df9-6bb2279b96da",
    "users": []
  }
]

4. sub queryを利用したパターン

sub-query.sql

SELECT
    t.name,
    t.team_id,
    COALESCE((SELECT JSON_AGG(JSON_BUILD_OBJECT(
        'user_id', tu.user_id,
        'user_name', u.name
        )) from team_users tu
              INNER JOIN users u ON u.user_id = tu.user_id
              WHERE tu.team_id = t.team_id
    ), '[]'::json) as users
FROM teams t
WHERE t.team_id = 'b4d27fd8-8ad1-4468-8df9-6bb2279b96da';

他のやり方は思いつかない。。。(なんかあるのかな)