Postgresql JSON 데이터 타입 handling

 

Postgresql DB에서 JSON 다루기

- 꼭 한번씩 본인 로컬 DB에서 실습을 해보시기 바랍니다!!

 

이번 포스팅에서는 PostgreSQL에서 JSON 데이터 타입으로 작업하는 방법을 다룹니다. 또한, JSON 데이터 타입을 다루기 위한 PostgreSQL 연산자 혹은 함수를 소개해보고자 합니다. 

 

JSON은 Javascript Object Notation의 약자입니다. JSON은 키-값 쌍으로 이루어진 개방형 표준형식입니다. JSON은 주로 서버와 웹 어플리케이션 사이에 데이터 이동에 사용됩니다. 다른 형식들과는 달리 사람이 읽고 파악할 수 있는 텍스트로 이루어졌습니다.

 

PostgreSQL은 9.2버전부터 기본 JSON 데이터 타입을 지원하였습니다. JSON 데이터를 다루기 위해 많은 연산자와 함수가 추가되었습니다.

 

새로운 테이블을 생성하여 JSON 데이터 타입을 연습해봅시다.

CREATE TABLE orders (
   ID serial NOT NULL PRIMARY KEY,
   info json NOT NULL
);

 

Orders 테이블은 두개의 컬럼이 있습니다.

 

1. Id : order 테이블의 식별자

2. Info : JSON 형식의 데이터를 저장하는 컬럼.

 

JSON 데이터 삽입하기

- JSON 컬럼에 데이터를 삽입하기 위해 알맞은 형식의 텍스트인지 확인해야합니다. 다음의 insert문은 orders 테이블에 row하나를 추가합니다.

 

INSERT INTO orders (info) VALUES
(
      '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'
);

 

John Doe가 Beer 6개를 주문했다는 의미의 row입니다.

 

여러개를 삽입해볼까요?

 

INSERT INTO orders (info) VALUES
(
      '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
),
(
      '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
),
(
      '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
);

 

 

쿼리로 JSON 데이터 확인하기

다른 데이터 타입을 조회하듯이 select 문을 통해 조회할 수 있습니다.

SELECT info FROM orders;

 

 

JSON 형태의 결과셋을 리턴해줍니다.

 

PostgreSQL은 두가지 기본 JSON 데이터용 연산자를 제공합니다. ( -> / ->> )

 - “->” 연산자는 키 형태로 JSON 객체를 반환합니다.

 - “->>” 연산자는 텍스트 형태로 JSON 객체를 반환합니다.

 

다음의 쿼리는 “->” 연산자를 이용하여 info 컬럼의 customer 키에 해당하는 데이터들을 가져옵니다.(json 타입으로 반환)

SELECT info -> 'customer' AS customer 
FROM orders;

 

 

다음의 쿼리는 “->>” 연산자를 이용해 info컬럼의 customer 키에 해당하는 데이터를 자겨옵니다.( text 타입으로 반환)

SELECT info ->> 'customer' AS customer
FROM orders;

 

이것을 응용해보자면 “->”연산자는 다시 json 타입을 반환하기 때문에 다시한번 “->>” 연산자와 체이닝형식으로 특정 노드를 다시 검색할 수 있습니다.

 

예를 들어, 다음의 쿼리는 주문한 모든 product 를 반환합니다.

SELECT info -> 'items' ->> 'product' as product
FROM orders
ORDER BY product;

 

 

 

처음 info -> ‘items’ 에서 JSON 객체를 리턴하고 이어서 ->> ‘product’ 연산자를 통해 최종적으로 모든 product가 text 데이터 형태로 출력됩니다.

 

where에 JSON 연산자 사용

 - json 연산자는 필터링을 위해 where 절에도 사용할 수 있습니다. 예를 들어, 누가 “Diaper”라는 물건을 샀는지를 확인하기 위해 다음과 같은 쿼리문을 작성할 수 있습니다.

SELECT info ->> 'customer' AS customer
FROM orders
WHERE info -> 'items' ->> 'product' = 'Diaper';

 

 

- 또한 한번에 2개의 물건을 산 사람을 찾기위해 다음과 같이 쿼리문을 작성할 수 있습니다.

SELECT
   info ->> 'customer' AS customer,
   info -> 'items' ->> 'product' AS product
FROM orders
WHERE CAST (info -> 'items' ->> 'qty' AS INTEGER ) = 2

 

Where 절에서 텍스트 타입으로 반환한 qty ‘2’ 값을 integer로 변환하여 숫자 2와 비교해아하는 것을 주의하세요.

 

 

JSON 데이터에 집계함수 사용.

Min, max, average, sum 과 같은 집계함수를 json 타입의 데이터에 사용해보겠습니다. 예를 들어, 아래의 쿼리문은 최소qty, 최대qty, 평균qty, 총qty를 반환할 것입니다.

SELECT
   MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
FROM orders

 

 

 

PostgreSQL JSON 함수

- json_each() 함수

 json_each() 함수는 가장 바깥쪽의 json 정보를 키/값 셋으로 반환해줍니다.

SELECT json_each (info) FROM orders;

 

 

만일 키/값 셋을 text 데이터 타입으로 반환받고싶다면 json_each_text() 함수를 사용할 수 있습니다.

 

- json_object_keys 함수

가장 바깥쪽 json 데이터의 키셋을 얻으려면 json_object_keys() 함수를 사용할 수 있습니다. 아래의 쿼리문은 info 컬럼의 items 라는 json 데이터의 키셋을 얻어옵니다.

SELECT json_object_keys (info->'items')
FROM orders;

 

- json_typeof 함수 (리턴타입 : varchar)

json_typeof() 함수는 json 데이터의 가장 바깥쪽 값들의 타입을 반환해줍니다. number, boolean, null, object, array, string 같은 데이터가 될 수 있습니다. 

 

아래의 쿼리문은 items라는 프로퍼티의 value에 해당하는 데이터 타입을 반환해줍니다.

SELECT json_typeof (info->'items')
FROM orders;

 

 

아래의 쿼리문은 중첩된 items json타입의 키가 qty 에 해당하는 값의 타입을 반환합니다.

SELECT json_typeof (info->'items'->'qty')
FROM orders;

 

 

 

 

참조페이지 : http://www.postgresqltutorial.com/postgresql-json/