PostgreSQL의 JSON/JSONB 배운 것 정리

 

1. JSON과 JSONB의 차이점

항목	JSON	JSONB
저장 방식	텍스트를 그대로 저장	이진(Binary) 형식으로 저장
검색 성능	느림	빠름(인덱싱 가능)
키 순서	보존함	보존하지 않음
중복 키	허용	마지막 키만 유지됨
사용 추천	로깅 등 읽기 전용	대부분의 경우 JSONB 권장

 

2. JSON 데이터 조회의 주요 연산자

연산자	설명
->	JSON 객체에서 값 추출 (JSON 형식 그대로 반환)
->>	문자열로 추출
#>	중첩 경로로 접근
#>>	중첩 경로에서 문자열 추출
?	해당 키 존재 여부 확인
@>	포함 여부 확인
JSONB_ARRAY_LENGTH()	배열 길이 구하기
||	병합 연산자로 두 JSONB 값을 합쳐서 하나의 JSONB 객체로 만드는 역할 (JSONB 전용)

 

2-1. 연산자 활용 관련

상황	사용 예
JSON 컬럼에서 특정 필드 값만 추출	profile ->> 'name'
배열 길이 계산	JSONB_ARRAY_LENGTH(profile -> 'hobbies')
필드 존재 여부 검사	profile ? 'email'
키-값 조건으로 필터링	profile -> 'hobbies' ? 'reading'

 

3. 예시 코드

3-1. 테이블 생성 및 데이터 삽입

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  profile JSONB
);

INSERT INTO users (profile) VALUES
('{"name": "Taco", "age": 30, "city": "Budapest"}'),
('{"name": "Giga", "age": 25, "city": "Tbilisi", "hobbies": ["reading", "climbing"]}');

 

3-2. JSON 생성 함수

SELECT JSON_BUILD_OBJECT('name', 'Taco', 'age', 30, 'city', 'Budapest');
-- → {"name": "Taco", "age": 30, "city": "Budapest"}

 

3-3. 기본 조회

SELECT 
  profile ->> 'name' AS name,
  profile -> 'city' AS city,
  profile -> 'hobbies' -> 0 AS first_hobby
FROM users;

 

3-4. 특정 키 존재 여부 확인

SELECT * FROM users WHERE profile ? 'hobbies';

 

3-5. 배열 안에 특정 값 포함 확인

SELECT * FROM users WHERE profile -> 'hobbies' ? 'climbing';

 

3-6. 여러 키 중 하나라도 포함 확인

SELECT * FROM users WHERE profile ?| ARRAY['reading', 'traveling'];
SELECT * FROM users WHERE profile ?| ARRAY['name', 'email'];

 

3-7. 문자열 필터링

SELECT * FROM users WHERE profile ->> 'city' LIKE 'B%';

 

3-8. 키 추가/병합

UPDATE users
SET profile = profile || JSONB_BUILD_OBJECT('email', 'x@x.com');

 

3-9. 키 삭제

UPDATE users
SET profile = profile - 'email'
WHERE profile ->> 'name' = 'Giga';

 

3-10. 배열 값 덮어쓰기

UPDATE users
SET profile = profile || JSONB_BUILD_OBJECT(
  'hobbies',
  JSONB_BUILD_ARRAY('climbing', 'traveling')
)
WHERE profile ->> 'name' = 'Taco';

 

3-11. 배열에 요소 추가 (덧붙이기)

UPDATE users
SET profile = profile || JSONB_SET(
  profile,
  '{hobbies}',
  (profile -> 'hobbies') || JSONB_BUILD_ARRAY('cooking')
);

 

---

관련 문서

• JSON Types