GIS Developer

원문출처 : http://www.postgresqltutorial.com/postgresql-json/

이 튜토리얼은 PostgreSQL의 JSON 데이터 타입을 어떻게 처리하는지 설명한다. 추가적으로 JSON 데이터를 처리하기 위한 가장 기본적인 연산자와 함수에 대해서도 설명한다.

JSON은 JavaScript Object Notation이다. JSON으로 다양한 데이터를 표현할 수 있지만 특히 키-값으로 구성된 쉽게 해석할 수 있는 텍스트 형식이다. JSON의 주요 사용처는 서버와 웹 어플리케이션 사이의 데이터 교환 및 전송이다. 바이너리 형식과 다르게 JSON은 사람이 이해하고 읽을 수 있는 텍스트 형식이다.

PostgreSQL은 9.2버전 이후부터 JSON 데이터 타입을 지원하며 JSON 데이터를 조작하기 위한 다양한 함수와 연산자를 제공한다.

JSON 데이터에 대한 이해를 위해 테이블을 생성하는 것부터 시작해 보자.

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

orders 테이블은 2개의 컬럼으로 구성 되어져 있는데, id는 순서를 나타내는 주 키이며 info는 JSON 형식으로 데이터가 저장될 컬럼이다.

JSON 데이터 INSERT

JSON 컬럼에 데이터를 입력하기 위해서 올바른 JSON 포맷의 데이터가 필요하다. 다음 INSERT 구문은 orders 테이블에 새로운 레코드를 추가한다.

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

위의 데이터는 6병의 맥주를 가진 John Doe를 의미한다.

마찬가지로 여러 개의 레코드를 추가해 보자.

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 데이터 쿼리

JSON 데이터를 쿼리하기 위해서 일반적인 다른 데이터 타입을 쿼리 하듯이 SELECT 구문을 사용한다.

SELECT
 info
FROM
 orders;

PostgreSQL은 그 결과를 JSON 형태의 집합으로 반환한다.

PostgreSQL은 JSON 데이터에 대한 쿼리를 돕기 위해 ->과 ->> 연산자를 제공한다.

“->” 연산자는 키로 JSON 데이터 필드를 반환한다.

“->>” 연산자는 텍스트로 JSON 데이터 필드를 반환한다.

다음 쿼리는 JSON 형식으로 모든 고객 정보를 얻기 위해 -> 연산자를 사용한다.

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

그리고 ->> 연산자를 사용하는 다음의 쿼리문은 텍스트 형식으로 모든 고객 정보를 반환한다.

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

-> 연산자가 JSON 객체를 반환하므로, 연관된 노드를 반환시키기 위해 ->> 연산자와 함께 연결 호출이 가능하다. 예를들어 아래의 구문은 판매된 모든 제품을 반환한다.

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

먼저 info->’items’는 JSON 객체로써 항목을 반환한다. 그런 뒤 info->’items’->>’product’는 텍스트 형식으로 모든 product를 반환한다.

WHERE 구문에서 JSON 연산자 사용

조건 검색을 지정하기 위해 WHERE 구문에서 JSON 연산자를 사용할 수 있다. 예를들어 Diaper를 가져온 사람을 얻고자 한다면 다음 쿼리를 사용한다.

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

또 다른 예로써, 다음의 쿼리는 2개의 product를 가지고 있는 레코드를 조회한다.

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

위의 쿼리문에서 주목해야 할 부분은 qty 필드를 숫자 2와 비교하기 위해 INTEGER로 형변환(Casting)했다는 것이다.

JSON 데이터에 집계 함수 적용하기

MIN, MAX, AVERAGE, SUM 등과 같은 집계 함수를 JSON 데이터에 적용할 수 있다. 예를 들어 다음 구문은 최소 개수, 최대 개수, 평균 개수와 전체 판매된 상품의 개수에 대한 쿼리문이다.

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 함수

PostgreSQL은 JSON 데이터를 처리하는데 도움이 되는 함수들을 제공한다.

json_each 함수

json_each 함수는 키-값의 집합으로 JSON 객체를 확장시킨다. 다음 구문을 보자.

SELECT
    json_each (info)
FROM
    orders;

만약 문자형으로 키-값의 집합을 얻고자 한다면, json_each_text 함수를 사용하면 된다.

json_object_keys 함수

JSON 객체의 키 집합을 얻고자 한다면, json_object_keys 함수를 사용한다. 다음의 쿼리는 info 컬럼 안의 items 객체의 모든 키를 반환한다.

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

json_typeof 함수

json_typeof 함수는 문자열로 JSON 값의 타입을 반환하며, 반환값은 number, boolean, null, object, array 그리고 string이다.

다음 쿼리문은 항목의 데이터 타입을 반환한다.

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

다음의 쿼리문은 중첩되어진 JSON 객체의 qty 필드의 데이터 타입을 반환한다.

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

JSON 함수에 대해 좀더 깊이 알고자 한다면, PostgreSQL JSON functions을 참고하기 바랍니다.

이 튜토리얼에서는, PostgreSQL JSON 데이터 타입에 대해 살펴보았다. 또한 JSON 데이터를 좀더 효율적으로 처리하기 위해서 가장 많이 활용되는 JSON 연산자와 함수에 대해서도 살펴보았다.

글

PostgreSQL 유용한 쿼리

특정 데이터베이스의 테이블 목록 조회

PostgreSQL JSON