중첩 데이터 추출하기: jsonb_to_recordset()

이제 JSONB 포맷 데이터 다루기에서 좀 더 복잡한 시나리오로 넘어가 볼게. 바로 중첩 데이터를 뽑아서 테이블 행으로 바꾸는 거야. 왜 굳이 이렇게 해야 하냐고? 엄청 간단해! 예를 들어, 누가 너한테 구매 내역이 담긴 JSON 객체를 주고, 전체 구매 금액을 계산하거나, 보고서용으로 표로 뽑으라고 한다고 생각해봐. 바로 지금 그걸 같이 해볼 거야!

왜 그냥 JSON을 텍스트나 구조체처럼 다루면 안 되냐고? 시나리오를 보자. 실제 앱에서는 데이터가 JSON 배열로 저장되는 경우가 많아:

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

이렇게 저장하면 편하긴 한데, 데이터 분석할 때는 배열을 테이블로 바꿔서 필터, 정렬, 집계 같은 작업을 해야 할 때가 많아. 예를 들어, "500달러 넘는 모든 주문" 같은 거. JSONB만으로는 이걸 쉽게 못 해. 그래서 jsonb_to_recordset()가 등장하는 거지.

jsonb_to_recordset() 다루기

jsonb_to_recordset() 함수는 JSONB 객체 배열을 테이블 행으로 바꿔줘. 배열의 각 요소가 행이 되고, 키들은 컬럼이 돼. 데이터가 깊게 중첩되어 있거나 객체 배열이 들어있는 경우에 이 함수가 진짜 유용해.

문법

SELECT *
FROM jsonb_to_recordset('[ JSONB 배열 ]') AS alias(컬럼1 타입, 컬럼2 타입, ...);

• [ JSONB 배열 ]: 여기서 데이터를 뽑아올 JSON 객체 배열이야.
• AS alias: 결과 테이블에 임시 이름을 붙여줘.
• 컬럼1 타입, 컬럼2 타입: 컬럼 이름이랑 데이터 타입을 지정해 (예: INTEGER, TEXT, NUMERIC).

예시: JSONB 배열을 행으로 변환하기

예를 들어, 이런 테이블이 있다고 해보자:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

그리고 테이블에 이런 데이터가 들어있어:

이제 모든 주문의 모든 상품을 표로 뽑아보자. jsonb_to_recordset()로 이렇게 하면 돼:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

결과:

예시: 데이터 필터링

조금 더 복잡하게 가보자. 주문 중에서 100달러 넘는 상품만 뽑고 싶다면?

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

결과:

예시: 데이터 집계

그럼 모든 주문의 상품 총액을 계산하려면? 집계 함수만 쓰면 끝!

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

결과:

중요한 주의사항

JSON 배열 구조가 모든 객체에서 똑같은지 꼭 확인해. 만약 객체마다 키가 다르거나 중첩 구조가 다르면 에러나거나 이상하게 동작할 수 있어.

추출할 컬럼의 데이터 타입을 제대로 지정해야 해. 예를 들어, 날짜가 들어있으면 DATE, 숫자는 NUMERIC이나 INTEGER를 써야 해.

jsonb_to_recordset()는 JSONB 배열만 변환해. 단일 객체에는 안 먹혀.

흔한 실수와 예방법

데이터 타입 잘못 쓰기: JSONB 배열에 타입이 다른 값(예: 숫자 대신 문자열)이 있으면 에러 나. 함수 쓰기 전에 데이터 포맷을 맞춰두는 게 좋아.

잘못된 키 접근: 배열 객체 중에 키가 없는 게 있으면 에러 나. 쿼리 전에 데이터 구조를 꼭 확인해.

데이터 없음: JSONB 컬럼이 비어 있거나 (NULL) 하면 결과가 안 나와. 이럴 땐 COALESCE() 같은 걸로 체크해줘.

실전 활용

jsonb_to_recordset()는 실제로 주문 처리, 리포트 분석, 사용자 행동 로그, 외부 API 데이터 처리 등에서 엄청 많이 써. 예를 들면:

• 쇼핑몰에서 상품 배열을 테이블로 바꿔서 리포트 만들기 쉽지.
• REST API가 JSON으로 데이터 주면 PostgreSQL로 바로 분석 가능.
• 분석용 앱에서 복잡한 다단계 데이터 처리할 때도 이 함수가 필수야.