SQL#1 JSONB 컬럼으로 테이블 대신하기

Published in

bgpworks

3 min readJun 15, 2022

관계형 데이터베이스에서 컬럼값이 복잡한 컬렉션 데이터일 경우 별도의 테이블에 분리해서 저장하는게 기본이다. 인덱싱을 이용해서 성능도 올릴 수 있고, 데이터의 타입(스키마)도 보장해주기 때문에 대부분의 경우에 올바른 선택이라고 할 수 있겠다. 하지만 프로토타이핑 단계이거나 복잡한 쿼리가 필요하지 않은 작은 데이터에 대해서는 이 방법이 오버킬인 면이 없잖아 있다.

이럴 경우 PostgreSQL의 JSONB 타입이 대안이 될 수 있다. 컬렉션 데이터를 JSONB 타입으로 선언된 컬럼에 저장하는 방법이다. 이 방법을 사용하면 구현이 매우 간단해져서 빠르게 기능을 실험해 볼 수 있다.

CREATE TABLE orders (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    items jsonb
);INSERT INTO orders (items) value ('[{"item_id":1,"quantity":1},{"item_id":2,"quantity":2}]'::jsonb);

JSONB 타입의 데이터를 다른 테이블과 JOIN 해서 데이터를 가공하고 싶을 때에는 jsonb_array_elements 나 jsonb_to_recordset 함수등을 활용하면 된다. 예를 들어 my_items 라는 테이블에 있는 name 컬럼을 가져오고 싶으면 아래와 같이 쿼리를 짤 수 있다.

SELECT
  id,
  (SELECT jsonb_agg(t || jsonb_build_object('name', my_items.name))
   FROM jsonb_array_elements(orders.items) t
   JOIN my_items on (t->'item_id')::integer = my_items.id) as items
FROM orders
WHERE id < 100;

그런데 JOIN을 하면 원래 데이터 순서가 보장 되지 않는다는 단점이 있다. 작은 데이터에 대해서는 대채로 원래 순서대로 나오지만, 쿼리 플랜에 따라 얼마든지 결과 데이터의 순서가 바뀔 수 있다. 만약 순서가 중요하다면 with ordinality 옵션을 사용해서 순서를 유지할 수 있다. (PostgresSQL 문서)

SELECT
  id,
  (SELECT jsonb_agg(t.value || jsonb_build_object('name', my_items.name) order by t.ordinality)
   FROM jsonb_array_elements(orders.items) with ordinality t(value, ordinality)
   JOIN my_items on (t.value->'item_id')::integer = my_items.id) as items
FROM orders
WHERE id < 100;

이상으로 PostgreSQL의 JSONB 타입을 활용하여 빠르게 기능을 구현해볼 수 있는 팁을 살펴보았다. 적극적으로 권장할 방법은 아니나, 활용하기에 따라 생산성을 많이 올릴 수 있으니 한번 쯤 활용해 보는 것도 추천한다.

SQL#1 JSONB 컬럼으로 테이블 대신하기

Written by Jeongbong Seo