[Query] JSON 데이터 처리 방식 : PostgreSQL / Oracle

 

 

회사에서 작업하던 MyBatis 쿼리에서 처음 보는 형식의 쿼리를 발견했다. 내용을 살펴보니 JSON 연산자와 관련된 부분이었다. 현재 회사 DB에서는 리소스의 세부 속성들을 props라는 컬럼에 JSON 형태로 그대로 저장하고 있었고, 이 JSON 데이터를 추출하는 데 이 연산자가 사용되고 있었다. 이번 기회에 JSON 데이터를 다루는 필요성과 함께 PostgreSQL과 Oracle의 JSON 처리 방식을 정리해 보려고 한다.

 

1. JSON 데이터 처리의 필요성

JSON은 현대 애플리케이션에서 데이터 교환과 저장에 널리 사용되는 포맷이다. 유연한 구조와 가독성 덕분에 REST API, 웹 애플리케이션, 데이터베이스 등 다양한 환경에서 중요한 역할을 한다. PostgreSQL과 Oracle은 JSON 데이터를 저장하고 처리할 수 있는 기능을 제공하며, 각각의 처리 방식과 특징이 다르다.

---

2. PostgreSQL - JSON 처리 방식

나는 데이터타입은 그냥 일반적인 text타입으로 되어있는데 찾아보니까 JSON과 JSONB라는 두 가지 데이터 타입을 제공한다고 한다. 그럼 그 두 타입의 차이점은 뭘까?

1) JSON과 JSONB의 차이점

• JSON
  • 입력 데이터를 텍스트 형식 그대로 저장하며, 데이터의 유효성만 검증한다.
  • 저장 속도가 빠르지만, 조회 및 검색 성능은 상대적으로 느리다.
• JSONB
  • 데이터를 바이너리 형식으로 변환하여 저장한다. 이를 통해 더 효율적인 인덱싱과 빠른 검색 속도를 제공한다.
  • 저장 시 추가 변환 작업이 필요하기 때문에 속도가 비교적 느릴 수 있다.

두 타입에 대해서는 위와 같이 간단히 찾아보기만 하고, 가장 중요한 JSON 연산자를 살펴보자.

 

2) PostgreSQL JSON 연산자

PostgreSQL에서는 JSON 데이터를 처리하기 위한 다양한 연산자를 제공한다.

• -> : JSON 객체에서 특정 키를 지정해 값을 JSON 형식으로 반환한다.
• ->> : JSON 객체에서 특정 키를 지정해 값을 문자열 형식으로 반환한다.
• #> : JSON 경로를 지정해 중첩된 값을 추출한다.

 

3) PostgreSQL JSON 함수

PostgreSQL은 JSON 데이터를 다룰 때 활용 가능한 특정 함수도 제공한다.

• jsonb_set : JSONB 객체의 특정 키 값을 업데이트한다.
• jsonb_array_elements : JSON 배열을 개별 요소로 분리한다.
• jsonb_object_keys : JSON 객체의 모든 키를 반환한다.

<예시>

SELECT props::json->'setter'->'selectItem'->>'path' as path FROM TEST_Instance;
// props 컬럼에서 setter 키의 selectItem.path 값을 문자열로 반환
        
SELECT jsonb_array_elements('[1, 2, 3]'::jsonb);
// 1, 2, 3로 각각 반환

SELECT jsonb_set('{"a": 1, "b": 2}'::jsonb, '{b}', '3'::jsonb);
// {"a": 1, "b": 3}

 

4) PostgreSQL JSON 데이터 처리의 특징

• 효율적인 데이터 저장과 처리
  • 바이너리로 변환해서 저장하는 JSONB라는 타입은 데이터를 검색하거나 필터링할 때 더 빠르게 처리되도록 도와준다. 즉, 대용량 데이터도 빠르게 찾아볼 수 있다.
• 유연한 구조
  • PostgreSQL에 JSON을 저장하면 테이블의 스키마(구조)를 변경하지 않고도 데이터를 저장할 수 있다.
• 특정 연산자 지원
  • 키와 값 탐색, 중첩 구조 처리, 배열 관리 등 다양한 기능 제공. 그렇지만 길어질 수록 코드가 복잡해질 수 있다.
• JSONB 변환 비용
  • 저장 시 텍스트 데이터를 바이너리로 변환하므로 초기 저장 성능이 다소 느릴 수 있다.
• 복잡한 경로 표현
  • SQL 표준 경로($.path.to.key)가 아닌 연산자 기반 접근 방식은 가독성이 떨어질 수 있다.
• 호환성 문제
  • 다른 RDBMS와의 JSON 쿼리 호환성이 낮을 수 있다.

---

그다음으로는 Oracle의 json 처리 방식에 대해 알아보자.

3. Oracle - JSON 처리 방식

Oracle은 JSON 데이터를 저장하고 처리하기 위해 CLOB, BLOB, VARCHAR2와 같은 문자열 형식을 사용하며, JSON 데이터를 유효성 검사할 수 있는 IS JSON 제약 조건을 제공한다. 또한 SQL 표준 경로($.key1.key2)와 강력한 JSON 함수를 통해 데이터를 추출하고 가공하는 기능을 제공한다.

1) Oracle JSON 함수

Oracle은 JSON 데이터를 다루기 위한 다양한 함수를 제공하며, SQL 표준 경로($.path.to.key)를 사용해 JSON 데이터에 접근한다.

• JSON_VALUE : JSON 경로를 사용해 특정 값을 추출하며, 반환 값은 스칼라(숫자, 문자열 등)이다.
• JSON_QUERY : JSON 경로를 사용해 JSON 객체나 배열을 반환한다.
• JSON_TABLE : JSON 데이터를 테이블 형태로 변환하며, 정규화된 데이터처럼 쿼리할 수 있다.

<예시>

SELECT JSON_VALUE(props, '$.setter.selectItem.path') AS path FROM TEST_Instance;   
// props에서 JSON 경로에 따라 path 값을 추출

SELECT JSON_QUERY(props, '$.setter.selectItem') AS selectItem FROM TEST_Instance;    
// JSON 객체나 배열 데이터를 그대로 반환

SELECT JSON_VALUE(props, '$.setter.selectItem.path') AS path FROM TEST_Instance;
// props 컬럼에서 $로 시작하는 JSON 경로를 탐색하여 setter.selectItem.path 값을 반환

 

2) Oracle JSON 데이터 처리의 특징

• SQL 표준 경로 지원
  • JSON 경로($.key1.key2)를 사용하므로 가독성이 높고, 직관적인 쿼리 작성이 가능하다.
• 다양한 함수 제공
  • JSON_VALUE, JSON_QUERY, JSON_TABLE 등 다양한 함수를 통해 JSON 데이터를 유연하게 처리할 수 있다.
• 테이블 변환 가능
  • JSON 데이터를 정규화된 테이블 형태로 변환하여 일반적인 SQL 쿼리처럼 다룰 수 있다.
• 성능 제약
  • JSON 데이터를 문자열로 저장하므로, PostgreSQL의 JSONB에 비해 저장 및 조회 성능이 낮을 수 있다.
• 복잡한 설정 필요
  • JSON 데이터를 효율적으로 사용하려면 IS JSON 제약 조건 추가 및 적절한 인덱싱 설정이 필요하다.
• 인덱스 최적화 필요
  • JSON_VALUE 기반의 인덱스 생성이 필요하며, 복잡한 경로나 조건에서는 성능 최적화가 까다로울 수 있다.

---

4. 결론

JSON 데이터 처리를 통해 스키마를 변경하지 않아도 유연하게 데이터를 관리할 수 있다. PostgreSQL과 Oracle의 JSON 기능은 아무래도 RDBMS의 안정성과 NoSQL의 유연성을 결합해 데이터를 효율적으로 다루게 해주는 큰 장점이 아닐까. 그렇지만 JSON 데이터를 처리하는 기능은 PostgreSQL과 Oracle에서 각각 특정 버전 이상부터 지원하니까 잘 확인하고 사용해야할 것 같다.

• postgresql : 9.3 이상부터 지원
• oracle : 12.1.0.2 이상부터 지원