JSON in Postgres

PostgreSQL의 JSON vs JSONB 타입

타입	설명	특징
JSON	텍스트 그대로 저장	입력 순서 보존, 사람이 읽기 쉬움
JSONB	이진(binary) 형태로 저장	더 빠른 검색, 키 순서 보장 안됨

---

JSON 필드 생성

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB
);

JSON 데이터 삽입

-- 더블 쿼트 사용
INSERT INTO products (name, attributes)
VALUES (
    'T-Shirt',
    '{"color": "red", "size": "M", "stock": 50}'
);

JSON 필드에서 데이터 조회

• JSON 객체 접근 (결과는 JSON)
• >> : JSON 객체 접근 (결과는 텍스트)

SELECT
  name,
  attributes->>'color' AS color,
  attributes->>'size' AS size
FROM products;

---

JSON 조건 검색

-- stock이 50인 제품 검색
SELECT *
FROM products
WHERE (attributes->>'stock')::int = 50;

또는 @> 연산자 사용:

-- color가 red인 제품 검색
SELECT *
FROM products
WHERE attributes @> '{"color": "red"}';

---

JSON 필드 업데이트

PostgreSQL 14부터는 jsonb_set 함수를 사용해 값을 바꿀 수 있습니다.

• '{stock}': JSON key 경로
• '100': 새 값 (문자열이어야 함)
• true: 키가 없을 경우 생성 여부

-- stock 값을 100으로 수정
UPDATE products
SET attributes = jsonb_set(attributes, '{stock}', '100', true)
WHERE name = 'T-Shirt';

---

JSON 배열 다루기

• ? 연산자: JSON 배열에 특정 요소가 있는지 확인

INSERT INTO products (name, attributes)
VALUES (
    'Sneakers',
    '{"colors": ["black", "white", "blue"]}'
);

-- 배열 요소에 특정 값 포함 여부 확인
SELECT *
FROM products
WHERE attributes->'colors' ? 'blue';

---

JSON 인덱스 설정

성능 향상을 위해 JSONB 컬럼에 인덱스를 설정할 수 있습니다.

• GIN 인덱스는 @>, ?, jsonb_path_ops 등에 최적화되어 있음

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

---