[TIL] PostgreSQL JSONB 타입으로 리스트 저장하기

 

음식점 테이블을 설계하면서 "휴무일"컬럼 타입을 고민하던 중에 새롭게 알게 된 것이 있다.

예를 들어, 음식점을 운영할 때, 가게의 휴무일을 설정하려고 하는데 보통은 휴무가 없거나 하루인 가게들이 많지만, 한 사장님은 워라벨을 중시해서 2일을 쉬고 싶어한다고 가정해보자. 월요일, 목요일을 쉬고 싶어하는데 이 두 요일을 closed_days라는 컬럼 하나에 값을 넣어버린다면 데이터 무결성 조건에 어긋난다. 그렇다고 이 컬럼을 위해 테이블을 하나 더 생성하기에는 ... 좀 무리라고 생각이 들었다. 

그래서 PostgreSQL의 타입을 살펴보다가 JSONB라는 타입을 알게 되었다. 배열 형태를 컬럼 값으로 저장할 수 있다는 것이다!!  

오늘은 postgreSQL을 사용할 때 JSONB 타입의 컬럼에 값을 저장하는 방법에 대해 알아볼 것이다.

---

📌 PostgreSQL에서 closed_days를 JSON 배열로 저장하는 방법

PostgreSQL에서는 JSONB 타입을 사용하여 배열 형태로 저장한다면 보다 유연하게 저장이 가능하다!

 

✅ 1. 테이블 컬럼 수정 (JSON 배열 사용)

📌 closed_days의 타입을JSONB로 설정

 

ALTER TABLE p_stores ALTER COLUMN closed_days TYPE JSONB USING '[]'::JSONB;

 

✔️ JSONB 타입을 사용하면 배열 형태로 저장 가능
✔️ JSON 관련 함수(jsonb_array_elements_text)를 활용하면 필터링도 쉬워짐

 

 

 

 

✅ 2. 데이터 삽입 (INSERT)

📌 closed_days를 JSON 배열로 저장

INSERT INTO p_stores ( store_id, owner_id, category_id, name, address, status, tel, description, open_time, close_time, break_start_time, break_end_time, closed_days ) 
VALUES ( gen_random_uuid(), 1, 'abc-456', '엽떡', '동대문구 123', 'OPEN', '0212341234', '맛있게 맵다! 동대문엽기떡볶이', '10:00', '23:00', '15:00', '17:00', '["월요일", "수요일"]'::JSONB );

 

✔️ ["월요일", "수요일"]을 JSON 배열(JSONB)로 저장

✔️ ::JSONB를 붙여야 PostgreSQL이 JSON 데이터로 인식

 

 

✅ 3. 데이터 조회 (SELECT)

📌 특정 휴무일이 포함된 음식점 조회 (월요일이 휴무인 음식점 찾기)

 

SELECT * FROM p_stores WHERE '월요일' IN (SELECT jsonb_array_elements_text(closed_days));

 

✔️ jsonb_array_elements_text(closed_days)를 사용하면 JSON 배열을 개별 문자열로 변환 가능

✔️ 특정 요일이 포함된 음식점 조회 가능

 

 

📌 특정 요일이 포함되지 않은 음식점 조회 (화요일이 휴무가 아닌 음식점)

 

SELECT * 
FROM p_stores 
WHERE NOT EXISTS ( SELECT 1 FROM jsonb_array_elements_text(closed_days) AS day WHERE day = '화요일' );

 

✔️ NOT EXISTS를 사용하여 특정 요일이 없는 경우만 조회

 

✅ 4. 휴무일 업데이트 (UPDATE)

 

📌 기존 휴무일을 덮어쓰기

UPDATE p_stores SET closed_days = '["화요일", "목요일"]'::JSONB WHERE store_id = '123';

 

 

📌 기존 휴무일에 추가 (예: "금요일" 추가)

UPDATE p_stores SET closed_days = closed_days || '["금요일"]'::JSONB WHERE store_id = '123';

 

✔️ || 연산자를 사용하여 JSON 배열에 새로운 값을 추가 가능

 

 

📌 특정 휴무일 제거 (예: "월요일" 삭제)

UPDATE p_stores SET closed_days = ( SELECT jsonb_agg(day) FROM jsonb_array_elements_text(closed_days) AS day WHERE day != '월요일' ) WHERE store_id = '123';

 

✔️ jsonb_agg(day)를 사용하여 특정 값을 제외하고 다시 JSON 배열로 변환

 

 

 

✅ 5. JSON 데이터를 Spring Boot에서 처리하기

 

📌 closed_days 필드를 **List<String>**으로 매핑
📌 @Column(columnDefinition = "jsonb") 사용하여 JSON 필드 설정

@Entity 
@Table(name = "p_stores") 
public class Store { 
    @Id 
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private UUID storeId; 
    
    @Column(nullable = false) 
    private String name; 
    
    @Column(nullable = false) 
    private String address; 
    
    @Column(nullable = false) 
    private String status; 
    
    @ElementCollection 
    @Column(columnDefinition = "jsonb") 
    private List<String> closedDays = new ArrayList<>(); 
}

✔️ List<String>으로 JSON 데이터를 자동 변환
✔️ Hibernate가 JSON 필드를 자동으로 직렬화 & 역직렬화

 

---

결론적으로, 

 

✅ PostgreSQL에서 휴무일(closed_days)를 JSONB로 저장하면 확장성과 유연성이 뛰어남
✅ JSONB 관련 함수(jsonb_array_elements_text)를 사용하면 휴무일 필터링 가능
✅ Spring Boot에서는 List<String>으로 매핑하여 손쉽게 관리 가능

 

 

 

spring에서 해당 컬럼을 적용해보는 글이다 ! 참고하십쇼 !

https://yeonnnnny.tistory.com/156

[TIL] JSONB 타입의 변수를 가진 엔티티 (Spring Boot - PostgreSQL)