[PostgreSQL] jsonb_build_object / jsonb_agg 로 행을 JSON 응답으로 조립하기

설치·접속: PostgreSQL 설치와 접속

부제: API 응답으로 내보낼 JSON을 애플리케이션에서 루프 돌며 조립하지 말고, SQL 한 방으로 원하는 모양(중첩 배열 포함)까지 만들어 받고 싶을 때

-- jsonb_build_object(키, 값, 키, 값 ...) — 행을 원하는 모양의 객체로
SELECT jsonb_build_object('uid', id, 'name', name, 'plan', settings->>'plan') FROM m4_users;
-- jsonb_agg — 여러 행을 배열 하나로 접는다
SELECT jsonb_agg(jsonb_build_object('id',id,'name',name)) AS users FROM m4_users;

 {"uid": 1, "name": "민수", "plan": "pro"}
 {"uid": 2, "name": "지영", "plan": "free"}
 {"uid": 3, "name": "현우", "plan": "pro"}

 [{"id": 1, "name": "민수"}, {"id": 2, "name": "지영"}, {"id": 3, "name": "현우"}]

jsonb_build_object는 키·값을 번갈아 넣어 한 행을 객체 하나로 만든다. 여기에 집계 함수 jsonb_agg를 씌우면 여러 행이 배열 하나로 접힌다. 이 둘을 겹치면 "객체들의 배열", 즉 REST API가 흔히 내보내는 그 모양이 SQL 안에서 완성된다. 함정: jsonb_agg는 집계 함수라 GROUP BY와 함께 쓰면 그룹별 배열이 되고, 그냥 쓰면 전체가 배열 하나로 뭉친다. 앱에서 N번 쿼리하고 코드로 붙이던 걸 DB가 한 번에 끝낸다.

이렇게도 쓴다

그룹별로 자식 목록 모으기 — 브랜드마다 상품명 배열.

SELECT attrs->>'brand' AS brand, jsonb_agg(name) AS items
FROM m4_products GROUP BY 1 ORDER BY 1;

 

행 전체를 손 안 대고 통째로 JSON으로 — to_jsonb(테이블별칭).

SELECT to_jsonb(u) FROM m4_users u WHERE id=1;

 

키-값 쌍을 객체로 모으기 — 상품명→가격 맵. (jsonb_object_agg)

SELECT jsonb_object_agg(name, (attrs->>'price')::int) FROM m4_products;

 

부모-자식 중첩 응답 조립 — 브랜드 요약 + 상품 배열. (조합: build + agg 중첩)

SELECT jsonb_build_object(
  'brand', attrs->>'brand', 'count', count(*),
  'products', jsonb_agg(jsonb_build_object('name',name,'price',(attrs->>'price')::int))
) FROM m4_products GROUP BY attrs->>'brand';

 

조건에 맞는 것만 배열에 담기 — FILTER 절 조합.

SELECT jsonb_agg(name) FILTER (WHERE (attrs->>'price')::int > 1000000) AS premium
FROM m4_products;

 

배열 원소를 정렬해서 담기 — 가격 높은 순. (조합: agg 내부 ORDER BY)

SELECT jsonb_agg(name ORDER BY (attrs->>'price')::int DESC) FROM m4_products;