Supabase 데이터 전처리 :: 여러 컬럼을 하나의 jsonb로 병합하기

목차

• 주의
• 상황
• 개선
  • 사이드 바 - SQL Editor 클릭
  • New Query 클릭
  • Query 정보 변경
  • 병합명령어 입력
  • 기존 컬럼 삭제하기
• 복구하기
  • 삭제한 컬럼 다시 만들기
  • jsonb 데이터 다시 컬럼으로 분리하기

주의

혹시 모르니 데이터 백업은 꼭 하고 진행할 것.

전체 데이터 선택 후 export CSV를 해서 파일로 남겨두면 추후에 다시 import 할 수 있음.

상황

 

• 사진과 같이 지출내용1~3이라는 컬럼에 각 내용을 하나씩 관리하고 있었다.
• 이 내용은 단순한 스프레드시트로 관리하고 있던 것이라 한 셀에 json 형식으로 관리하고 있지 않았다.
• 이렇게 되면 지출내용을 사용자가 여러 개 입력하면 입력한 만큼 컬럼을 미리 만들어줘야 하고, 입력값이 없는 경우에는 null이 너무 많이 생긴다.

개선

supabase와 같은 SQL은 json 보다 jsonb 형식을 더 권장한다고 경고창이 나온다. 아래와 같은 형식으로 입력 가능하다.

{
  "details": [
    "<나몽이> 중성화 수술 책임비 입금",
    "2번 내용",
    "3번 내용",
  ]
}

이렇게 되면 사용자가 몇 개를 입력했든 데이터 테이블을 좀 더 자유롭게 배열 형태로 사용할 수 있다.

 

그런데 본인은 이런 데이터의 레코드가 575개이다. 수작업으론 불가능에 가깝다.

다행이도 supabase는 SQL 쿼리를 지원하기 때문에 아래와 같은 명령어로 한꺼번에 개선해보겠다.

 

supabase web에서 수동으로 입력해도 되는 경우, jsonb는 큰 따옴표만 됨.

 

사이드 바 - SQL Editor 클릭

New Query 클릭

Query 정보 변경

혹시라도 쿼리가 잘못 되었을 때 쿼리만 삭제하면 복구되지 않는다.

 

지금은 해당사항 없지만 복구하는 명령어를 다시 입력해야 할 수 있으니,  습관적으로 쿼리명과 설명을 자세히 기록하는 습관을 들이도록 한다.

병합명령어 입력

UPDATE bankstatement
SET details = jsonb_build_object(
    '지출내용1', 지출내용1,
    '지출내용2', 지출내용2,
    '지출내용3', 지출내용3
);

 

• bankstatement : 진행할 데이터 테이블 이름으로 바꾼다.
• SET details : 새로 만들 컬럼명을 지정한다.
• '지출내용1', 지출내용1, : 어떤 컬럼의 내용을 넣을 것인지 입력한다. 따옴표 안에 있는 지출내역은 미리 만들어 놓을 컬럼 개수이다. 본인은 default로 최대 개수를 3개까지는 사용하고 있기 때문에 3개까지는 만들어 두겠다. 필요에 따라 1개만 만들어 두고 코드 작성 시에 새로 만들어서 밀어 넣어도 될 것 같다.

 

다시 데이터 테이블로 가서 보면 마법과 같은 일이 벌어져있을 것이다.

기존 컬럼 삭제하기

이제 지출내용1~3은 필요 없으니 수동으로 supabase에서 삭제해줘도 되고, 개수가 많다면 SQL 쿼리로 한 번에 작업해도 된다.

ALTER TABLE bankstatement
DROP COLUMN 지출내용1,
DROP COLUMN 지출내용2,
DROP COLUMN 지출내용3;

 

개인적으로는 쿼리로 작업하는 것이 좋을 것 같다.

왜냐면 쿼리 명령어의 기록이 남기 때문에 추후 문제가 생기면 어느 과정에서 문제가 생겼는지 추적하기가 쉬울 것으로 생각된다.

복구하기

삭제한 컬럼 다시 만들기

ALTER TABLE statement
ADD COLUMN 지출내용1 text,
ADD COLUMN 지출내용2 text,
ADD COLUMN 지출내용3 text;

jsonb 데이터 다시 컬럼으로 분리하기

UPDATE statement
SET
    지출내용1 = details->>'지출내용1',
    지출내용2 = details->>'지출내용2',
    지출내용3 = details->>'지출내용3';