PostgreSQL JSONB와 CTE 활용: 키 누락·JSON null·빈 배열 처리

1장. JSON을 펼쳤더니 주문 두 건이 사라졌다#

주문 테이블에 JSONB 데이터가 저장되어 있습니다.

주문 1:

{
  "customer": {
    "name": "가람"
  },
  "status": "shipped",
  "tags": ["vip", "gift"]
}

주문 2:

{
  "customer": {
    "name": "나래"
  },
  "status": "ready",
  "tags": []
}

주문 3:

{
  "status": "shipped"
}

태그를 행으로 펼쳤습니다.

결과:

주문 1 / vip
주문 1 / gift

뿐입니다.

주문 2와 주문 3은 사라졌습니다.

데이터가 삭제된 것은 아닙니다.

배열을 행으로 확장하는 과정에서:

빈 배열
→ 생성할 행 0개

키 누락
→ 확장할 배열 없음

이 되었기 때문입니다.

JSON을 SQL로 다룰 때 가장 먼저 이해해야 하는 것은 이것입니다.

JSON 문서 한 건과 JSON 내부 값을 펼친 SQL 한 행은 같은 단위가 아니다.

2장. JSON은 유연하지만 상태의 종류가 많다#

관계형 테이블에서는 다음과 같이 생각하기 쉽습니다.

값 있음

NULL

하지만 JSON에서는 더 많은 상태가 존재할 수 있습니다.

키 자체가 없음

키는 있지만 JSON null

빈 문자열

문자열 "null"

빈 배열 []

빈 객체 {}

예상과 다른 자료형

화면에서는 모두 빈칸처럼 보일 수 있습니다.

하지만 데이터 품질과 SQL 처리에서는 서로 다른 상태입니다.

3장. 이번 실습 테이블을 만들자#

CREATE TABLE json_order (
    order_id integer PRIMARY KEY,
    detail jsonb NOT NULL
);

데이터를 입력합니다.

INSERT INTO json_order
VALUES
    (
        1,
        '{
            "customer": {
                "name": "가람"
            },
            "status": "shipped",
            "tags": ["vip", "gift"]
        }'
    ),
    (
        2,
        '{
            "customer": {
                "name": "나래"
            },
            "status": "ready",
            "tags": []
        }'
    ),
    (
        3,
        '{
            "status": "shipped"
        }'
    );

4장. PostgreSQL JSONB에서 자주 쓰는 세 가지 추출 방식#

대표적인 연산자는 다음과 같습니다.

->

JSON 값을 반환합니다.

->>

텍스트 값을 반환합니다.

#>>

중첩 경로의 값을 텍스트로 반환합니다.

5장. ->는 JSON 값을 유지한다#

SELECT
    order_id,
    detail -> 'status' AS status
FROM json_order
ORDER BY order_id;

결과는 개념적으로:

1 / "shipped"
2 / "ready"
3 / "shipped"

입니다.

문자열도 JSON 값으로 반환됩니다.

6장. ->>는 텍스트를 반환한다#

SELECT
    order_id,
    detail ->> 'status' AS status
FROM json_order
ORDER BY order_id;

결과:

1 / shipped
2 / ready
3 / shipped

입니다.

SQL 조건에서 일반 문자열처럼 비교하기 편합니다.

7장. 중첩 객체의 값을 꺼내 보자#

고객 이름 경로:

customer
→ name

입니다.

다음처럼 사용할 수 있습니다.

SELECT
    order_id,
    detail #>> '{customer,name}'
        AS customer_name
FROM json_order
ORDER BY order_id;

결과:

주문 고객 이름
1 가람
2 나래
3 NULL

주문 3에는 customer.name이 없습니다.

8장. 텍스트 결과 NULL만 보고 원본 상태를 판단하면 안 된다#

다음 두 JSON을 생각해 보겠습니다.

첫 번째:

{}

두 번째:

{
  "name": null
}

다음 조회에서는 둘 다 SQL NULL처럼 보일 수 있습니다.

detail ->> 'name'

하지만 원본 상태는 다릅니다.

첫 번째
키 없음

두 번째
키 존재 + JSON null

입니다.

9장. JSON null과 SQL NULL은 다르다#

JSON:

{
  "name": null
}

의 null은 JSON 문서 내부에 저장된 값입니다.

반면 SQL NULL은:

SQL 값이 없음

을 나타냅니다.

두 개념을 섞으면 상태 판정이 어렵습니다.

10장. 문자열 "null"은 또 다른 값이다#

다음 JSON:

{
  "name": "null"
}

은 JSON null이 아닙니다.

문자열:

null

입니다.

즉 다음 세 가지는 서로 다릅니다.

키 없음

"name": null

"name": "null"

11장. 키 존재 여부를 직접 검사할 수 있다#

PostgreSQL JSONB에서는 ? 연산자로 최상위 키 존재 여부를 확인할 수 있습니다.

SELECT
    order_id,
    detail ? 'tags' AS has_tags
FROM json_order
ORDER BY order_id;

예상:

주문 1
true

주문 2
true

주문 3
false

입니다.

12장. 값 추출과 키 존재 검사는 목적이 다르다#

다음:

detail ->> 'tags'

은 값을 읽는 작업입니다.

반면:

detail ? 'tags'

은 키 존재 여부를 확인합니다.

데이터 품질 규칙이:

tags가 제공되었는가?

라면 키 존재 여부가 더 중요할 수 있습니다.

13장. JSON 자료형도 확인할 수 있다#

jsonb_typeof(detail -> 'tags')

을 사용할 수 있습니다.

정상 배열:

array

문자열:

string

객체:

object

JSON null:

null

등으로 구분할 수 있습니다.

14장. JSON 상태를 더 다양하게 만들어 보자#

이번에는 별도 입력을 이용하겠습니다.

WITH input (
    order_id,
    detail
) AS (
    VALUES
        (
            1,
            '{"tags":["vip","gift"]}'::jsonb
        ),
        (
            2,
            '{"tags":[]}'::jsonb
        ),
        (
            3,
            '{}'::jsonb
        ),
        (
            4,
            '{"tags":null}'::jsonb
        ),
        (
            5,
            '{"tags":"vip"}'::jsonb
        )
)
SELECT *
FROM input;

15장. 다섯 가지 입력의 의미#

주문 1#

{"tags":["vip","gift"]}

상태:

정상 배열

주문 2#

{"tags":[]}

상태:

빈 배열

주문 3#

{}

상태:

키 누락

주문 4#

{"tags":null}

상태:

JSON null

주문 5#

{"tags":"vip"}

상태:

자료형 오류

입니다.

16장. 상태를 CASE로 분류해 보자#

WITH input (
    order_id,
    detail
) AS (
    VALUES
        (
            1,
            '{"tags":["vip","gift"]}'::jsonb
        ),
        (
            2,
            '{"tags":[]}'::jsonb
        ),
        (
            3,
            '{}'::jsonb
        ),
        (
            4,
            '{"tags":null}'::jsonb
        ),
        (
            5,
            '{"tags":"vip"}'::jsonb
        )
)
SELECT
    order_id,
    CASE
        WHEN NOT (
            detail ? 'tags'
        )
            THEN '키 누락'
        WHEN detail -> 'tags'
             = 'null'::jsonb
            THEN 'JSON null'
        WHEN jsonb_typeof(
            detail -> 'tags'
        ) = 'array'
            THEN '배열'
        ELSE '자료형 오류'
    END AS tag_state
FROM input
ORDER BY order_id;

17장. 결과#

주문 상태
1 배열
2 배열
3 키 누락
4 JSON null
5 자료형 오류

빈 배열도 자료형은 array입니다.

따라서 배열인지 여부만으로:

원소 있음

원소 없음

까지 구분되지는 않습니다.

18장. 빈 배열 여부는 길이를 확인할 수 있다#

정상 배열이라는 조건이 먼저 보장된 상황이라면:

jsonb_array_length(
    detail -> 'tags'
)

를 사용할 수 있습니다.

예:

["vip","gift"]
→ 2

[]
→ 0

입니다.

19장. 자료형 확인 없이 배열 함수를 호출하면 오류가 날 수 있다#

다음 입력:

{"tags":"vip"}

에서:

jsonb_array_elements_text(
    detail -> 'tags'
)

를 호출하면 배열이 아닌 값을 배열처럼 펼치려 하기 때문에 문제가 발생할 수 있습니다.

따라서 반정형 데이터에서는:

자료형 확인
↓
확장

순서가 중요합니다.

20장. 배열을 행으로 펼쳐 보자#

기본 주문 데이터에서:

SELECT
    j.order_id,
    tag.value
FROM json_order AS j
CROSS JOIN LATERAL
    jsonb_array_elements_text(
        COALESCE(
            j.detail -> 'tags',
            '[]'::jsonb
        )
    ) AS tag(value)
ORDER BY
    j.order_id,
    tag.value;

결과:

1 / gift

1 / vip

입니다.

21장. 왜 주문 2가 사라졌을까#

주문 2:

"tags": []

입니다.

배열 원소 수:

0

입니다.

jsonb_array_elements_text는 생성할 행이 없습니다.

따라서 내부 LATERAL 결합에서는 원본 주문 2도 결과에서 사라집니다.

22장. 주문 3도 사라지지만 이유는 다르다#

주문 3에는:

tags 키 없음

입니다.

COALESCE로:

[]

로 바꿨기 때문에 확장 결과가 0행이 됩니다.

결과에서는 주문 2와 주문 3 모두 사라집니다.

하지만 원인은 서로 다릅니다.

주문 2
빈 배열

주문 3
키 누락

입니다.

23장. COALESCE는 편리하지만 상태를 지울 수 있다#

다음 표현:

COALESCE(
    detail -> 'tags',
    '[]'::jsonb
)

은 키가 없을 때 빈 배열로 바꿉니다.

확장 쿼리는 간단해집니다.

하지만:

원래 키가 없었는가?

실제로 빈 배열이었는가?

를 결과만 보고 구분하기 어려워집니다.

그래서 상태를 별도 컬럼으로 유지하는 것이 좋습니다.

24장. 원본 주문을 모두 유지하려면 LEFT JOIN LATERAL을 사용한다#

SELECT
    j.order_id,
    tag.value
FROM json_order AS j
LEFT JOIN LATERAL
    jsonb_array_elements_text(
        COALESCE(
            j.detail -> 'tags',
            '[]'::jsonb
        )
    ) AS tag(value)
ON true
ORDER BY
    j.order_id,
    tag.value;

25장. LEFT JOIN LATERAL 결과#

개념적으로:

주문 태그
1 gift
1 vip
2 NULL
3 NULL

입니다.

원래 주문 2와 3이 유지됩니다.

26장. 하지만 주문 2와 주문 3이 다시 같은 모습이 됐다#

결과만 보면:

주문 2
tag = NULL

주문 3
tag = NULL

입니다.

하지만 상태는:

2
빈 배열

3
키 누락

입니다.

따라서 외부 조인만으로 원인까지 보존되는 것은 아닙니다.

27장. CTE로 상태 판정과 배열 확장을 나누자#

WITH checked AS (
    SELECT
        order_id,
        detail,
        CASE
            WHEN NOT (
                detail ? 'tags'
            )
                THEN '키 누락'
            WHEN detail -> 'tags'
                 = 'null'::jsonb
                THEN 'JSON null'
            WHEN jsonb_typeof(
                detail -> 'tags'
            ) = 'array'
                THEN '배열'
            ELSE '자료형 오류'
        END AS tag_state
    FROM json_order
)
SELECT *
FROM checked;

이제 상태 판정이라는 작업에 이름을 붙였습니다.

28장. CTE는 복잡한 SQL을 단계별 데이터 변환으로 볼 수 있게 한다#

이번 흐름은 다음과 같습니다.

flowchart LR
    A["원본 JSON"] --> B["상태 판정"]
    B --> C["안전한 배열 변환"]
    C --> D["LATERAL 확장"]
    D --> E["집계·보고"]

각 단계에서 행과 값이 어떻게 바뀌는지 확인할 수 있습니다.

29장. 잘못된 자료형은 빈 배열로 대체하되 상태는 남긴다#

WITH input (
    order_id,
    detail
) AS (
    VALUES
        (
            1,
            '{"tags":["vip","gift"]}'::jsonb
        ),
        (
            2,
            '{"tags":[]}'::jsonb
        ),
        (
            3,
            '{}'::jsonb
        ),
        (
            4,
            '{"tags":null}'::jsonb
        ),
        (
            5,
            '{"tags":"vip"}'::jsonb
        )
),
checked AS (
    SELECT
        order_id,
        detail,
        CASE
            WHEN NOT (
                detail ? 'tags'
            )
                THEN '키 누락'
            WHEN detail -> 'tags'
                 = 'null'::jsonb
                THEN 'JSON null'
            WHEN jsonb_typeof(
                detail -> 'tags'
            ) = 'array'
                THEN '배열'
            ELSE '자료형 오류'
        END AS tag_state
    FROM input
)
SELECT
    c.order_id,
    c.tag_state,
    t.value AS tag
FROM checked AS c
LEFT JOIN LATERAL
    jsonb_array_elements_text(
        CASE
            WHEN jsonb_typeof(
                c.detail -> 'tags'
            ) = 'array'
                THEN c.detail -> 'tags'
            ELSE '[]'::jsonb
        END
    ) AS t(value)
ON true
ORDER BY
    c.order_id,
    t.value;

30장. 완성 결과를 보면#

주문 상태 태그
1 배열 gift
1 배열 vip
2 배열 NULL
3 키 누락 NULL
4 JSON null NULL
5 자료형 오류 NULL

이제 주문을 잃지 않으면서 원래 상태도 구분할 수 있습니다.

31장. 결과 행 수는 주문 수가 아니다#

원본 주문:

5건

입니다.

확장 결과:

6행

입니다.

왜냐하면 주문 1이 태그 두 개를 가지고 있기 때문입니다.

주문 1
→ 2행

주문 2
→ 1행

주문 3
→ 1행

주문 4
→ 1행

주문 5
→ 1행

총 6행입니다.

32장. COUNT 별표를 쓰면 무엇을 세는가#

확장 결과에서:

COUNT(*)

을 사용하면:

확장 결과 행 수

를 셉니다.

이번 예에서는:

6

입니다.

하지만 주문 수는:

5

입니다.

33장. 주문 수를 세려면 주문 기준으로 계산해야 한다#

예:

COUNT(
    DISTINCT order_id
)

를 사용할 수 있습니다.

결과:

5

입니다.

SQL 집계에서는 항상:

현재 결과의 한 행은 무엇인가?

를 확인해야 합니다.

34장. 태그 수 역시 COUNT 별표와 같지 않을 수 있다#

LEFT JOIN LATERAL 결과:

주문 2
tag NULL

주문 3
tag NULL

같은 보존 행이 있습니다.

따라서:

COUNT(*)

은 이 행도 셉니다.

반면:

COUNT(tag)

는 SQL NULL을 세지 않습니다.

35장. 주문 1의 태그 수는 2다#

원본:

["vip", "gift"]

이므로 실제 배열 원소 수:

2

입니다.

하지만 전체 LEFT JOIN 결과에서:

COUNT(*)

를 태그 수라고 해석하면 빈 배열 주문의 보존 행까지 포함될 수 있습니다.

36장. 배열 안에 JSON null이 들어오면 문제는 더 복잡해진다#

예:

{
  "tags": ["vip", null]
}

배열 원소 자체는:

2개

입니다.

그러나 텍스트 추출과 SQL NULL 처리 방식에 따라 집계 결과가 달라질 수 있습니다.

따라서:

배열 원소 수

유효 문자열 태그 수

서로 다른 태그 수

를 구분해야 할 수 있습니다.

37장. 중복 태그도 업무 의미를 먼저 정해야 한다#

다음 배열:

["vip", "vip"]

이 있다고 하겠습니다.

원소 수:

2

서로 다른 값:

1

입니다.

질문에 따라 답이 달라집니다.

태그 입력 횟수
→ 2

태그 종류 수
→ 1

입니다.

38장. DISTINCT는 어디에 적용하는지 중요하다#

다음:

COUNT(
    DISTINCT tag
)

는 서로 다른 태그 값을 셉니다.

반면:

COUNT(
    DISTINCT order_id
)

는 주문 수를 셉니다.

같은 DISTINCT라도 대상에 따라 완전히 다른 지표입니다.

39장. 배열 펼침은 관계형 데이터의 1:N 조인과 비슷하다#

주문:

1행

태그:

2개

를 펼치면:

주문 1
→ 태그 2행

이 됩니다.

관계형 모델에서:

Order
1:N
OrderTag

를 조인했을 때와 비슷한 행 확장이 발생합니다.

40장. JSON 안에 넣었다고 관계의 카디널리티가 사라지는 것은 아니다#

문서 한 건에:

{
  "tags": ["vip", "gift"]
}

가 들어 있어도 논리적으로는:

주문 1건

태그 연결 2건

입니다.

SQL로 펼치는 순간 그 관계가 행으로 드러납니다.

41장. JSON 배열을 무작정 관계형 컬럼처럼 집계하면 분모가 흔들린다#

예를 들어 주문별 매출:

주문 1
10,000원

에 태그가 두 개 있습니다.

배열을 먼저 펼친 뒤:

SUM(order_amount)

하면:

10,000
+
10,000
=
20,000

으로 중복 계산될 수 있습니다.

42장. 원본 주문 금액과 태그 분석을 분리해야 한다#

태그별 매출을 분석하는 목적이라면:

주문 금액을 태그마다 중복 귀속할 것인가?

태그 수로 나눌 것인가?

대표 태그만 사용할 것인가?

같은 업무 정의가 필요합니다.

SQL이 자동으로 정답을 정해 주지 않습니다.

43장. JSON 키가 없다는 사실도 데이터 품질 정보다#

예를 들어 주문 3에:

customer

키가 없습니다.

이것이:

비회원 주문

인지:

수집 실패

인지:

이전 스키마 버전

인지 알 수 없습니다.

키 누락을 단순 NULL로만 바꾸면 이 차이를 잃을 수 있습니다.

44장. 스키마 버전을 JSON에 둘 수도 있다#

예:

{
  "schema_version": 2,
  "customer": {
    "name": "가람"
  }
}

이런 정보가 있으면:

v1 문서

v2 문서

를 구분해 해석할 수 있습니다.

반정형 데이터에서도 스키마가 완전히 사라지는 것은 아닙니다.

45장. JSON은 스키마가 없는 것이 아니라 스키마 통제가 느슨할 수 있는 것이다#

관계형 테이블은 컬럼 정의로 구조를 강하게 제한합니다.

JSONB는 하나의 컬럼 안에서 다양한 구조를 저장할 수 있습니다.

하지만 애플리케이션이 기대하는 구조는 여전히 존재합니다.

예:

status
문자열

tags
문자열 배열

customer
객체

이 자체가 사실상 데이터 계약입니다.

46장. 자료형 오류를 정상적인 값 없음으로 처리하면 문제가 숨는다#

다음:

{
  "tags": "vip"
}

를:

태그 없음

으로 처리하면 원본 공급자의 구조 오류를 놓칩니다.

더 나은 상태는:

자료형 오류

입니다.

조회는 계속할 수 있게 빈 배열로 대체하더라도 상태를 별도로 남겨야 합니다.

47장. 보고용 표시와 데이터 품질 상태를 분리하자#

화면에서는:

키 누락
JSON null
빈 배열
자료형 오류

를 모두:

태그 없음

으로 보여줄 수 있습니다.

하지만 분석 컬럼에는:

tag_state

를 보존할 수 있습니다.

표현과 원본 상태는 다른 문제입니다.

48장. COALESCE는 표시용으로 유용하지만 원인을 보존하지 않는다#

예:

COALESCE(
    detail #>> '{customer,name}',
    '미확인'
)

은 화면에서 편리합니다.

하지만 결과가:

미확인

이라고 해서:

customer 없음

name 없음

JSON null

중 어떤 경우인지 알 수 없습니다.

49장. 상태 판정 CTE와 표시 CTE를 나눌 수도 있다#

예:

raw
→ 원본 JSON

checked
→ 구조·자료형 판정

expanded
→ 배열 확장

reported
→ 사용자 표시

처럼 단계마다 역할을 분리할 수 있습니다.

복잡한 JSON SQL일수록 이런 구조가 디버깅에 유리합니다.

50장. JSONB 표현식도 인덱싱할 수 있다#

다음 조건을 자주 사용한다고 하겠습니다.

WHERE detail ->> 'status'
      = 'shipped'

표현식 인덱스를 검토할 수 있습니다.

CREATE INDEX json_order_status_idx
ON json_order (
    (detail ->> 'status')
);

51장. 표현식 인덱스가 있다고 항상 사용하는 것은 아니다#

행이 세 건뿐인 현재 테이블에서는 PostgreSQL이:

Seq Scan

을 선택할 수 있습니다.

인덱스는:

사용 가능한 경로

이지:

반드시 선택되는 경로

가 아닙니다.

52장. 자주 검색하는 핵심 속성은 정규 컬럼으로 분리할 수도 있다#

예를 들어 모든 주문에서:

status

를 자주 검색하고 상태 무결성도 중요하다고 하겠습니다.

이 경우 JSON 내부보다:

status text NOT NULL

같은 정규 컬럼으로 빼는 것이 더 단순할 수 있습니다.

JSONB 사용이 모든 속성을 JSON 안에 넣어야 한다는 뜻은 아닙니다.

53장. JSONB가 잘 맞는 데이터와 정규 컬럼이 잘 맞는 데이터가 다르다#

정규 컬럼에 잘 맞는 것:

주문 ID

상태

고객 ID

주문 금액

생성 시각

처럼:

검색 빈도가 높고

제약이 중요하고

형식이 안정적인 값

입니다.

JSONB에 잘 맞을 수 있는 것:

부가 메타데이터

외부 서비스별 추가 속성

자주 바뀌는 옵션

입니다.

54장. JSONB 객체 내부 존재 조건도 사용할 수 있다#

예:

WHERE detail ? 'customer'

는 최상위 customer 키 존재 여부를 확인합니다.

하지만:

customer 키 존재

와:

customer.name 존재

는 다릅니다.

55장. 중첩 경로는 단계별로 확인할 수 있다#

다음 JSON:

{
  "customer": {}
}

에서는:

customer
→ 존재

customer.name
→ 없음

입니다.

데이터 품질 규칙에 따라 어느 단계가 필수인지 정해야 합니다.

56장. JSON 객체가 예상과 다른 자료형일 수도 있다#

예:

{
  "customer": "가람"
}

애플리케이션은:

{
  "customer": {
    "name": "가람"
  }
}

을 기대합니다.

단순한 customer 키 존재 검사만으로는 이 문제를 발견하지 못합니다.

자료형까지 확인해야 합니다.

57장. CTE는 결과를 저장하는 임시 테이블이라고만 이해하면 부족하다#

CTE는 쿼리 내부의 논리적 단계에 이름을 붙이는 도구로 사용할 수 있습니다.

예:

active

dept_avg

company_avg

처럼 각 단계의 의미를 명확하게 할 수 있습니다.

PostgreSQL에서 CTE의 실제 실행 전략은 쿼리와 설정 등에 따라 달라질 수 있으므로:

CTE
=
항상 물리적 임시 테이블

이라고 이해하면 안 됩니다.

58장. 평균 비교를 CTE로 나눠 보자#

직원 데이터를 별도 예제로 사용하겠습니다.

CREATE TABLE department (
    dept_id integer PRIMARY KEY,
    dept_name text NOT NULL
);

CREATE TABLE employee (
    emp_id integer PRIMARY KEY,
    emp_name text NOT NULL,
    dept_id integer
        REFERENCES department(dept_id),
    salary integer NOT NULL,
    mgr_id integer
        REFERENCES employee(emp_id),
    retire_date date
);

59장. 예제 직원 데이터#

INSERT INTO department
VALUES
    (10, '개발'),
    (20, '영업'),
    (30, '인사');
INSERT INTO employee
VALUES
    (1, '가람', 10, 600, NULL, NULL),
    (2, '나래', 10, 500, 1, NULL),
    (3, '다온', 10, 500, 1, NULL),
    (4, '라온', 20, 300, 1, NULL),
    (5, '마루', 20, 100, 4, NULL),
    (6, '바다', NULL, 400, 1, NULL),
    (7, '사라', 20, 900, 4,
        DATE '2026-01-01');

60장. 첫 번째 CTE는 재직자 집합이다#

WITH active AS (
    SELECT *
    FROM employee
    WHERE retire_date IS NULL
)
SELECT *
FROM active;

이 단계는:

누가 분석 대상인가?

를 정의합니다.

61장. 두 번째 CTE는 부서 평균이다#

WITH active AS (
    SELECT *
    FROM employee
    WHERE retire_date IS NULL
),
dept_avg AS (
    SELECT
        dept_id,
        AVG(salary) AS avg_sal
    FROM active
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT *
FROM dept_avg;

결과:

개발
533.33...

영업
200

입니다.

62장. 세 번째 CTE는 회사 평균이다#

company_avg AS (
    SELECT
        AVG(salary) AS avg_sal
    FROM active
)

전체 재직자 급여:

600
500
500
300
100
400

평균:

400

입니다.

63장. 각 CTE를 결합한다#

WITH active AS (
    SELECT *
    FROM employee
    WHERE retire_date IS NULL
),
dept_avg AS (
    SELECT
        dept_id,
        AVG(salary) AS avg_sal
    FROM active
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
),
company_avg AS (
    SELECT
        AVG(salary) AS avg_sal
    FROM active
)
SELECT
    e.emp_id,
    e.salary,
    ROUND(
        d.avg_sal,
        2
    ) AS dept_avg,
    c.avg_sal AS company_avg
FROM active AS e
JOIN dept_avg AS d
  ON e.dept_id = d.dept_id
CROSS JOIN company_avg AS c
WHERE e.salary > d.avg_sal
ORDER BY e.emp_id;

64장. 결과#

가람
600
부서 평균 533.33
회사 평균 400

라온
300
부서 평균 200
회사 평균 400

입니다.

65장. company_avg를 SELECT에 넣었다고 필터에 사용되는 것은 아니다#

현재 조건:

WHERE e.salary > d.avg_sal

입니다.

따라서 질문은:

자신의 부서 평균보다 급여가 높은 직원

입니다.

company_avg는 결과에 표시될 뿐 필터에 사용되지 않습니다.

66장. 전체 평균보다 높은 직원이 필요하다면 조건이 달라진다#

WHERE e.salary > c.avg_sal

이 필요합니다.

SQL에서 컬럼을 SELECT 했다는 사실과 WHERE에서 비교했다는 사실을 구분해야 합니다.

67장. 재귀 CTE도 같은 원칙으로 읽을 수 있다#

조직 구조:

가람
├─ 나래
├─ 다온
├─ 라온
│  └─ 마루
└─ 바다

를 조회하겠습니다.

68장. 재귀 CTE는 앵커와 재귀 부분으로 나뉜다#

WITH RECURSIVE org_tree (
    emp_id,
    emp_name,
    mgr_id,
    depth
) AS (

첫 번째 SELECT는 시작 행입니다.

두 번째 SELECT는 앞에서 찾은 행을 기준으로 하위 행을 다시 찾습니다.

69장. 앵커는 최상위 관리자다#

SELECT
    emp_id,
    emp_name,
    mgr_id,
    1
FROM employee
WHERE mgr_id IS NULL
  AND retire_date IS NULL

현재 결과:

가람

입니다.

70장. 재귀 부분은 직속 부하 직원을 찾는다#

SELECT
    e.emp_id,
    e.emp_name,
    e.mgr_id,
    o.depth + 1
FROM employee AS e
JOIN org_tree AS o
  ON e.mgr_id = o.emp_id
WHERE e.retire_date IS NULL

한 단계씩 조직 아래로 내려갑니다.

71장. 완성된 재귀 CTE#

WITH RECURSIVE org_tree (
    emp_id,
    emp_name,
    mgr_id,
    depth
) AS (
    SELECT
        emp_id,
        emp_name,
        mgr_id,
        1
    FROM employee
    WHERE mgr_id IS NULL
      AND retire_date IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.emp_name,
        e.mgr_id,
        o.depth + 1
    FROM employee AS e
    JOIN org_tree AS o
      ON e.mgr_id = o.emp_id
    WHERE e.retire_date IS NULL
)
SELECT
    emp_id,
    emp_name,
    depth
FROM org_tree
ORDER BY
    depth,
    emp_id;

72장. 예상 깊이#

가람
1

나래
2

다온
2

라온
2

바다
2

마루
3

입니다.

퇴직한 사라는 제외됩니다.

73장. 재귀 CTE에서도 순환 데이터는 별도 문제다#

잘못된 조직 관계가:

1 → 2

2 → 4

4 → 1

처럼 되어 있다면 반복 탐색이 생길 수 있습니다.

실제 시스템에서는:

방문 경로 저장

순환 검사

CYCLE 지원 기능

무결성 검증

등을 검토해야 합니다.

74장. SQL의 집합 연산과 JSON 펼침을 함께 이해하면 좋다#

관계 대수의 투영은 전통적인 집합 의미에서 중복을 제거한다고 설명합니다.

하지만 SQL의:

SELECT
    name,
    dept_id
FROM employee;

는 기본적으로 중복 행을 유지할 수 있습니다.

중복 제거가 필요하면:

SELECT DISTINCT
    name,
    dept_id
FROM employee;

를 사용합니다.

75장. UNION과 UNION ALL의 차이#

query_a

UNION

query_b

는 중복을 제거합니다.

query_a

UNION ALL

query_b

는 중복을 유지합니다.

JSON 배열에서 같은 태그가 여러 주문에 등장할 수 있으므로 이 차이가 중요합니다.

76장. 태그 사용 횟수를 세는데 UNION을 사용하면 정보가 사라질 수 있다#

주문 1:

vip

주문 2:

vip

라고 하겠습니다.

태그 사용 횟수는:

2

입니다.

그런데 중간 결과를 UNION으로 합치면:

vip

한 행만 남을 수 있습니다.

집합 연산의 중복 제거가 업무 의미를 바꿀 수 있습니다.

77장. NATURAL JOIN도 실무에서는 주의가 필요하다#

NATURAL JOIN은 같은 이름의 컬럼을 자동으로 연결 조건에 사용합니다.

처음에는:

customer_id

만 같았다고 하겠습니다.

나중에 두 테이블에 우연히:

status

컬럼까지 추가되면 조인 조건의 의미가 달라질 수 있습니다.

78장. 연결 조건은 명시적으로 쓰는 편이 이해하기 쉽다#

JOIN customer AS c
  ON c.customer_id
     = o.customer_id

처럼 작성하면 무엇을 기준으로 연결하는지 분명합니다.

특히 JSON을 관계형 데이터와 섞을 때 자동 조인 규칙보다 명시적 조건이 안전합니다.

79장. EXCEPT는 차집합을 표현할 수 있다#

예를 들어:

전체 고객
-
주문 고객

을 구하는 방식으로 사용할 수 있습니다.

PostgreSQL에서는:

EXCEPT

를 사용할 수 있습니다.

다른 DBMS에서는 대응 문법이나 지원 범위가 다를 수 있습니다.

80장. PostgreSQL JSONB 문법은 다른 DBMS에 그대로 적용되지 않는다#

이번 글에서 사용한:

->

->>

#>>

jsonb_array_elements_text

?

등은 PostgreSQL JSONB 기능입니다.

MySQL, Oracle, SQL Server는 JSON을 지원하지만 문법과 함수가 다릅니다.

따라서:

JSON은 SQL 표준 기능이니까
같은 SQL을 그대로 사용한다.

고 생각하면 안 됩니다.

81장. JSON을 언제 관계형으로 분리할지 판단해야 한다#

태그 검색이 많아졌습니다.

요구:

태그별 주문 검색

태그별 집계

태그별 권한

태그 유효성 검사

태그 사전 관리

까지 생겼다고 하겠습니다.

이 경우 JSON 배열보다:

order_tag

같은 별도 관계형 테이블이 더 적합할 수 있습니다.

82장. JSONB는 모델링을 하지 않아도 된다는 뜻이 아니다#

JSONB를 사용하면 컬럼 추가 없이 구조를 유연하게 저장할 수 있습니다.

하지만 다음 질문은 여전히 필요합니다.

어떤 키가 필수인가?

어떤 자료형인가?

배열 중복을 허용하는가?

키 누락은 어떤 의미인가?

스키마 버전은 어떻게 관리하는가?

모델링의 위치가 DB DDL에서 애플리케이션 계약으로 이동할 뿐입니다.

83장. JSONB 데이터 품질 검사를 정기적으로 수행할 수도 있다#

예:

SELECT
    COUNT(*) FILTER (
        WHERE NOT (
            detail ? 'tags'
        )
    ) AS missing_tags,
    COUNT(*) FILTER (
        WHERE detail -> 'tags'
              = 'null'::jsonb
    ) AS json_null_tags,
    COUNT(*) FILTER (
        WHERE detail ? 'tags'
          AND detail -> 'tags'
              <> 'null'::jsonb
          AND jsonb_typeof(
              detail -> 'tags'
          ) <> 'array'
    ) AS invalid_type_tags
FROM json_order;

이렇게 문서 상태를 품질 지표로 관리할 수 있습니다.

84장. 정상 비율 하나만 보면 원인을 잃는다#

예:

정상 tags
95%

라고만 하면 나머지 5%가 무엇인지 알 수 없습니다.

보다 유용한 분류:

키 누락
2%

JSON null
1%

자료형 오류
2%

처럼 원인별로 나누는 것입니다.

85장. JSON 구조가 변경되면 품질 지표도 버전별로 봐야 한다#

v1 문서에는:

tags

가 없었습니다.

v2부터 필수가 되었다고 하겠습니다.

전체 문서에서:

tags 누락 40%

이라고 계산하면 오래된 정상 v1 문서까지 오류로 잡힐 수 있습니다.

따라서:

schema_version

생성 시점

적용 규칙

을 함께 봐야 합니다.

86장. JSON 배열 확장 테스트 체크리스트#

  1. 원본 JSON 문서 수는 몇 개인가?
  2. 배열 키가 없는 문서가 있는가?
  3. JSON null이 있는가?
  4. 빈 배열이 있는가?
  5. 배열 대신 문자열·숫자가 들어오는가?
  6. 배열 안에 JSON null이 있는가?
  7. 중복 원소가 있는가?
  8. CROSS JOIN LATERAL에서 사라지는 문서는 무엇인가?
  9. 모든 원본 행을 보존해야 하는가?
  10. LEFT JOIN LATERAL이 필요한가?
  11. 확장 후 한 행의 의미는 무엇인가?
  12. COUNT 별표가 실제 세려는 단위와 같은가?
  13. 원본 상태를 별도 컬럼으로 보존하는가?
  14. 자료형 오류를 빈 배열로 숨기고 있지 않은가?

87장. JSON 추출 체크리스트#

  1. JSON 값을 원하는가 텍스트를 원하는가?
  2. ->와 ->>를 구분했는가?
  3. 중첩 경로라면 #>> 등이 적합한가?
  4. 키 없음과 JSON null을 구분해야 하는가?
  5. 문자열 "null"이 들어올 가능성이 있는가?
  6. 객체·배열 자료형을 확인하는가?
  7. COALESCE로 원본 상태를 지우고 있지 않은가?
  8. 자주 검색하는 속성을 정규 컬럼으로 분리할 필요가 있는가?
  9. 표현식 인덱스의 쓰기 비용을 확인했는가?
  10. JSON 스키마 버전을 관리해야 하는가?

88장. CTE 체크리스트#

  1. 각 CTE가 어떤 데이터 단위를 만드는가?
  2. 입력 필터와 집계를 분리했는가?
  3. CTE를 지나며 행 수가 어떻게 변하는가?
  4. 계산 컬럼을 SELECT만 했는지 실제 조건에도 사용했는지 구분했는가?
  5. 재귀 CTE의 시작점은 무엇인가?
  6. 재귀 방향은 부모에서 자식인가?
  7. 퇴직자나 비활성 데이터를 포함하는가?
  8. 순환 관계 가능성이 있는가?
  9. UNION과 UNION ALL의 중복 의미를 확인했는가?
  10. 결과 행의 타입과 열 수가 호환되는가?

89장. JSONB를 SQL로 변환할 때 가장 흔한 실수#

첫 번째:

키 누락
=
JSON null

로 보는 것입니다.

둘은 다릅니다.

두 번째:

빈 배열
=
키 없음

으로 보는 것입니다.

둘 다 확장 결과 0행이 될 수 있지만 원본 상태는 다릅니다.

세 번째:

배열 확장 후 COUNT(*)
=
원본 주문 수

라고 보는 것입니다.

배열 원소 수에 따라 주문 한 건이 여러 행이 될 수 있습니다.

90장. 또 다른 실수는 자료형 오류를 정상적인 빈값으로 숨기는 것이다#

{
  "tags": "vip"
}

는:

{
  "tags": []
}

와 다릅니다.

전자는:

계약 위반

일 수 있고 후자는:

정상적인 태그 없음

일 수 있습니다.

데이터 품질에서는 이 차이를 보존해야 합니다.

91장. 배열 확장 전후의 행 수를 기록하면 문제를 빨리 찾을 수 있다#

예:

원본 주문
100만 건

확장 결과
240만 행

서로 다른 주문
100만 건

태그 NULL 보존 행
15만 건

처럼 단계별 카운트를 기록하면:

어디서 행이 늘었는가?

어디서 행이 사라졌는가?

를 확인하기 쉽습니다.

92장. 데이터 파이프라인에서도 같은 원칙을 적용할 수 있다#

JSON 이벤트:

1건

을 배열 단위로 펼치면:

N건

이 됩니다.

그 뒤 웨어하우스에 저장할 때:

이벤트 건수

배열 원소 건수

를 섞으면 지표가 부풀 수 있습니다.

JSON 확장은 단순 문법 문제가 아니라 데이터 그레인의 변화입니다.

93장. 핵심 정리#

PostgreSQL JSONB를 SQL로 다룰 때 가장 중요한 것은 JSON 문법을 많이 아는 것이 아닙니다.

먼저 원본 상태를 구분해야 합니다.

키 없음

JSON null

빈 배열

잘못된 자료형

은 서로 다른 상태입니다.

예를 들어:

{}

과:

{"tags": null}

과:

{"tags": []}

과:

{"tags": "vip"}

는 모두 다른 입력입니다.

단순한 COALESCE로 모두 빈 배열로 바꾸면 SQL은 편해질 수 있지만 왜 원래 값이 비어 있었는지는 알 수 없게 됩니다.

따라서 좋은 패턴은:

원본 상태 판정
↓
안전한 확장용 값 생성
↓
배열 확장
↓
보고용 변환

처럼 단계를 나누는 것입니다.

배열을 행으로 펼칠 때는 더 중요한 변화가 발생합니다.

주문 1건
+
태그 2개
↓
결과 2행

입니다.

따라서:

COUNT(*)

은 더 이상 주문 수가 아닐 수 있습니다.

원하는 것이 주문 수라면 주문 ID 기준으로 세고, 태그 수라면 실제 태그 원소를 세어야 합니다.

또 CROSS JOIN LATERAL에서는 빈 배열이나 키가 없는 주문이 사라질 수 있습니다.

원본 주문 자체를 유지해야 한다면:

LEFT JOIN LATERAL
...
ON true

같은 외부 결합을 사용할 수 있습니다.

하지만 외부 결합을 했다고 원본 상태가 자동으로 보존되는 것은 아닙니다.

빈 배열

키 누락

JSON null

자료형 오류

를 별도 상태 컬럼으로 남기는 것이 중요합니다.

CTE 역시 같은 원칙으로 사용할 수 있습니다.

입력

검증

확장

집계

단계에 이름을 붙이면 어느 단계에서 행이 늘어나고 값의 의미가 변했는지 추적하기 쉽습니다.

결국 JSONB와 CTE를 안정적으로 사용하는 핵심은 이것입니다.

문서 안의 값만 추출하지 말고, 그 값이 어떤 상태로 들어왔으며 SQL로 변환되는 과정에서 한 행의 의미가 어떻게 바뀌는지 함께 추적해야 한다.

JSON의 유연함은 강력합니다.

하지만 그 유연성을 안전하게 사용하려면:

구조

자료형

NULL 의미

행의 단위

집계 분모

를 관계형 데이터보다 오히려 더 명확하게 정의해야 합니다.

이 페이지의 목차