Outsider's Dev Story: Database/PostgreSQL 카테고리 글 목록https://blog.outsider.ne.kr/Stay Hungry. Stay Foolish. Don't Be Satisfied.2024-03-15T10:11:41+09:00Textcube 1.10.7 : Tempo primoPostgreSQL의 JSONB 데이터 타입의 배열에서 특정 값 조회하기Outsiderhttps://blog.outsider.ne.kr/11942016-02-14T22:11:04+09:002016-02-14T22:11:00+09:00<p>작년에 <a href="https://blog.outsider.ne.kr/1061">PostgreSQL의 JSON 데이터 타입 오퍼레이터</a>라는 글에서 <a href="http://www.postgresql.org/">PostgreSQL</a> 데이터베이스의 <a href="http://www.postgresql.org/docs/9.3/static/datatype-json.html">JSON 데이터타입</a>에 대해서 간략하게 소개했다. 관계형 데이터베이스의 한계를 벗어난 기분에 JSON 데이터 타입을 마구 남용하다 보면 조만간 지옥을 맛보게 되기는 하지만 적절하게 사용하면 아주 편리한 기능 중 하나이다.<sup id="fnref:1"><a href="https://blog.outsider.ne.kr/1194#fn:1" rel="footnote">1</a></sup></p>
<p>PostgreSQL 9.4부터는 <a href="http://www.postgresql.org/docs/9.4/static/datatype-json.html">JSONB 데이터타입</a>이 추가되었다. 문서에 따르면 Insert는 JSON보다는 느리지만, 바이너리 형식으로 저장되어서 처리가 훨씬 유연하다. 내가 써봤을 때는 특이한 조건이 아니라면 JSON 대신 JSONB를 안 쓸 이유가 없다. 대신 JSON이나 JSONB 형식이나 일반적인 관계형 데이터베이스와는 형식이 다르므로 처음 쓸 때 연산자나 함수에 대해서 익히는 시간이 좀 필요하다.</p>
<p>예를 들어 <code>examples</code>이라는 테이블에서 JSONB로 정의된 <code>meta</code>라는 칼럼에 다음과 같은 데이터가 들어있다고 해보자. JSONB 데이터 타입에 JSON을 보통 다루듯이 JSON 객체를 가진 배열을 넣은 것이다.</p>
<pre class="line-numbers"><code class="language-clike">[{"id": 1, "image": "aaa.jpg"}, {"id": 2, "image": "ddd.jpg"}]
[{"id": 2, "image": "bbb.jpg"}, {"id": 5, "image": "ccc.jpg"}]
[{"id": 1, "image": "ccc.jpg"}, {"id": 3, "image": "bbb.jpg"}]
[{"id": 3, "image": "ddd.jpg"}, {"id": 6, "image": "aaa.jpg"}]
</code></pre>
<p>여기서 <code>id</code>가 <code>1</code>인 데이터를 가진 row를 조회하고 싶은 경우가 있다. 위에서 각 배열은 <code>examples</code> 테이블의 하나의 row인데 여기서 <code>meta</code> 칼럼에 배열을 하나 더 넣은 것이다. 왜 이러한 데이터베이스 디자인을 하는가는 별도의 이야기이고 이를 일반적인 데이터라고 생각하면 포함된 내용의 특정 값으로 조회하고자 하는 기능은 자연스러운 요구사항이다.</p>
<pre class="line-numbers"><code class="language-sql">SELECT * from examples where meta @> '[{"id":1}]';
</code></pre>
<p>여기서는 <code>@></code> 연산자를 사용했다. <a href="http://www.postgresql.org/docs/9.4/static/functions-json.html">문서</a>에 따르면 JSON이 해당 값을 포함하고 있는지를 검사하는 연산자이다. 그러므로 <code>meta</code> 칼럼이 <code>[{"id":1}]</code>를 포함하고 있는지를 검사하는 쿼리이다. 이렇게 조회하면 다음과 같이 <code>id</code>가 <code>1</code>인 결과만 조회된다.</p>
<pre class="line-numbers"><code class="language-clike">[{"id": 1, "image": "aaa.jpg"}, {"id": 2, "image": "ddd.jpg"}]
[{"id": 1, "image": "ccc.jpg"}, {"id": 3, "image": "bbb.jpg"}]
</code></pre>
<p>당연히 다음과 같이 <code>image</code>필드에 있는 값으로 조회할 수도 있다.</p>
<pre class="line-numbers"><code class="language-sql">SELECT * from examples where meta @> '[{"image":"bbb.jpg"}]';
</code></pre>
<pre class="line-numbers"><code class="language-clike">[{"id": 2, "image": "bbb.jpg"}, {"id": 5, "image": "ccc.jpg"}]
[{"id": 1, "image": "ccc.jpg"}, {"id": 3, "image": "bbb.jpg"}]
</code></pre>
<p><br><br></p>
<div class="footnotes">
<hr />
<ol>
<li id="fn:1">
<p><a href="http://dev.mysql.com/doc/refman/5.7/en/json.html">MySQL도 5.7부터 JSON 데이터 타입을 지원</a>한다. <a href="#fnref:1" rev="footnote">↩</a></p>
</li>
</ol>
</div>
<p><strong><a href="https://blog.outsider.ne.kr/1194?commentInput=true#entry1194WriteComment">댓글 쓰기</a></strong></p>PostgreSQL의 JSON 데이터 타입 오퍼레이터Outsiderhttps://blog.outsider.ne.kr/10612014-06-14T22:08:43+09:002014-06-14T22:08:43+09:00<p><a href="http://www.postgresql.org/">PostgreSQL</a> 9.2부터 JSON 데이터 타입이 추가되어서 칼럼에 JSON 객체를 저장할 수 있다. 이는 아주 유용한 기능인데 RDB의 기능을 그대로 쓰면서 NoSQL처럼 Schemaless의 이점을 취할 수 있다. 관계형 디비를 사용할 때 어려운 점 중 하나가 칼럼을 추가해야 할 때인데 이미 운영 중이라면 특히 처리가 쉽지 않고 개발 중인 경우에는 계속해서 스키마가 달라질 가능성이 높으므로 처리가 어려운데 이러한 부분을 JSON으로 저장해 버릴 수 있다.</p>
<p>이 기능을 써보기 위해서 약간 테스트해본 결과 처음 JSON이 추가된 9.2와 9.3에서 사용방법이 많이 달라진 것으로 보인다. 자세히는 모르지만, 그동안 찾아본 내용을 보면 PostgrSQL은 JSON 데이터를 문자열로 저장하고 조회하는 등의 처리를 할 때 전용 JSON 함수 등을 사용해야 하는데 데이터를 저장하는 건 같지만 <a href="http://www.postgresql.org/docs/9.3/static/functions-json.html">이 관련 오퍼레이터가 9.3에서 많이 달라졌다</a>. 9.2는 안 써봤지만 9.2에선 오퍼레이터가 거의 없었던 듯하고 9.3에서 사용할 만 해진 것 같다.<br />
<br></p>
<h1>JSON 데이터 타입</h1>
<pre class="line-numbers"><code class="language-sql">CREATE TABLE posts
(
id serial NOT NULL,
meta JSON
);
</code></pre>
<p>위 <code>posts</code> 테이블에서 <code>meta</code>라는 칼럼을 <code>JSON</code>으로 지정했다. 이 테이블에는 다음 쿼리로 JSON 객체를 담을 수 있다.</p>
<pre class="line-numbers"><code class="language-sql">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"]
}'
);
</code></pre>
<p>문자열로 값을 넣기는 하지만 JSON으로 정의했으므로 유효한 JSON이 아니면 인서트할 때 오류가 발생한다.</p>
<h1>JSON 데이터 타입 조회</h1>
<p>전체를 다시 조회할 때는 평소에 사용하던 <code>SELECT</code> 쿼리와 다를 바 없고 각 언어에서 사용하는 PostgreSQL 드라이버나 라이브러리가 JSON 데이터 타입을 지원한다면 크게 할 일은 없을 것이다. 대신 JSON 데이터의 특정 키만 조회한다거나 하면 <a href="http://www.postgresql.org/docs/9.3/static/functions-json.html">PostgreSQL에서 지원하는 JSON 오퍼레이터</a>를 사용해야 한다.</p>
<pre class="line-numbers"><code class="language-sql">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"
</code></pre>
<p>JSON 데이터의 값을 조회하려면 <code>-></code>와 <code>->></code>를 위처럼 사용해야 한다. 위에처럼 JSON 객체를 탐색해서 들어가면 값을 조회할 수 있다. <code>-></code>는 JSON 배열이나 JSON 객체를 반환하고 <code>->></code>는 JSON 배열이나 객체를 텍스트로 반환한다. 그래서 <code>meta->'sns'->>'facebook'</code>처럼 사용할 수는 있지만 <code>meta->>'sns'->'facebook'</code>처럼은 사용할 수 없다. <code>#></code>는 배열 내의 값을 조회하는 오퍼레이터이다. 그래서 <code>meta#>'{category, 2}'</code>와 같이 사용하면 <code>meta</code> JSON내에서 <code>category</code>키의 배열 값에서 3번째 값을 반환한다. 그리고 여기서 meta는 칼럼 명이므로 그냥 써도 되지만 이후의 킷값은 따옴표로 묶어주지 않으면 오류가 발생한다. 처음에 보면 문법이 난해하긴 한데 동작방식을 이해하고 나면 어렵지는 않다.<br />
<br></p>
<h2>JSON 키에 대한 인덱스</h2>
<p>JSON 안에 들어있는 값을 기준으로 조회하고 싶을 수 있다. 예를 들어 위 <code>posts</code>테이블에서 <code>meta</code>의 <code>authorId</code>로 조회를 한다고 하면 인덱스를 생성하지 않으면 속도에 큰 문제가 있을 것이다. 이런 경우를 위해서 JSON내의 값으로 인덱스를 생성할 수 있다.</p>
<pre class="line-numbers"><code class="language-sql">CREATE INDEX posts_idx ON posts((meta->>'authorId'));
</code></pre>
<p>일반적인 인덱스 생성쿼리와 같고 컬럼명대신 위에서 살펴본 오퍼레이터로 JSON의 키를 지정하면 인덱스를 만들 수 있다. 여기서 주의할 점은 깊이는 여러 단계로 찾아 들어갈 수 있지만, 마지막은 <code>->></code>로 끝나야 한다. <code>-></code>를 사용하면 <strong>data type json has no default operator class for access method "btree"</strong>라는 오류가 발생한다. 그래서 <code>sns</code>안에 <code>facebook</code>에 인덱스를 걸려면 <code>meta->'sns'->>'facebook'</code>처럼 사용해야 하고 위 코드에서 괄호가 2개 들어간 것은 꼭 사용해야 하는 부분이다.</p>
<p>참고로 이제 막 사용해 본 상황이라 인덱스의 사용이나 JSON의 키를 이용한 조회의 성능 같은 부분은 아직 제대로 확인해 보지 못했다.</p>
<p><strong><a href="https://blog.outsider.ne.kr/1061?commentInput=true#entry1061WriteComment">댓글 쓰기</a></strong></p>