PostgreSQL JSON – 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로 형변환했다는 것이다.

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 연산자와 함수에 대해서도 살펴보았다.