PostgreSQL 17과 SQL/JSON

PostgreSQL 17은 SQL:2023 표준의 SQL/JSON 기능을 대폭 강화했다. 핵심 추가사항은 JSON_TABLE() 함수로, JSON 데이터를 관계형 테이블로 변환할 수 있다. 기존 json_each, jsonb_array_elements 등을 복잡하게 조합하던 패턴을 하나의 표준 SQL 구문으로 대체한다.

JSON_TABLE 기본 문법

-- JSON_TABLE 기본 구조
SELECT jt.*
FROM JSON_TABLE(
    json_data,           -- JSON 소스
    json_path            -- 행을 추출할 JSONPath
    COLUMNS (
        column_name type PATH json_path_expr  -- 컬럼 정의
    )
) AS jt;

실전 예제 1: API 응답 파싱

-- API에서 받은 사용자 목록 JSON 파싱
SELECT users.*
FROM JSON_TABLE(
    $${
      "status": "ok",
      "data": [
        {"id": 1, "name": "김철수", "email": "[email protected]", "age": 30},
        {"id": 2, "name": "이영희", "email": "[email protected]", "age": 25},
        {"id": 3, "name": "박민수", "email": "[email protected]", "age": 35}
      ]
    }$$::jsonb,
    $$$.data[*]$$         -- data 배열의 각 요소를 행으로
    COLUMNS (
        id      integer  PATH $$$.id$$,
        name    text     PATH $$$.name$$,
        email   text     PATH $$$.email$$,
        age     integer  PATH $$$.age$$
    )
) AS users;

-- 결과:
--  id |  name  |     email     | age
-- ----+--------+---------------+-----
--   1 | 김철수  | [email protected]   |  30
--   2 | 이영희  | [email protected]   |  25
--   3 | 박민수  | [email protected]   |  35

실전 예제 2: 테이블의 JSONB 컬럼 파싱

-- orders 테이블에 items JSONB 컬럼이 있는 경우
CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    customer text,
    items jsonb
);

INSERT INTO orders VALUES
(1, "홍길동", $$[
    {"product": "노트북", "qty": 1, "price": 1500000},
    {"product": "마우스", "qty": 2, "price": 35000}
]$$),
(2, "장보고", $$[
    {"product": "키보드", "qty": 1, "price": 89000}
]$$);

-- 주문별 상품 상세 내역 조회
SELECT o.order_id, o.customer,
       items.product, items.qty, items.price,
       items.qty * items.price AS total
FROM orders o,
     JSON_TABLE(
         o.items,
         $$$[*]$$
         COLUMNS (
             product text    PATH $$$.product$$,
             qty     integer PATH $$$.qty$$,
             price   integer PATH $$$.price$$
         )
     ) AS items;

-- 결과:
--  order_id | customer | product | qty |  price  |  total
-- ----------+----------+---------+-----+---------+---------
--         1 | 홍길동    | 노트북   |   1 | 1500000 | 1500000
--         1 | 홍길동    | 마우스   |   2 |   35000 |   70000
--         2 | 장보고    | 키보드   |   1 |   89000 |   89000

실전 예제 3: 중첩 JSON 처리 (NESTED PATH)

-- 중첩 배열이 있는 JSON 처리
SELECT dept.name AS department, emp.name AS employee, emp.role
FROM JSON_TABLE(
    $${
      "departments": [
        {
          "name": "개발팀",
          "members": [
            {"name": "김개발", "role": "백엔드"},
            {"name": "이프론", "role": "프론트엔드"}
          ]
        },
        {
          "name": "디자인팀",
          "members": [
            {"name": "박디자", "role": "UI/UX"}
          ]
        }
      ]
    }$$::jsonb,
    $$$.departments[*]$$
    COLUMNS (
        name text PATH $$$.name$$,
        NESTED PATH $$$.members[*]$$ COLUMNS (
            name text PATH $$$.name$$,
            role text PATH $$$.role$$
        )
    )
) AS dept
CROSS JOIN LATERAL (
    SELECT name, role FROM dept
) AS emp;

SQL/JSON 생성자 함수

-- JSON 생성자: 쿼리 결과를 JSON으로 변환
SELECT JSON_OBJECT(
    "user_id": id,
    "full_name": first_name || " " || last_name,
    "active": is_active
) AS user_json
FROM users
WHERE department = "개발팀";

-- JSON_ARRAY: 배열 생성
SELECT JSON_ARRAY(
    SELECT product_name
    FROM products
    WHERE category = "전자기기"
) AS product_list;

-- JSON_EXISTS: JSON 내 경로 존재 여부 확인
SELECT *
FROM orders
WHERE JSON_EXISTS(
    items, $$$[*] ? (@.price > 1000000)$$
);

-- JSON_VALUE: 스칼라 값 추출
SELECT JSON_VALUE(
    metadata, $$$.version$$ RETURNING integer
) AS api_version
FROM configurations;

성능 최적화 팁

1. JSONB 컬럼에 GIN 인덱스 생성: CREATE INDEX idx_items ON orders USING GIN (items);
2. JSON_TABLE보다 간단한 추출은 ->> 연산자가 빠름
3. 자주 조회하는 JSON 필드는 Generated Column으로 분리 고려
4. JSON_TABLE은 FROM 절에서 사용하므로 WHERE 조건으로 먼저 행을 필터링한 후 적용

PostgreSQL 17의 JSON_TABLE은 JSON 기반 API 데이터를 관계형 쿼리로 자연스럽게 처리할 수 있는 강력한 도구다. SQL:2023 표준을 따르므로 다른 DBMS와의 호환성도 확보된다.