PostgreSQL LEFT JOIN json_agg() ignore/remove NULL
PostgreSQL LEFT JOIN json_agg() で NULL 値を除外する方法
PostgreSQL の LEFT JOIN と json_agg() 関数を組み合わせて、結合結果の JSON 配列から NULL 値を除外する方法について解説します。
問題
LEFT JOIN を使用してテーブルを結合する場合、結合条件に一致しない行は結果に含まれませんが、JSON 配列として集計される場合、NULL 値として含まれてしまうことがあります。
解決策
以下の方法で NULL 値を除外できます。
方法 1: COALESCE と FILTER を使用する
COALESCE関数を使用して、NULL 値を空の JSON 配列[]に置き換えます。FILTER式を使用して、user_idが NULL ではない行のみを処理します。
SELECT C.id, C.name,
COALESCE(json_agg(E) FILTER (WHERE E.user_id IS NOT NULL), '[]') AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;
方法 2: jsonb_agg() 関数を使用する
PostgreSQL 12 以降では、jsonb_agg() 関数を使用して、NULL 値を自動的に除外できます。
SELECT C.id, C.name,
jsonb_agg(E) AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;
方法 3: CASE 式を使用する
CASE 式を使用して、NULL 値の場合とそうでない場合で処理を分岐できます。
SELECT C.id, C.name,
(CASE WHEN COUNT(E.user_id) > 0 THEN json_agg(E) ELSE '[]' END) AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;
上記の方法のいずれかを使用して、LEFT JOIN と json_agg() 関数で結合結果の JSON 配列から NULL 値を除外できます。
-- テーブル定義
CREATE TABLE contacts (
id INT PRIMARY KEY,
name TEXT
);
CREATE TABLE emails (
id INT PRIMARY KEY,
user_id INT,
email TEXT
);
-- データ挿入
INSERT INTO contacts (id, name) VALUES (1, 'John Doe'), (2, 'Jane Doe');
INSERT INTO emails (id, user_id, email) VALUES (1, 1, '[email protected]'), (2, 2, '[email protected]'), (3, 2, '[email protected]');
-- LEFT JOIN と json_agg() を使用して JSON 配列を生成
SELECT C.id, C.name,
COALESCE(json_agg(E) FILTER (WHERE E.user_id IS NOT NULL), '[]') AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;
結果
[
{
"id": 1,
"name": "John Doe",
"emails": [
"[email protected]"
]
},
{
"id": 2,
"name": "Jane Doe",
"emails": [
"[email protected]",
"[email protected]"
]
}
]
このサンプルコードでは、COALESCE 関数と FILTER 式を使用して、emails 配列から NULL 値を除外しています。
上記のサンプルコード以外にも、jsonb_agg() 関数や CASE 式を使用して、NULL 値を除外することができます。
LEFT JOIN と json_agg() で NULL 値を除外するその他の方法
json_strip_nulls() 関数は、JSON オブジェクトまたは配列から NULL 値とそのキーを削除します。
SELECT C.id, C.name,
json_strip_nulls(json_agg(E)) AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;
方法 5: LATERAL JOIN を使用する
LATERAL JOIN を使用すると、LEFT JOIN の結果をサブクエリとして処理できます。
SELECT C.id, C.name,
(
SELECT json_agg(E.email)
FROM LATERAL (
SELECT E.email
FROM emails E
WHERE E.user_id = C.id
) AS E
) AS emails
FROM contacts C
GROUP BY C.id, C.name
ORDER BY C.id;
方法 6: DISTINCT ON を使用する
DISTINCT ON を使用すると、重複する行を排除できます。
SELECT C.id, C.name,
json_agg(DISTINCT ON (E.email) E.email) AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;
postgresql
