[Postgresql] JSON 데이터 다루기

JSON 데이터 타입을 다루기 위한 PostgreSQL 연산자 혹은 함수에 대해서 알아보고자 합니다.

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

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

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

방금 생성한 Orders 테이블은 두 개의 칼럼이 있습니다.

1. Id : orders 테이블의 식별자
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 입니다.

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 칼럼의 item이라는 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;