PostgreSQL 9.2부터 JSON 데이터 타입이 추가되어서 칼럼에 JSON 객체를 저장할 수 있다. 이는 아주 유용한 기능인데 RDB의 기능을 그대로 쓰면서 NoSQL처럼 Schemaless의 이점을 취할 수 있다. 관계형 디비를 사용할 때 어려운 점 중 하나가 칼럼을 추가해야 할 때인데 이미 운영 중이라면 특히 처리가 쉽지 않고 개발 중인 경우에는 계속해서 스키마가 달라질 가능성이 높으므로 처리가 어려운데 이러한 부분을 JSON으로 저장해 버릴 수 있다.
이 기능을 써보기 위해서 약간 테스트해본 결과 처음 JSON이 추가된 9.2와 9.3에서 사용방법이 많이 달라진 것으로 보인다. 자세히는 모르지만, 그동안 찾아본 내용을 보면 PostgrSQL은 JSON 데이터를 문자열로 저장하고 조회하는 등의 처리를 할 때 전용 JSON 함수 등을 사용해야 하는데 데이터를 저장하는 건 같지만 이 관련 오퍼레이터가 9.3에서 많이 달라졌다. 9.2는 안 써봤지만 9.2에선 오퍼레이터가 거의 없었던 듯하고 9.3에서 사용할 만 해진 것 같다.
JSON 데이터 타입
CREATE TABLE posts
(
id serial NOT NULL,
meta JSON
);
위 posts 테이블에서 meta라는 칼럼을 JSON으로 지정했다. 이 테이블에는 다음 쿼리로 JSON 객체를 담을 수 있다.
INSERT INTO posts (id, meta)
VALUES (
1,
'{
"author": "outsider",
"authorId": 43434,
"sns": {
"facebook": "https://facebook.com",
"twitter": "https://twitter.com"
},
"createAt": "2014-06-14",
"category": ["pg", "tech", "dev"]
}'
);
문자열로 값을 넣기는 하지만 JSON으로 정의했으므로 유효한 JSON이 아니면 인서트할 때 오류가 발생한다.
JSON 데이터 타입 조회
전체를 다시 조회할 때는 평소에 사용하던 SELECT 쿼리와 다를 바 없고 각 언어에서 사용하는 PostgreSQL 드라이버나 라이브러리가 JSON 데이터 타입을 지원한다면 크게 할 일은 없을 것이다. 대신 JSON 데이터의 특정 키만 조회한다거나 하면 PostgreSQL에서 지원하는 JSON 오퍼레이터를 사용해야 한다.
SELECT meta->'author' FROM posts;
-- "outsider"
SELECT meta->'sns'->'facebook' FROM posts;
-- "https://facebook.com"
SELECT meta->'sns'->>'facebook' FROM posts;
-- https://facebook.com
SELECT meta#>'{category, 2}' FROM posts;
-- "dev"
JSON 데이터의 값을 조회하려면 ->와 ->>를 위처럼 사용해야 한다. 위에처럼 JSON 객체를 탐색해서 들어가면 값을 조회할 수 있다. ->는 JSON 배열이나 JSON 객체를 반환하고 ->>는 JSON 배열이나 객체를 텍스트로 반환한다. 그래서 meta->'sns'->>'facebook'처럼 사용할 수는 있지만 meta->>'sns'->'facebook'처럼은 사용할 수 없다. #>는 배열 내의 값을 조회하는 오퍼레이터이다. 그래서 meta#>'{category, 2}'와 같이 사용하면 meta JSON내에서 category키의 배열 값에서 3번째 값을 반환한다. 그리고 여기서 meta는 칼럼 명이므로 그냥 써도 되지만 이후의 킷값은 따옴표로 묶어주지 않으면 오류가 발생한다. 처음에 보면 문법이 난해하긴 한데 동작방식을 이해하고 나면 어렵지는 않다.
JSON 키에 대한 인덱스
JSON 안에 들어있는 값을 기준으로 조회하고 싶을 수 있다. 예를 들어 위 posts테이블에서 meta의 authorId로 조회를 한다고 하면 인덱스를 생성하지 않으면 속도에 큰 문제가 있을 것이다. 이런 경우를 위해서 JSON내의 값으로 인덱스를 생성할 수 있다.
CREATE INDEX posts_idx ON posts((meta->>'authorId'));
일반적인 인덱스 생성쿼리와 같고 컬럼명대신 위에서 살펴본 오퍼레이터로 JSON의 키를 지정하면 인덱스를 만들 수 있다. 여기서 주의할 점은 깊이는 여러 단계로 찾아 들어갈 수 있지만, 마지막은 ->>로 끝나야 한다. ->를 사용하면 data type json has no default operator class for access method "btree"라는 오류가 발생한다. 그래서 sns안에 facebook에 인덱스를 걸려면 meta->'sns'->>'facebook'처럼 사용해야 하고 위 코드에서 괄호가 2개 들어간 것은 꼭 사용해야 하는 부분이다.
참고로 이제 막 사용해 본 상황이라 인덱스의 사용이나 JSON의 키를 이용한 조회의 성능 같은 부분은 아직 제대로 확인해 보지 못했다.
Comments