PostgreSQL의 jsonb 타입 조회 및 업데이트 방법

PostgreSQL의 jsonb 타입은 JSON 데이터를 저장하고 처리할 수 있는 강력한 기능을 제공합니다. 이 타입은 JSON 데이터를 바이너리 형태로 저장하여 검색과 수정이 더 빠르고 효율적입니다. 다음은 jsonb 타입을 사용하는 데이터 조회 및 업데이트 방법에 대한 설명과 예시입니다.

---

1. 테이블 생성

먼저, jsonb 타입을 포함한 테이블을 생성합니다.

CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    data jsonb
);

 

---

2. 데이터 삽입

테이블에 JSON 데이터를 삽입합니다.

INSERT INTO example_table (data)
VALUES
    ('{"name": "John", "age": 30, "skills": ["Python", "SQL"]}'),
    ('{"name": "Alice", "age": 25, "skills": ["Java", "JavaScript"]}');

---

3. 데이터 조회

3.1. 특정 키의 값 조회

JSONB 데이터에서 특정 키의 값을 추출합니다.

 

SELECT data->>'name' AS name, data->>'age' AS age FROM example_table;


name  | age
------+-----
John  | 30
Alice | 25

3.2. 조건에 따른 데이터 필터링

JSONB 데이터에서 특정 조건을 만족하는 데이터를 조회합니다.

SELECT * 
FROM example_table
WHERE data->>'name' = 'Alice';

3.3. 배열 값 검색

JSON 배열에 특정 값을 포함하는 데이터를 조회합니다.

SELECT * 
FROM example_table
WHERE 'Python' = ANY (jsonb_array_elements_text(data->'skills'));

---

4. 데이터 업데이트

4.1. JSONB에 새 키 추가

기존 데이터에 새 키를 추가합니다.

UPDATE example_table
SET data = jsonb_set(data, '{city}', '"New York"')
WHERE id = 1;

4.2. JSONB 값 수정

기존 키의 값을 수정합니다.

UPDATE example_table
SET data = jsonb_set(data, '{age}', '35')
WHERE id = 1;

4.3. 배열에 값 추가

JSON 배열에 새 값을 추가합니다.

UPDATE example_table
SET data = jsonb_set(
    data,
    '{skills}',
    (data->'skills')::jsonb || '"PostgreSQL"'
)
WHERE id = 1;

4.4. 키 삭제

특정 키를 삭제합니다.

UPDATE example_table
SET data = data - 'city'
WHERE id = 1;

---

5. 복합 예제: 조회와 업데이트

skills 배열에 SQL을 포함하는 사용자들의 나이를 5살 증가시킵니다.

UPDATE example_table
SET data = jsonb_set(data, '{age}', (data->>'age')::int + 5::text::jsonb)
WHERE 'SQL' = ANY (jsonb_array_elements_text(data->'skills'));

---

6. 성능 최적화를 위한 인덱스 생성

jsonb 데이터를 효율적으로 검색하려면 GIN (Generalized Inverted Index) 인덱스를 사용할 수 있습니다.

CREATE INDEX idx_data_jsonb ON example_table USING gin (data);

---

7. 정리

PostgreSQL의 jsonb 타입은 JSON 데이터의 유연한 저장과 강력한 쿼리 기능을 제공합니다. 위의 예제를 참고하여 다양한 방식으로 데이터를 조회하고 업데이트할 수 있습니다.