PostgreSQL에서 JSON 데이터 추출하기

PostgreSQL에서 JSON 데이터를 다루는 방법에 대해 알아보겠습니다.

 

JSON 데이터는 웹 개발, 모바일 애플리케이션, API 등 다양한 분야에서 사용되고 있으며, 데이터 저장 및 전송에 효율적인 형태로 널리 알려져 있습니다. 

 

아래 쿼리를 사용하여 직원과 부서 정보가 저장된 예시 테이블을 생성하겠습니다.

CREATE TABLE employee_department (
    emp_id SERIAL PRIMARY KEY,
    dept_id INT,
    office_location JSON
);

INSERT INTO employee_department (emp_id, dept_id, office_location)
VALUES (1, 10, '{"locations": [{"country": "KR", "city": "서울", "full_time": true, "part_time": false}, {"country": "US", "city": "New York", "full_time": true, "part_time": false}]}'),
       (2, 20, '{"locations": [{"country": "JP", "city": "도쿄", "full_time": false, "part_time": true}, {"country": "UK", "city": "London", "full_time": true, "part_time": true}]}'),
       (3, 30, '{"locations": [{"country": "FR", "city": "Paris", "full_time": true, "part_time": false}, {"country": "CA", "city": "Toronto", "full_time": false, "part_time": true}]}'),
       (4, 40, '{"locations": [{"country": "AU", "city": "Sydney", "full_time": true, "part_time": true}, {"country": "DE", "city": "Berlin", "full_time": true, "part_time": false}]}');

 

이 테이블에는 직원 ID, 부서 ID, 그리고 각 직원이 근무하는 지역 정보가 JSON 형태로 저장됩니다.

예시 데이터

 

JSON 데이터를 처리하는 쿼리를 작성해 보겠습니다. 

PostgreSQL에서는 'json_array_elements' 함수를 사용하여 JSON 배열의 각 요소를 처리할 수 있습니다.

SELECT
    e.emp_id,
    e.dept_id,
    (l->>'country')::text as country,
    (l->>'city')::text as city,
    (l->>'full_time')::boolean as full_time,
    (l->>'part_time')::boolean as part_time
FROM
    employee_department as e
CROSS JOIN
    json_array_elements(e.office_location->'locations') as l;

위 쿼리에서는 json_array_elements 함수를 사용하여 JSON 배열의 요소를 추출합니다. 

그리고 ->> 연산자를 사용하여 JSON 객체의 특정 키 값을 추출하며, 적절한 데이터 타입으로 변환합니다.

 

결과는 아래와 같습니다.

JSON 배열 요소 추출

실제 프로젝트에서는 더 복잡한 JSON 데이터 구조를 마주할 수 있으며, 이를 처리하기 위해 추가적인 함수와 연산자를 사용해야 할 수도 있습니다.