[Database] PostgreSQL JSONB 실습

PostgreSQL JSONB 실습

JSONB 란?

JSONB 는 PostgreSQL에서 JSON 데이터를 이진(Binary) 형태로 변환하여 저장하는 데이터 타입이다.

단순히 텍스트로 저장하는 json 타입과 달리, 데이터를 저장할 때 파싱 과정을 거쳐 검색과 처리에 최적화된 내부 구조로 저장합니다. 현대적인 웹 개발에서 "스키마가 정해지지 않은 데이터"를 다룰 때 필수적인 도구다.

JSON vs JSONB 비교

구분	json (Plain Text)	jsonb (Binary)
저장 방식	입력한 텍스트 그대로 저장	파싱된 이진(Binary) 형태로 저장
속도 (입력)	빠름 (그냥 저장)	약간 느림 (파싱 과정 필요)
속도 (조회)	느림 (매 조회 시 파싱)	매우 빠름 (바이너리 구조)
공백 / 키 순서	입력한 공백과 키 순서 유지	공백 제거, 키 순서 정렬
인덱스	불가능	가능 (GIN 인덱스 지원)
권장 용도	원본 JSON 보존 목적	조회/검색/조건 필터링

---

실습 DB: bootcamp_json

---

0. 실습 목표

이 워크북을 통해 다음을 달성한다.

• JSONB가 왜 필요한지 이해한다
• JSONB 조회 / 필터 / 수정을 자유롭게 한다
• JSONB 배열 / 중첩 구조를 다룬다
• JSONB 인덱스(GIN / 표현식) 를 이해한다
• 언제 JSONB를 쓰고, 언제 컬럼으로 바꿔야 하는지 판단할 수 있다

---

1. 실습 환경 준비

1-1. DB 생성

CREATE DATABASE bootcamp_json;
CREATE USER bootcamp_json_user WITH PASSWORD 'json1234';
GRANT ALL PRIVILEGES ON DATABASE bootcamp_json TO bootcamp_json_user;

접속:

psql -U bootcamp_json_user -d bootcamp_json

확인:

SELECT current_database();

---

2. 테이블 생성 (JSONB 실습 전용)

2-1. users – 설정/프로필(JSONB)

CREATE TABLE users (
  user_id     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  profile     JSONB NOT NULL DEFAULT '{}'::jsonb
);

2-2. events – 로그/이벤트(JSONB)

CREATE TABLE events (
  event_id   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id    BIGINT,
  event_type TEXT NOT NULL,
  payload    JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2-3. payments_raw – 외부 API Raw(JSONB)

CREATE TABLE payments_raw (
  payment_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  provider   TEXT NOT NULL,
  payload    JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

테이블 확인:

\dt
\d users

---

3. 실습용 데이터 입력

3-1. users 데이터

INSERT INTO users (email, name, created_at, profile) VALUES
('minsu@example.com','김민수',NOW()-INTERVAL '30 days',
 '{"tier":"gold","marketing":{"email_opt_in":true,"sms_opt_in":true},"tags":["vip","keyboard"],"ui":{"theme":"dark","lang":"ko"}}'),
('jiyoon@example.com','박지윤',NOW()-INTERVAL '20 days',
 '{"tier":"silver","marketing":{"email_opt_in":true},"tags":["new"],"ui":{"theme":"light","lang":"ko"}}'),
('seojun@example.com','이서준',NOW()-INTERVAL '10 days',
 '{"tier":"gold","marketing":{"sms_opt_in":true},"tags":["vip","monitor"],"ui":{"theme":"dark","lang":"en"}}'),
('yuna@example.com','최유나',NOW()-INTERVAL '5 days',
 '{"tier":"bronze","tags":["new"],"ui":{"theme":"light"}}'),
('dohyuk@example.com','정도혁',NOW()-INTERVAL '2 days',
 '{"tier":"silver","tags":["early-adopter"]}');

3-2. events 데이터

INSERT INTO events (user_id,event_type,created_at,payload) VALUES
(1,'LOGIN',NOW()-INTERVAL '3 days','{"ip":"10.0.0.1","device":"mobile"}'),
(1,'ORDER_CREATED',NOW()-INTERVAL '2 days','{"order_id":1001,"amount":198000}'),
(2,'LOGIN',NOW()-INTERVAL '1 day','{"ip":"10.0.0.2","device":"pc"}'),
(3,'ORDER_PAID',NOW()-INTERVAL '12 hours','{"order_id":1002,"amount":279000,"method":"CARD"}'),
(3,'LOGOUT',NOW()-INTERVAL '1 hour','{"duration_sec":3600}');

3-3. payments_raw 데이터

INSERT INTO payments_raw (provider,created_at,payload) VALUES
('KCP',NOW()-INTERVAL '5 days',
 '{"tid":"TID-10001","result":{"code":"0000","message":"APPROVED"},"amount":198000}'),
('KCP',NOW()-INTERVAL '3 days',
 '{"tid":"TID-10002","result":{"code":"9999","message":"DENIED"},"amount":279000,"error":{"reason":"LIMIT_EXCEEDED"}}'),
('NAVERPAY',NOW()-INTERVAL '1 day',
 '{"tid":"NP-20001","result":{"code":"0000","message":"OK"},"amount":59000}');

---

4. JSONB 기본 조회 실습

4-1. JSON 값 조회

SELECT user_id, profile->>'tier' AS tier
FROM users;

profile->>'tier' -> 출력이 gold
profile->'tier' -> 출력이 "glod"

4-2. 중첩 JSON 조회

SELECT user_id, profile->'marketing'->>'email_opt_in'
FROM users;

---

5. JSONB 필터 실습

-- tier = gold
SELECT user_id, name
FROM users
WHERE profile->>'tier' = 'gold';

-- tags에 vip 포함
SELECT user_id, name
FROM users
WHERE profile->'tags' @> '["vip"]'::jsonb;

-- marketing 키 존재 유무
SELECT user_id, name
FROM users
WHERE profile ? 'marketing';

WHERE profile->'tier' = '"gold"'; 이렇게 할 수도 있긴하다.

---

6. JSONB 배열 처리

6-1. 배열 펼치기 (jsonb_array_elements_text)

SELECT user_id, jsonb_array_elements_text(profile->'tags') AS tag
FROM users;

6-2. 배열 기반 필터

SELECT user_id, name
FROM users
WHERE profile->'tags' ? 'vip';

---

7. JSONB 수정(Update) 실습

7-1. 키 추가 (jsonb_build_object)

UPDATE users
SET profile = profile || jsonb_build_object('last_login', NOW())
WHERE user_id = 1;

7-2. 중첩 키 수정 (jsonb_set)

UPDATE users
SET profile = jsonb_set(profile,'{ui,theme}','"dark"'::jsonb,true)
WHERE user_id = 2;

---

8. JSONPath 실습 (PostgreSQL 12+)

-- gold 사용자
SELECT user_id, name
FROM users
WHERE profile @? '$.tier ? (@ == "gold")';

-- 결제 성공
SELECT payment_id, provider
FROM payments_raw
WHERE payload @? '$.result.code ? (@ == "0000")';

---

9. JSONB 인덱스 실습

9-1. GIN 인덱스 (Generalized Inverted Index)

GIN은 PostgreSQL에서 복합 값(배열,JSONB,전문검색)을 빠르게 찾기 위한 특수 인덱스

CREATE INDEX idx_users_profile_gin
ON users USING GIN (profile);

9-2. 표현식 인덱스

CREATE INDEX idx_users_tier
ON users ((profile->>'tier'));

9-3. 인덱스 확인

EXPLAIN ANALYZE
SELECT user_id FROM users
WHERE profile->>'tier' = 'gold';

---

10. 설계 판단 실습 (중요)

JSONB에 두는 게 적절한 것

• 설정
• 옵션
• 태그
• 외부 API 응답
• 로그

컬럼으로 승격해야 할 신호

• WHERE / JOIN / GROUP BY에 자주 등장
• 인덱스 필수
• 비즈니스 핵심 로직 의존

-- 승격 예시
ALTER TABLE users
ADD COLUMN tier TEXT
GENERATED ALWAYS AS (profile->>'tier') STORED;

---

11. 실습 마무리 체크

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM events;
SELECT COUNT(*) FROM payments_raw;

---