중첩·반정형 데이터
JSON, STRUCT, LIST, VARIANT를 분석 가능한 스키마로 바꾸는 DuckDB 모델링 기준을 정리합니다.
핵심 요약
- DuckDB는 JSON, STRUCT, LIST, MAP, 2.0의 VARIANT를 제공하지만 publish mart는 해석 가능한 typed column으로 고정해야 합니다.
- JSON은 보관 후 path 추출, 동일 key 구조는 STRUCT, 반복 값은 LIST, 매우 가변 스키마는 VARIANT처럼 임시 분석과 publish mart의 타입 선택을 분리합니다.
- STRUCT는 모든 row가 같은 key layout을 쓰는 제약 덕에 분석 성능과 타입 일관성이 좋고, struct_pack과 dot 접근으로 다룹니다.
- 배열은 UNNEST 전에 length 분위수로 길이 분포를 보고 row explosion을 미리 가늠합니다.
- raw payload 보존, mart에 typed projection, key registry, JSON(0-based)·LIST(1-based) indexing 차이 문서화, try_cast 실패 카운트 병행이 운영 규칙입니다.
2.0 VARIANT와 타입 경계
2.0은 VARIANT를 저장·실행·Parquet 읽기/쓰기까지 통합합니다. JSON 문자열과 동일한 저장 방식으로 가정하지 말고, 이종 원본 보관과 typed mart 계약을 나눕니다.
SELECT variant_type('{"plan":"pro","seats":3}'::JSON::VARIANT) AS payload_type;
SELECT list_transform([1, 2, 3], lambda value: value + 1) AS next_values;공개 mart에서는 NULL::VARCHAR처럼 결측 컬럼의 타입을 명시합니다. 2.0의 CREATE TABLE AS SELECT NULL AS x는 나중에 문자열을 넣을 수 있는 placeholder 컬럼이 아닙니다.
분석 데이터는 갈수록 반정형으로 들어옵니다. 이벤트 properties, webhook payload, product metadata, API response는 애초에 flat table이 아닙니다.
DuckDB가 JSON, STRUCT, LIST, MAP, 2.0의 VARIANT 같은 타입을 제공하긴 하지만, publish mart는 해석 가능한 컬럼으로 고정해야 합니다.
타입 선택
| 입력 | 임시 분석 | publish mart |
|---|---|---|
| JSON payload | JSON으로 보관 후 path 추출 | 필요한 key를 typed column으로 승격 |
| 동일 key 구조 | STRUCT | STRUCT 또는 flat column |
| 반복 값 | LIST | explode table 또는 list 유지 |
| schema가 매우 가변 | VARIANT 검토 | 소비자별 typed projection 제공 |
JSON 추출
SELECT
event_id,
payload->>'$.plan' AS plan,
try_cast(payload->>'$.amount' AS DECIMAL(18,2)) AS amount,
payload->'$.items' AS items_json
FROM raw_events;JSONPath는 field lookup과 array lookup에만 씁니다. 복잡한 필터 로직은 JSONPath에 숨기지 말고 SQL로 드러냅니다.
STRUCT
DuckDB STRUCT는 모든 row가 같은 key layout을 쓰는 중첩 타입입니다. 이 제약 덕에 분석 성능과 타입 일관성이 좋아집니다.
SELECT
customer_id,
struct_pack(
plan := plan,
billing_cycle := billing_cycle,
mrr := mrr
) AS subscription
FROM customer_snapshot;SELECT
customer_id,
subscription.plan,
subscription.mrr
FROM customer_snapshot_struct;Unnest 기준
반복 배열은 "row를 늘릴지"를 먼저 결정합니다.
SELECT
order_id,
item.item_id,
item.quantity
FROM orders,
UNNEST(items) AS t(item);row explosion이 예상되면 먼저 배열 길이 분포를 봅니다. list_count는 NULL 원소를 제외하므로 전체 원소 수에는 length를 씁니다. NULL 리스트와 빈 리스트는 별도 집계합니다.
SELECT
quantile_cont(length(items), [0.5, 0.9, 0.99]) AS item_count_quantiles
FROM orders;반정형 운영 규칙
| 규칙 | 이유 |
|---|---|
| raw payload를 보존한다 | 새 key 재처리와 디버깅 |
| mart에는 typed projection을 둔다 | BI와 downstream 안정성 |
| key registry를 관리한다 | payload drift 감지 |
| JSON indexing 차이를 문서화한다 | JSON은 0-based, LIST/ARRAY는 1-based |
try_cast와 실패 카운트를 함께 둔다 | 조용한 null 전환 방지 |
2.0 JSON 수정 함수
json_set은 추가/교체, json_insert는 없는 경로만 추가, json_replace는 있는 경로만 교체, json_remove는 제거를 수행합니다. 원본을 직접 바꾸지 않고 수정된 JSON을 반환합니다. 값 인자는 JSON 텍스트이므로 문자열 값에는 JSON 따옴표도 필요합니다.
SELECT json_set('{"plan":"free","seats":1}', '$.plan', '"pro"') AS upgraded;
SELECT json_remove('{"plan":"pro","debug":true}', '$.debug') AS public_payload;
SELECT length([1, NULL, 3]) AS elements,
list_count([1, NULL, 3]) AS non_null_elements; -- 3, 2키 순서만 다른 JSON을 비교할 때는 json_normalize를 검토하고, SQL NULL과 JSON null을 구분합니다. JSON 함수.