PostgreSQL에서 JSON 데이터 다루기

PostgreSQL은 JSON 형식의 데이터를 효과적으로 다룰 수 있는 다양한 함수와 연산자를 제공하며, 특히 JSONB 형식은 효율적인 검색 성능을 제공합니다. JSON 데이터를 처리하는 PostgreSQL 쿼리 방법을 알아보겠습니다.

 

1. JSON 데이터 접근 연산자

PostgreSQL에서는 JSON 데이터를 쉽게 추출할 수 있는 여러 연산자를 지원합니다. 기본 연산자로 원하는 키나 중첩된 키를 선택할 수 있습니다.

• -> : JSON 객체에서 키에 해당하는 값을 JSON 형식으로 가져옵니다.
• ->> : JSON 객체에서 키에 해당하는 값을 텍스트 형식으로 가져옵니다.
• #> : JSON 객체에서 중첩된 키의 값을 JSON 형식으로 가져옵니다.
• #>> : JSON 객체에서 중첩된 키의 값을 텍스트 형식으로 가져옵니다.

 

-- 예시 테이블 생성 및 데이터 삽입
CREATE TABLE test_data (
    data jsonb
);

INSERT INTO test_data (data) VALUES
('{"name": "홍길동", "age": 25, "address": {"city": "부산", "zipcode": "67890"}}');

-- 키에 해당하는 값 가져오기
SELECT data->'name' AS name -- JSON 형식으로 name 값 가져오기
FROM test_data;

SELECT data->>'name' AS name_text -- 텍스트 형식으로 name 값 가져오기
FROM test_data;

-- 중첩된 키에 접근하기
SELECT data#>'{address, city}' AS city -- JSON 형식으로 city 값 가져오기
FROM test_data;

SELECT data#>>'{address, city}' AS city_text -- 텍스트 형식으로 city 값 가져오기
FROM test_data;

 

2. JSON 함수 활용하기

PostgreSQL은 JSON 데이터를 쉽게 조작할 수 있는 여러 함수도 제공합니다.

• jsonb_array_elements() : JSON 배열을 개별 요소로 확장합니다.
• jsonb_object_keys() : JSON 객체의 키 목록을 가져옵니다.
• jsonb_each_text() : JSON 객체의 각 키-값 쌍을 텍스트 형식으로 가져옵니다.

-- JSON 배열의 각 요소 추출하기
INSERT INTO test_data (data) VALUES
('{"items": [{"name": "item1"}, {"name": "item2"}]}');

SELECT elem->>'name' AS item_name
FROM test_data,
LATERAL jsonb_array_elements(data->'items') AS elem;

-- JSON 객체의 키 목록 가져오기
SELECT jsonb_object_keys(data) AS keys
FROM test_data;

-- 각 키-값 쌍 추출하기
SELECT key, value
FROM test_data, 
LATERAL jsonb_each_text(data) AS key_value(key, value);

 

3. JSON 데이터 검색하기

JSON 데이터를 쿼리 조건으로 사용하여 특정 조건에 맞는 데이터를 검색할 수 있습니다.

-- 특정 값 조건으로 검색하기
SELECT *
FROM test_data
WHERE data->>'name' = '홍길동';

-- 중첩된 키 조건으로 검색하기
SELECT *
FROM test_data
WHERE data#>>'{address, city}' = '부산';