cd

PostgreSQL JSONB 실습

4 min read

실습 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;

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';

6. JSONB 배열 처리

6-1. 배열 펼치기

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. 키 추가

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

7-2. 중첩 키 수정

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 인덱스

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;

그래프 뷰

백링크