PostgreSQL LEFT JOIN 뒤 json_agg가 빈 배열 대신 [null]을 반환한다

PostgreSQL에서 부모별 자식 목록을 LEFT JOIN과 json_agg(c)로 만들면, 자식이 없는 부모에 [] 대신 [null]이 붙을 수 있다. LEFT JOIN이 남긴 한 행이 집계에 들어가기 때문이다. 자식이 없는 행을 집계 입력에서 제외하고, 그 결과가 SQL NULL이면 빈 JSON 배열로 바꾼다.

API 쿼리에서 바꿀 부분은 아래의 FILTER와 집계 바깥의 COALESCE다. parent와 child는 자신의 부모·자식 테이블로, c.id는 자식의 NULL 불가 기본 키로 바꾼다. 이 예제에서 c는 자식 행 전체이므로 JSON 객체에 자식 테이블의 모든 열이 들어간다.

SELECT p.id AS parent_id,
       coalesce(
           json_agg(c ORDER BY c.id) FILTER (WHERE c.id IS NOT NULL),
           '[]'::json
       ) AS children
FROM parent p LEFT JOIN child c ON c.parent_id = p.id
GROUP BY p.id ORDER BY p.id;

[null]은 원소가 하나인 JSON 배열이다. SQL NULL이 아니므로 coalesce(json_agg(c), '[]'::json)만으로는 바뀌지 않는다. 아래 비교는 실제 서비스 장애 기록이 아니라, 새 전용 스키마에 합성 부모·자식 행을 넣어 PostgreSQL 18.6에서 실행한 결과다.

자식 수와 조인 결과의 행 수를 따로 본다

부모 1에는 자식이 없고, 부모 2에는 자식 21 하나가 있다. 부모 3에는 자식 31과 32가 있으며 32의 label은 NULL이다. 부모 4에는 label이 NULL인 자식 41 하나만 있다. 같은 입력을 직접 만들 SQL은 글 아래에 있다.

자기 쿼리를 진단할 때는 같은 조인 조건으로 입력 행 수, 자식 기본 키의 개수, 집계할 값을 함께 조회한다. 다음 조회는 자식 ID 배열과 내용 배열이 왜 다르게 보이는지 확인한다.

SELECT p.id AS parent_id, count(*) AS joined_rows,
       count(c.id) AS child_rows, count(c.label) AS labeled_rows,
       json_agg(c.id ORDER BY c.id) AS ids,
       json_agg(c.label ORDER BY c.id) AS labels
FROM parent p LEFT JOIN child c ON c.parent_id = p.id
GROUP BY p.id ORDER BY p.id;

열을 |로 구분한 실제 출력이다.

parent_id|joined_rows|child_rows|labeled_rows|ids|labels
1|1|0|0|[null]|[null]
2|1|1|1|[21]|["alpha"]
3|2|2|1|[31, 32]|["beta", null]
4|1|1|0|[41]|[null]
(4 rows)

부모 1의 child_rows는 0인데 joined_rows는 1이다. 일치하는 자식이 없으면 LEFT JOIN이 자식 쪽 열을 NULL로 채운 행을 남긴다. 자식 테이블의 기본 키가 NULL 불가여도, 조인 결과에는 이 NULL이 생긴다. 이때 ‘자식 없음’은 정확히는 현재 ON 조건에 맞는 자식이 없다는 뜻이다. PostgreSQL 18 외부 조인 설명↗ (새 탭에서 열림)

count(*)는 조인 결과의 행을 세고, count(c.id)는 ID가 NULL이 아닌 행을 센다. 반면 json_agg는 NULL 입력도 배열 원소에 포함한다. 따라서 부모 1의 ID 배열은 빈 입력을 집계한 결과가 아니라, NULL 하나를 집계한 [null]이다. 자식 행 전체를 넣은 json_agg(c)도 이번 실행에서 [null]을 반환했다. 집계 함수의 NULL 처리↗ (새 탭에서 열림)

첫 쿼리의 FILTER (WHERE c.id IS NOT NULL)은 이 집계가 받을 행만 고른다. 부모 1의 그룹은 유지하면서 JSON 집계 입력은 0행이 된다. FILTER만 적용한 실행에서는 그 부모의 집계 결과가 SQL NULL이었다. 집계 바깥의 COALESCE가 이 값을 []로 바꿨다. FILTER의 입력 선택 규칙은 집계식 문법↗ (새 탭에서 열림), 첫 번째 NULL 아닌 값을 고르는 동작은 COALESCE 문서↗ (새 탭에서 열림)에서 확인할 수 있다.

첫 쿼리의 실제 결과는 다음과 같다. 부모 1은 남아 있고 배열만 비었다.

parent_id|children
1|[]
2|[{"id":21,"parent_id":2,"label":"alpha"}]
3|[{"id":31,"parent_id":3,"label":"beta"}, 
 {"id":32,"parent_id":3,"label":null}]
4|[{"id":41,"parent_id":4,"label":null}]
(4 rows)

내용이 NULL인 실제 자식은 남겨야 한다

부모 1과 부모 4의 labels는 모두 [null]이었다. 하지만 부모 4에는 자식 41이 있다. 배열에 null이 있다는 이유만으로 원소를 없애면 실제 데이터를 버릴 수 있다. 자식의 존재 여부는 nullable 내용 열이 아니라 NULL 불가 식별자로 판별한다.

같은 입력에서 FILTER를 WHERE c.label IS NOT NULL로 바꾸자 부모 3의 자식 32가 빠졌고, 부모 4의 자식 ID 배열은 []가 됐다. 기본 키로 거른 첫 쿼리에서는 두 자식이 모두 유지됐다. 내용만 모으는 배열이 필요하다면 부모 4의 [null]은 이 데이터에서 올바른 결과일 수 있다.

또한 집계의 FILTER 대신 조회 전체에 WHERE c.id IS NOT NULL을 추가하면 부모 1 자체가 사라진다. 실제 비교에서도 부모 2·3·4만 반환됐다. 자식 없는 부모를 빈 배열과 함께 유지하려는 목적에는 집계의 FILTER가 맞는다.

API에서 필요한 열만 골라 json_build_object('id', c.id, 'label', c.label)를 집계해도 같은 ID FILTER를 붙인다. 이번 실행에서 FILTER 없는 부모 1의 결과는 [{"id" : null, "label" : null}]이었다. NULL 필드를 가진 객체가 만들어진 것이므로, 객체 생성이나 집계 결과의 COALESCE만으로는 빈 배열이 되지 않는다. JSON 객체 생성 함수↗ (새 탭에서 열림)

반환 타입과 배열 순서를 함께 정한다

위 쿼리는 json_agg와 '[]'::json을 써서 json을 반환한다. jsonb가 필요한 쿼리에서는 집계를 jsonb_agg로, 기본값도 '[]'::jsonb로 맞춘다. 같은 자료에서 pg_typeof로 확인한 두 결과 타입은 각각 json과 jsonb였고, 자식의 포함 여부는 같았다. 집계 함수의 반환 타입↗ (새 탭에서 열림)

배열 원소의 순서는 json_agg(c ORDER BY c.id) 안에서 정했다. 부모 3의 자식을 32, 31 순서로 삽입했어도 두 집계 모두 배열에는 31, 32 순서로 담았다. 바깥의 ORDER BY p.id는 부모 결과 행의 순서를 정한다. 배열 순서까지 보장하는 구문은 아니다. 문서도 JSON 집계의 기본 입력 순서를 보장하지 않으므로, API 배열에 순서가 필요하면 집계 안에 ORDER BY를 지정하도록 설명한다. 날짜로 정렬한다면 같은 날짜끼리도 순서가 정해지도록 고유 ID를 보조 기준으로 넣는다. 집계 내부 ORDER BY↗ (새 탭에서 열림)

같은 입력으로 직접 비교하려면

PostgreSQL 18과 psql 또는 SQL 편집기, 스키마를 만들 수 있는 폐기 가능한 테스트 DB가 필요하다. 아래 입력으로 새 스키마와 네 부모의 조건을 만든 뒤, 앞의 두 SELECT를 같은 연결에서 실행하면 된다. lj_json_aggregate_lab이라는 스키마가 이미 있다면 기존 것을 지우지 말고 사용하지 않는 새 이름으로 바꾼다.

BEGIN;
CREATE SCHEMA lj_json_aggregate_lab;
SET LOCAL search_path = lj_json_aggregate_lab, pg_catalog;
CREATE TABLE parent (id integer PRIMARY KEY);
CREATE TABLE child (
    id integer PRIMARY KEY,
    parent_id integer NOT NULL REFERENCES parent(id),
    label text
);
INSERT INTO parent VALUES (1), (2), (3), (4);
INSERT INTO child VALUES
    (21, 2, 'alpha'), (32, 3, NULL), (31, 3, 'beta'), (41, 4, NULL);

비교 후 같은 연결에서 ROLLBACK;을 실행하면 이 트랜잭션에서 만든 스키마·테이블·행이 되돌아간다. 자동 커밋 여부와 별개로 위의 BEGIN부터 ROLLBACK까지 한 연결을 유지해야 한다.

자기 쿼리에서 FILTER 적용 뒤에도 뜻밖의 원소가 남는다면, 먼저 해당 부모 하나에 대해 집계 전의 p.id, c.id, 내용 열을 조회한다. 다른 조인이 자식 행을 중복시키는 경우까지 이 FILTER가 제거하지는 않는다. 이번에 검증한 범위는 PostgreSQL 18.6의 부모·자식 두 테이블, 0개·1개·여러 자식과 nullable 내용, JSON/JSONB 반환값이다. 실제 API 드라이버의 변환, 다중 조인의 중복, 실행 계획과 성능, 다른 PostgreSQL 버전은 검증하지 않았다.