PostgreSQL 17은 데이터 처리 능력을 대폭 강화했습니다. 특히 MERGE 문의 RETURNING 절 지원과 JSON_TABLE 함수는 실무에서 자주 마주치는 복잡한 작업을 SQL 한 줄로 해결할 수 있게 합니다. 이 글에서는 두 기능의 실전 활용법을 상세히 다룹니다.

MERGE 문 강화: RETURNING 절

PostgreSQL 17에서 MERGE 문에 RETURNING 절이 추가되어, UPSERT 후 영향받은 행의 데이터를 즉시 가져올 수 있습니다.

기본 MERGE 문법 복습

-- 기본 MERGE: 있으면 UPDATE, 없으면 INSERT
MERGE INTO products AS target
USING (
    VALUES
        ('SKU001', '무선 키보드', 45000),
        ('SKU002', '게이밍 마우스', 89000),
        ('SKU003', 'USB-C 허브', 32000)
) AS source(sku, name, price)
ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET
        name = source.name,
        price = source.price,
        updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (sku, name, price, created_at)
    VALUES (source.sku, source.name, source.price, NOW());

RETURNING 절 활용

-- RETURNING으로 처리 결과 즉시 확인
MERGE INTO products AS target
USING new_products AS source
ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET price = source.price, updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (sku, name, price, created_at)
    VALUES (source.sku, source.name, source.price, NOW())
RETURNING
    target.id,
    target.sku,
    target.name,
    target.price,
    merge_action() AS action;  -- 'INSERT' 또는 'UPDATE' 반환

-- 결과:
-- id  | sku    | name         | price | action
-- 1   | SKU001 | 무선 키보드   | 45000 | UPDATE
-- 15  | SKU003 | USB-C 허브   | 32000 | INSERT

CTE와 결합한 고급 패턴

-- MERGE 결과를 CTE로 받아 후속 처리
WITH merged AS (
    MERGE INTO inventory AS target
    USING incoming_stock AS source
    ON target.product_id = source.product_id
    WHEN MATCHED THEN
        UPDATE SET
            quantity = target.quantity + source.quantity,
            last_restock = NOW()
    WHEN NOT MATCHED THEN
        INSERT (product_id, quantity, last_restock)
        VALUES (source.product_id, source.quantity, NOW())
    RETURNING target.product_id, target.quantity, merge_action() AS action
)
INSERT INTO inventory_log (product_id, new_quantity, action_type, logged_at)
SELECT product_id, quantity, action, NOW()
FROM merged;

JSON_TABLE: JSON을 관계형 테이블로 변환

SQL/JSON 표준의 JSON_TABLE 함수를 사용하면 JSON 데이터를 SQL 테이블처럼 조회할 수 있습니다.

기본 문법

-- JSON 배열을 테이블 행으로 변환
SELECT jt.*
FROM JSON_TABLE(
    '[{"id": 1, "name": "Alice", "age": 30},
      {"id": 2, "name": "Bob", "age": 25},
      {"id": 3, "name": "Charlie", "age": 35}]'::jsonb,
    '$[*]'
    COLUMNS (
        id      INTEGER  PATH '$.id',
        name    TEXT     PATH '$.name',
        age     INTEGER  PATH '$.age'
    )
) AS jt;

-- 결과:
-- id | name    | age
-- 1  | Alice   | 30
-- 2  | Bob     | 25
-- 3  | Charlie | 35

중첩 JSON 처리

-- 주문 데이터에서 주문 상품 목록 추출
SELECT o.order_id, items.*
FROM orders o,
JSON_TABLE(
    o.order_data,
    '$.items[*]'
    COLUMNS (
        row_num    FOR ORDINALITY,
        product_id INTEGER  PATH '$.productId',
        name       TEXT     PATH '$.name',
        quantity   INTEGER  PATH '$.qty',
        price      NUMERIC  PATH '$.price',
        subtotal   NUMERIC  PATH '$.price' -- 계산용
    )
) AS items
WHERE o.status = 'completed';

NESTED PATH로 다단계 중첩 처리

-- 복잡한 중첩 JSON 구조 처리
SELECT dept.*, emp.*
FROM company_data cd,
JSON_TABLE(
    cd.structure,
    '$.departments[*]'
    COLUMNS (
        dept_name TEXT PATH '$.name',
        budget    NUMERIC PATH '$.budget',
        NESTED PATH '$.employees[*]' COLUMNS (
            emp_name   TEXT    PATH '$.name',
            emp_role   TEXT    PATH '$.role',
            emp_salary NUMERIC PATH '$.salary'
        )
    )
) AS dept;

-- 결과:
-- dept_name | budget  | emp_name | emp_role    | emp_salary
-- 개발팀    | 5000000 | 김개발   | 시니어      | 6000
-- 개발팀    | 5000000 | 이주니어 | 주니어      | 3500
-- 기획팀    | 3000000 | 박기획   | PM          | 5500

기본값과 에러 처리

SELECT jt.*
FROM JSON_TABLE(
    '{"users": [{"name": "Alice"}, {"name": "Bob", "email": "bob@test.com"}]}'::jsonb,
    '$.users[*]'
    COLUMNS (
        name  TEXT PATH '$.name',
        email TEXT PATH '$.email'
            DEFAULT '미등록' ON EMPTY
            DEFAULT '오류' ON ERROR,
        idx   FOR ORDINALITY
    )
) AS jt;

-- 결과:
-- name  | email         | idx
-- Alice | 미등록        | 1
-- Bob   | bob@test.com  | 2

실전 활용: API 응답 데이터 분석

-- api_responses 테이블에 저장된 JSON 응답 분석
CREATE VIEW parsed_api_data AS
SELECT
    ar.request_id,
    ar.endpoint,
    ar.requested_at,
    items.product_name,
    items.category,
    items.rating,
    items.review_count
FROM api_responses ar,
JSON_TABLE(
    ar.response_body,
    '$.data.products[*]'
    COLUMNS (
        product_name  TEXT    PATH '$.title',
        category      TEXT    PATH '$.category',
        rating        NUMERIC PATH '$.avgRating',
        review_count  INTEGER PATH '$.reviews'
    )
) AS items
WHERE ar.status_code = 200;

-- 뷰를 통한 집계 쿼리
SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(rating), 2) AS avg_rating,
    SUM(review_count) AS total_reviews
FROM parsed_api_data
GROUP BY category
ORDER BY avg_rating DESC;

인덱스 최적화

-- JSONB 컬럼에 GIN 인덱스
CREATE INDEX idx_orders_data ON orders USING GIN (order_data jsonb_path_ops);

-- 특정 JSON 경로에 대한 인덱스
CREATE INDEX idx_orders_status ON orders (
    (order_data->>'status')
);

-- JSON_TABLE과 함께 사용할 때 WHERE 조건 최적화
SELECT items.*
FROM orders o,
JSON_TABLE(o.order_data, '$.items[*]' COLUMNS (
    name TEXT PATH '$.name',
    price NUMERIC PATH '$.price'
)) AS items
WHERE o.order_data @> '{"status": "shipped"}'  -- 인덱스 활용
  AND items.price > 10000;

PostgreSQL 17의 MERGE RETURNING과 JSON_TABLE은 복잡한 애플리케이션 로직을 SQL 레벨로 내릴 수 있게 해주어 코드 복잡도를 크게 줄여줍니다. 특히 JSON 기반 API를 다루는 백엔드 개발에서 JSON_TABLE의 활용 가치가 높습니다.