PostgreSQL ARRAY_AGGでNULL値を除外する：上級者向けテクニック

2024-04-10

PostgreSQLでARRAY_AGGでNULL値を除外する方法

この問題を解決するには、以下の2つの方法があります。

FILTER句を使用すると、ARRAY_AGGで処理する前にNULL値を除外できます。

SELECT array_agg(DISTINCT column_name)
FROM table_name
WHERE column_name IS NOT NULL;

例

-- テーブル employees の "department_id" 列のNULL値を除外して配列にまとめる
SELECT array_agg(DISTINCT department_id)
FROM employees
WHERE department_id IS NOT NULL;

COALESCE関数を使用すると、NULL値を別の値に置き換えることができます。

SELECT array_agg(COALESCE(column_name, 'NULL'))
FROM table_name;

-- テーブル employees の "department_id" 列のNULL値を "Unknown" に置き換えて配列にまとめる
SELECT array_agg(COALESCE(department_id, 'Unknown'))
FROM employees;

NULL値を完全に除外したい場合は、FILTER句を使用します。
NULL値を別の値に置き換えて配列に含めたい場合は、COALESCE関数を使用します。

補足

PostgreSQL 9.1では、FILTER句は使用できません。この場合は、COALESCE関数を使用する必要があります。

テーブル employees

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  department_id INT
);

INSERT INTO employees (name, department_id) VALUES
  ('John Doe', 1),
  ('Jane Doe', NULL),
  ('Peter Smith', 2);

サンプルクエリ

-- FILTER句を使用する

SELECT array_agg(DISTINCT department_id)
FROM employees
WHERE department_id IS NOT NULL;

-- 結果:
-- {1, 2}

-- COALESCE関数を使用する

SELECT array_agg(COALESCE(department_id, 'Unknown'))
FROM employees;

-- 結果:
-- {1, Unknown, 2}

上記のサンプルコードは、PostgreSQL 9.1以降で使用できます。
DISTINCTキーワードを使用すると、重複する値を配列から除外できます。
ORDER BY句を使用すると、配列の要素を並べ替えることができます。

PostgreSQLでARRAY_AGGでNULL値を除外する方法

CASE式を使用する

SELECT array_agg(
  CASE
    WHEN column_name IS NOT NULL THEN column_name
  END)
FROM table_name;

-- テーブル employees の "department_id" 列のNULL値を除外して配列にまとめる
SELECT array_agg(
  CASE
    WHEN department_id IS NOT NULL THEN department_id
  END)
FROM employees;

LATERAL JOINを使用する

SELECT array_agg(t.column_name)
FROM table_name t
LEFT JOIN LATERAL (
  SELECT column_name
  FROM table_name
  WHERE column_name IS NOT NULL
) AS t2 ON TRUE;

-- テーブル employees の "department_id" 列のNULL値を除外して配列にまとめる
SELECT array_agg(t.department_id)
FROM employees t
LEFT JOIN LATERAL (
  SELECT department_id
  FROM employees
  WHERE department_id IS NOT NULL
) AS t2 ON TRUE;

WITH句を使用する

WITH t AS (
  SELECT column_name
  FROM table_name
  WHERE column_name IS NOT NULL
)
SELECT array_agg(t.column_name)
FROM t;

-- テーブル employees の "department_id" 列のNULL値を除外して配列にまとめる
WITH t AS (
  SELECT department_id
  FROM employees
  WHERE department_id IS NOT NULL
)
SELECT array_agg(t.department_id)
FROM t;