[PostgreSQL] jsonb_to_recordset — JSON 객체 배열을 관계형 행으로 펼쳐 집계한다

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

부제: 외부 API 응답을 통째로 JSONB에 저장했는데, 그 안의 주문 라인 배열을 ETL이나 임시 테이블 없이 그 자리에서 행으로 펼쳐 GROUP BY 하고 싶을 때

jsonb_to_recordset 은 JSON 객체 배열을 타입 지정 SQL 행 집합으로 바꾼다. AS x(col type) 로 스키마를 즉석에서 붙이면, JSONB 안에 갇혀 있던 배열이 일반 테이블처럼 조인·집계 대상이 된다. 컬럼 정의 AS x(...) 는 필수다(빼면 에러).

CREATE TABLE api (id int primary key, resp jsonb);
INSERT INTO api VALUES
 (1, '{"order":"o1","lines":[{"cat":"book","qty":2,"price":15},{"cat":"pen","qty":5,"price":2}]}'),
 (2, '{"order":"o2","lines":[{"cat":"book","qty":1,"price":30},{"cat":"book","qty":3,"price":12}]}'),
 (3, '{"order":"o3","lines":[{"cat":"pen","qty":10,"price":1},{"cat":"mug","qty":1,"price":8}]}');

-- JSON 객체 배열을 행으로 펼쳐 카테고리별 집계
SELECT x.cat,
       sum(x.qty)           AS total_qty,
       sum(x.qty * x.price) AS revenue
FROM api,
     jsonb_to_recordset(resp->'lines') AS x(cat text, qty int, price numeric)
GROUP BY x.cat
ORDER BY revenue DESC;

 cat  | total_qty | revenue
------+-----------+---------
 book |         6 |      96
 pen  |        15 |      20
 mug  |         1 |       8
(3 rows)

resp->'lines' 로 배열을 꺼내 jsonb_to_recordset 에 넘기면, 각 객체가 x(cat text, qty int, price numeric) 스키마의 행이 된다. FROM 절에서 api 와 콤마 조인(암묵 LATERAL)돼 세 주문의 라인이 전부 펼쳐지고, 그 위에 평범한 GROUP BY x.cat 이 걸린다. book은 세 라인(2·1·3개) 합쳐 revenue 96이다. 함정은 AS x(...) 컬럼 정의를 반드시 붙여야 한다는 것과, 객체가 아니라 스칼라 배열이면 jsonb_array_elements 를 쓴다는 점이다.

이렇게도 쓴다

명시적 LATERAL로 각 행의 배열을 펼친다. 콤마 조인과 동작은 같지만 의도가 드러난다. (조합: LATERAL)

SELECT s.id, x.cat, x.qty
FROM api s, LATERAL jsonb_to_recordset(s.resp->'lines') AS x(cat text, qty int, price numeric);

 

jsonb_to_record는 배열이 아니라 객체 하나를 행으로 편다. 최상위 필드를 컬럼으로.

SELECT r.order FROM api, jsonb_to_record(resp) AS r("order" text, lines jsonb);

 

jsonb_array_elements는 스키마 없이 배열 요소를 jsonb 그대로 편다. 스칼라 배열이나 이질적 구조에.

SELECT id, jsonb_array_elements(resp->'lines') AS line FROM api;

 

펼친 뒤 WHERE로 필터링해 특정 라인만 집계한다.

SELECT x.cat, sum(x.qty) FROM api,
  jsonb_to_recordset(resp->'lines') AS x(cat text, qty int, price numeric)
WHERE x.price >= 10 GROUP BY x.cat;

 

json_to_recordset은 jsonb 대신 text json 입력에 쓴다. 동작은 동일.

SELECT * FROM json_to_recordset('[{"a":1},{"a":2}]') AS x(a int);

 

jsonb_populate_recordset은 이미 정의된 테이블 타입에 배열을 매핑한다. AS x(...) 대신 행 타입 재사용.

SELECT * FROM jsonb_populate_recordset(null::api, '[]'::jsonb);