PostgreSQL 17의 SQL/JSON 표준 지원

PostgreSQL 17은 SQL:2016 표준의 SQL/JSON 함수를 본격 지원합니다. JSON_TABLE, JSON_EXISTS, JSON_QUERY, JSON_VALUE 4가지 핵심 함수로 JSON 데이터를 관계형 테이블처럼 다룰 수 있습니다.

JSON_TABLE - JSON을 테이블로 변환

JSON 배열을 관계형 테이블 행으로 변환하는 가장 강력한 함수입니다.

-- 주문 JSON 데이터를 테이블로 변환
SELECT jt.*
FROM orders o,
  JSON_TABLE(
    o.order_data,
    '$.items[*]'
    COLUMNS (
      item_id    INT          PATH '$.id',
      name       VARCHAR(100) PATH '$.name',
      price      NUMERIC      PATH '$.price',
      quantity   INT          PATH '$.qty' DEFAULT 1 ON EMPTY,
      category   VARCHAR(50)  PATH '$.category'
                              DEFAULT 'uncategorized' ON ERROR
    )
  ) AS jt
WHERE jt.price > 10000;

중첩 JSON 처리

-- 중첩된 JSON 구조 처리
SELECT *
FROM JSON_TABLE(
  '{"store": "A", "products": [{"name": "노트북", "specs": {"cpu": "M4", "ram": 16}}, {"name": "태블릿", "specs": {"cpu": "A17", "ram": 8}}]}',
  '$.products[*]'
  COLUMNS (
    product_name VARCHAR(50) PATH '$.name',
    cpu          VARCHAR(20) PATH '$.specs.cpu',
    ram_gb       INT         PATH '$.specs.ram'
  )
) AS products;

JSON_EXISTS - 조건 검사

JSON 경로가 존재하는지 boolean으로 반환합니다. WHERE 절에서 JSON 필터링에 사용합니다.

-- 특정 키가 존재하는 행만 필터링
SELECT id, data
FROM events
WHERE JSON_EXISTS(data, '$.error');

-- 조건부 경로 검사 (필터 표현식)
SELECT id, data
FROM events
WHERE JSON_EXISTS(
  data,
  '$.items[*] ? (@.price > 50000)'
);

-- PASSING 절로 변수 바인딩
SELECT id, data
FROM products
WHERE JSON_EXISTS(
  data,
  '$.reviews[*] ? (@.rating >= $min_rating)'
  PASSING 4 AS min_rating
);

JSON_QUERY - JSON 부분 추출

JSON 문서에서 JSON 객체나 배열을 추출합니다. 스칼라가 아닌 구조화된 JSON을 반환합니다.

-- 중첩된 객체 추출
SELECT
  id,
  JSON_QUERY(data, '$.address') AS address_json,
  JSON_QUERY(data, '$.tags') AS tags_array
FROM users;

-- WRAPPER 절로 배열 감싸기
SELECT JSON_QUERY(
  '{"a": 1, "b": 2}',
  '$.a'
  WITH ARRAY WRAPPER
) AS result;
-- 결과: [1]

-- 에러 처리
SELECT JSON_QUERY(
  '{"name": "test"}',
  '$.missing'
  EMPTY OBJECT ON EMPTY
  EMPTY ARRAY ON ERROR
) AS safe_result;
-- 결과: {} (키가 없으면 빈 객체)

JSON_VALUE - 스칼라 값 추출

JSON에서 단일 스칼라 값(문자열, 숫자, boolean)을 추출하여 SQL 타입으로 변환합니다.

-- 스칼라 값 추출 및 타입 변환
SELECT
  JSON_VALUE(data, '$.name') AS name,
  JSON_VALUE(data, '$.age' RETURNING INT) AS age,
  JSON_VALUE(data, '$.score' RETURNING NUMERIC(5,2)) AS score,
  JSON_VALUE(data, '$.active' RETURNING BOOLEAN) AS is_active
FROM users;

-- DEFAULT 값 지정
SELECT
  JSON_VALUE(
    data,
    '$.nickname'
    RETURNING VARCHAR(50)
    DEFAULT 'anonymous' ON EMPTY
    DEFAULT 'error' ON ERROR
  ) AS display_name
FROM users;

Incremental Backup 지원

PostgreSQL 17은 증분 백업도 새로 지원합니다. WAL summarizer가 변경된 블록만 추적하여 백업 시간과 용량을 대폭 줄입니다.

# postgresql.conf 설정
summarize_wal = on

# 전체 백업 (최초 1회)
pg_basebackup -D /backup/full --checkpoint=fast

# 증분 백업 (변경분만)
pg_basebackup -D /backup/incr1 \
  --incremental=/backup/full/backup_manifest

# 증분 백업 복원 (pg_combinebackup)
pg_combinebackup -d /backup/restored \
  /backup/full /backup/incr1

MERGE RETURNING 절

-- MERGE 결과를 바로 반환
MERGE INTO inventory t
USING incoming_stock s
ON t.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET quantity = t.quantity + s.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, name, quantity)
  VALUES (s.sku, s.name, s.quantity)
RETURNING merge_action(), t.sku, t.quantity;
-- merge_action()은 'INSERT' 또는 'UPDATE' 반환

핵심 정리

• JSON_TABLE로 JSON 배열을 관계형 테이블로 변환
• JSON_EXISTS로 JSON 조건 필터링
• JSON_QUERY로 중첩 JSON 구조 추출
• JSON_VALUE로 스칼라 값 추출 및 타입 변환
• Incremental Backup으로 백업 용량 절감
• MERGE RETURNING으로 upsert 결과 즉시 확인