JSON·jsonb 다루기 — ->·->>·@>

API 응답·로그·설정·메타데이터 — "스키마가 자주 바뀌거나 모양이 들쭉날쭉" 한 데이터를 정식 컬럼으로 만들기 부담스러울 때 jsonb 가 답입니다. 12편은 핵심 연산자 5개, jsonb_set, GIN 인덱스, 그리고 "언제 jsonb 가 적절한가" 의 가이드까지.

json vs jsonb — 항상 jsonb

거의 항상 jsonb. json 은 "원본을 글자 단위로 보존해야 한다" 같은 특수 사례에만.

테이블 만들고 데이터 넣기

CREATE TABLE products (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  meta JSONB NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO products (name, meta) VALUES
  ('티셔츠', '{"size": "L", "color": "black", "tags": ["sale", "new"]}'),
  ('컵',     '{"size": "M", "color": "white", "tags": ["hot"], "stock": 5}'),
  ('볼펜',   '{"color": "blue"}');

5가지 핵심 연산자

-- 색깔만 뽑기
SELECT name, meta->>'color' AS color FROM products;

-- 사이즈 L 만
SELECT name FROM products WHERE meta->>'size' = 'L';

-- 또는 contains 연산자
SELECT name FROM products WHERE meta @> '{"size":"L"}';

-- 태그 첫 번째 항목
SELECT name, meta#>>'{tags,0}' AS first_tag FROM products;

-- 특정 태그 포함 검사 (jsonb 배열)
SELECT name FROM products WHERE meta->'tags' ? 'sale';

jsonb_set — 부분 수정

-- meta.color 를 'red' 로 바꾸기
UPDATE products
SET    meta = jsonb_set(meta, '{color}', '"red"')
WHERE  id = 1;

-- 깊은 경로
UPDATE products
SET    meta = jsonb_set(meta, '{address,city}', '"서울"', true)
WHERE  id = 1;
-- create_missing=true → 경로 없으면 만듦

-- 키 삭제
UPDATE products SET meta = meta - 'stock' WHERE id = 2;
UPDATE products SET meta = meta #- '{address,city}' WHERE id = 1;

-- 객체 합치기 (||)
UPDATE products SET meta = meta || '{"featured":true}' WHERE id = 1;

GIN 인덱스 — jsonb 검색 가속

-- 전체 jsonb 에 인덱스 (모든 키·값 검색)
CREATE INDEX ix_products_meta ON products USING GIN (meta);

-- @> ? ?| ?& 연산자가 인덱스 사용
SELECT * FROM products WHERE meta @> '{"color":"black"}';   -- 빠름

-- 특정 키만 (path ops 더 작음)
CREATE INDEX ix_products_meta_path ON products USING GIN (meta jsonb_path_ops);

-- 특정 키만 따로 — B-tree 표현식 인덱스
CREATE INDEX ix_products_color ON products ((meta->>'color'));

jsonb 집계 — 배열로 모으기

-- 카테고리별 jsonb 배열로 합치기
SELECT category_id,
       jsonb_agg(meta) AS all_meta,
       jsonb_object_agg(name, meta) AS by_name
FROM   products
GROUP BY category_id;

-- 배열 풀기 (jsonb_array_elements)
SELECT id, t.tag
FROM   products,
       jsonb_array_elements_text(meta->'tags') AS t(tag);
-- 한 상품의 태그 N 개를 N 행으로 풀어줌

jsonb 의 함정 4가지

"이 데이터 jsonb 로 갈까 정식 컬럼으로 갈까" 가이드