[SQL] Base64로 인코딩된 JSON, DB에서 바로 열고 배열까지 풀기 (MySQL, Postgre, BigQuery)
개발을 하다 보면 데이터베이스 컬럼에 알아볼 수 없는 문자열이 저장된 경우를 종종 마주합니다.
eyJ1aV9tb2RlIjogImRhcmsiLCAidGFncyI6IFsiU1FMIiwgIkRiIiwgIlRpcCJdfQ==바로 Base64로 인코딩된 JSON 데이터입니다.
보통은 애플리케이션 코드(Java, Python, Node.js 등)에서 데이터를 가져온 뒤 언어 레벨에서 디코딩해서 사용합니다. 하지만 긴급하게 데이터를 확인하거나, 데이터 웨어하우스에서 통계를 내야 할 때는 DB 접속 툴(DBeaver, Datagrip 등)에서 SQL 쿼리만으로 해결해야 하는 순간이 옵니다.
오늘은 MySQL(MariaDB), PostgreSQL 그리고 클라우드 환경인 Google BigQuery에서 이 암호문 같은 데이터를 SQL만으로 자유자재로 다루는 법을 정리해 봅니다.
특히 JSON 배열(['A', 'B'])을 행(Row)으로 펼치는 고급 기법(UNNEST/CROSS JOIN)까지 다룹니다.
1. 이 짓을 도대체 왜, 언제 하나요? (Use Case)
정상적인 DB 설계라면 JSON 데이터를 JSON 타입 컬럼에 넣거나, 정규화해서 테이블로 쪼개 저장하는 것이 맞습니다. 하지만 현실(Legacy)은 그렇지 않을 때가 많습니다.
- 긴급 디버깅 (Hotfix): 운영 중인 서비스의 설정값이 꼬였는데, 로그 찍을 시간도 없이 DB 원천 데이터를 바로 확인해야 할 때.
- 통계 추출 (Analytics): "설정값에 'Dark Mode'를 켜둔 사용자가 몇 명이지?" 처럼, 숨겨진 데이터로 집계 쿼리를 짜야 할 때.
- ELT 데이터 파이프라인: 원천 로그를 일단 Base64 통째로 데이터 레이크(BigQuery 등)에 적재해두고, 나중에 쿼리로 파싱해서 쓰는 경우.
2. MySQL / MariaDB (8.0+)
가정:
TB_USER_LOG테이블의LOG_BLOB컬럼에 Base64 문자열이 들어있습니다.
✅ 조회 및 값 추출 (SELECT)
가장 먼저 해야 할 일은 '문자셋 변환'입니다. Base64를 풀면 바이너리(이진 데이터)가 되는데, 이를 반드시 UTF-8 문자로 변환해줘야 JSON 함수가 먹힙니다.
SELECT
ID,
-- 1단계: 통째로 디코딩해서 눈으로 확인하기
CONVERT(FROM_BASE64(LOG_BLOB) USING utf8mb4) AS FULL_JSON,
-- 2단계: JSON 내부의 특정 값만 콕 집어서 추출하기 (화살표 연산자)
-- JSON_UNQUOTE를 안 쓰면 "dark" 처럼 따옴표가 붙어서 나옵니다.
JSON_UNQUOTE(
JSON_EXTRACT(
CONVERT(FROM_BASE64(LOG_BLOB) USING utf8mb4),
'$.ui_mode'
)
) AS UI_MODE
FROM TB_USER_LOG
WHERE ID = 101;✅ 값 수정 (UPDATE)
데이터를 꺼내서, 수정하고, 다시 묶어서 저장하는 과정을 쿼리 한 방으로 처리합니다.
UPDATE TB_USER_LOG
SET LOG_BLOB = TO_BASE64( -- 3. [마무리] 다시 Base64로 포장
JSON_SET( -- 2. [수정] JSON 값을 변경
CONVERT(FROM_BASE64(LOG_BLOB) USING utf8mb4), -- 1. [준비] 내용물 꺼내기
'$.ui_mode', -- 바꿀 타겟 경로
'light' -- 새로운 값
)
)
WHERE ID = 101;🔥 고급: JSON 배열 평탄화 (JSON_TABLE)
만약 데이터가 {"tags": ["SQL", "Db", "Tip"]} 처럼 배열을 포함하고 있고, 이 태그들을 각각 하나의 행(Row)으로 쪼개고 싶다면 어떻게 해야 할까요?
이때 사용하는 것이 JSON_TABLE입니다. 이 함수는 내부적으로 원본 테이블과 추출된 JSON 데이터 간의 CROSS JOIN(상호 조인)과 유사한 역할을 수행하여 데이터를 뻥튀기(Unnest)합니다.
SELECT
T.ID,
JT.TAG_NAME
FROM
TB_USER_LOG T,
JSON_TABLE(
CONVERT(FROM_BASE64(T.LOG_BLOB) USING utf8mb4), -- 1. 디코딩 대상
'$.tags[*]' -- 2. 배열 경로 ([*]는 전체)
COLUMNS (
TAG_NAME VARCHAR(50) PATH '$' -- 3. 값을 넣을 컬럼 정의
)
) AS JT;결과:
| ID | TAG_NAME |
|---|---|
| 101 | SQL |
| 101 | Db |
| 101 | Tip |
한 줄이었던 데이터가 태그 개수만큼 3줄로 늘어났습니다.
3. PostgreSQL
PostgreSQL은 JSON 처리에 있어 가장 강력하고 엄격한 기능을 제공합니다.
가정:
tb_app_config테이블의config_data컬럼(text 타입)에 데이터가 있습니다.
✅ 조회 및 값 추출 (SELECT)
PostgreSQL은 decode 함수를 사용하며, ::json 또는 ::jsonb로 형변환(Casting)이 필수입니다.
SELECT
id,
-- 1단계: 디코딩 (bytea -> text 변환)
convert_from(decode(config_data, 'base64'), 'UTF8') AS json_raw,
-- 2단계: 값 추출 (화살표 연산자 ->>)
(convert_from(decode(config_data, 'base64'), 'UTF8')::json ->> 'ui_mode') AS ui_mode
FROM tb_app_config
WHERE id = 55;✅ 값 수정 (UPDATE)
jsonb_set 함수를 활용합니다. 수정 시 jsonb 타입으로 변환했다가, 저장할 때는 다시 bytea -> base64로 돌아가야 하는 '형변환 지옥'에 주의하세요.
UPDATE tb_app_config
SET config_data = encode( -- 4. [마무리] 다시 Base64로 인코딩
jsonb_set( -- 3. [수정] JSON 값 변경
convert_from(decode(config_data, 'base64'), 'UTF8')::jsonb, -- 1. [준비]
'{ui_mode}', -- 2. [타겟] 경로 (배열 표기법 사용)
'"light"' -- 새로운 값 (JSON 규격 준수)
)::text::bytea, -- 인코딩을 위해 text -> bytea로 변환
'base64'
)
WHERE id = 55;🔥 고급: 배열 풀기 (CROSS JOIN LATERAL & UNNEST)
PostgreSQL에서 JSON 배열을 행으로 펼칠 때는 CROSS JOIN LATERAL과 jsonb_array_elements 함수를 조합합니다.
이 구문은 "각 행(Row)마다(LATERAL) JSON 배열을 풀어서(unnest) 원본 테이블과 곱하기(CROSS JOIN) 하겠다"는 의미입니다.
SELECT
t.id,
-- 배열 요소에서 텍스트 추출
elem.value #>> '{}' as tag_name
FROM
tb_app_config t
-- CROSS JOIN LATERAL을 사용하여 각 행의 JSON 배열을 순회
CROSS JOIN LATERAL jsonb_array_elements(
convert_from(decode(t.config_data, 'base64'), 'UTF8')::jsonb -> 'tags'
) AS elem
WHERE t.id = 55;핵심 포인트:
CROSS JOIN: 원래 테이블의 1개 행이 배열 요소 N개와 결합되어 N개의 행이 됩니다.LATERAL: 조인하는 오른쪽 서브쿼리(jsonb_array_elements)가 왼쪽 테이블(t)의 컬럼을 참조할 수 있게 해줍니다. 이게 없으면 함수 안에서t.config_data를 못 읽어옵니다.
4. 구글 빅쿼리 (Google BigQuery)
최근 많이 사용하는 BigQuery 같은 클라우드 데이터 웨어하우스(SaaS)에서는 이 과정이 훨씬 더 빈번하게 일어납니다.
데이터 엔지니어링 트렌드가 ELT (일단 적재하고 나중에 변환)로 바뀌면서, 로그나 설정값을 일단 Base64나 통 JSON으로 빅쿼리에 던져놓고 필요할 때 쿼리로 뜯어보는 경우가 많기 때문입니다.
✅ 조회 및 디코딩 (Safe functions)
빅쿼리는 FROM_BASE64 결과가 BYTES 타입이므로 STRING으로 변환해줘야 합니다. 이때 한글 깨짐이나 데이터 오류를 방지하기 위해 SAFE_CONVERT_BYTES_TO_STRING을 쓰면 쿼리가 중간에 죽지 않고 안전합니다.
SELECT
id,
-- 1. Base64 디코딩 (BYTES -> STRING 변환 필수)
SAFE_CONVERT_BYTES_TO_STRING(FROM_BASE64(log_blob)) AS json_str,
-- 2. JSON 값 추출 (JSON_VALUE: 스칼라 값 추출)
JSON_VALUE(
SAFE_CONVERT_BYTES_TO_STRING(FROM_BASE64(log_blob)),
'$.ui_mode'
) AS ui_mode
FROM `project.dataset.tb_user_log`
WHERE id = 101;🔥 핵심: 콤마(,) 하나로 끝내는 배열 풀기 (UNNEST)
빅쿼리는 아예 배열(Array)과 구조체(Struct)를 네이티브로 지원합니다. 특히 CROSS JOIN을 명시하지 않고 콤마(,) 만으로도 묵시적 조인(Correlated Cross Join)이 가능합니다.
JSON 안의 tags 리스트(["A", "B", "C"])를 행으로 펼치는 쿼리는 다음과 같습니다.
SELECT
t.id,
tag -- UNNEST로 풀려난 태그 값
FROM
`project.dataset.tb_user_log` t, -- 콤마(,)가 CROSS JOIN 역할을 함
UNNEST(
JSON_VALUE_ARRAY( -- JSON 문자열 배열을 빅쿼리 ARRAY로 변환
SAFE_CONVERT_BYTES_TO_STRING(FROM_BASE64(t.log_blob)),
'$.tags'
)
) AS tag
WHERE t.id = 101;빅쿼리 특징:
JSON_VALUE_ARRAY: JSON 안의 리스트를 빅쿼리의ARRAY<STRING>타입으로 바로 바꿔줍니다.UNNEST(...): 배열을 행(Row)으로 펼칩니다.- 콤마(,) Join:
FROM 테이블, UNNEST(...)구문으로 가장 직관적인 평탄화가 가능합니다.
5. Snowflake & AWS Athena (Cloud Data Warehouse)
클라우드 데이터 플랫폼의 양대 산맥인 Snowflake와 AWS Athena도 빠질 수 없습니다. 이들은 대용량 로그 분석에 최적화되어 있어 Base64/JSON 파싱 기능을 아주 강력하게 지원합니다.
❄️ Snowflake (스노우플레이크)
Snowflake는 VARIANT라는 반정형 데이터 타입을 핵심으로 내세우기 때문에, JSON 처리가 타 DB 대비 압도적으로 편합니다.
✅ 조회 및 디코딩
BASE64_DECODE_STRING 함수 한 번이면 끝납니다. 이후 PARSE_JSON을 통해 VARIANT 객체로 만들고, :(콜론) 표기법으로 바로 접근합니다.
SELECT
ID,
-- 1. 디코딩 및 JSON 파싱
PARSE_JSON(BASE64_DECODE_STRING(LOG_BLOB)) AS JSON_OBJ,
-- 2. 값 추출 (콜론 표기법 사용)
PARSE_JSON(BASE64_DECODE_STRING(LOG_BLOB)):ui_mode::STRING AS UI_MODE
FROM TB_USER_LOG
WHERE ID = 101;🔥 핵심: LATERAL FLATTEN
Snowflake에서는 UNNEST 대신 FLATTEN이라는 함수를 사용합니다.
SELECT
t.ID,
f.VALUE::STRING AS TAG_NAME -- FLATTEN된 결과는 'VALUE' 컬럼에 들어감
FROM
TB_USER_LOG t,
LATERAL FLATTEN(
input => PARSE_JSON(BASE64_DECODE_STRING(t.LOG_BLOB)):tags
) f
WHERE t.ID = 101;🌩️ AWS Athena (Presto / Trino 기반)
Athena는 Presto 엔진을 사용하므로, AWS Redshift Spectrum이나 다른 Presto 기반 DW와 문법이 유사합니다.
✅ 조회 및 디코딩
Athena는 FROM_BASE64 결과가 VARBINARY이므로, VARCHAR로 캐스팅하는 과정이 필요합니다.
SELECT
id,
-- 1. 디코딩 (Varbinary -> Varchar)
CAST(FROM_BASE64(log_blob) AS VARCHAR) AS json_str,
-- 2. 값 추출
JSON_EXTRACT_SCALAR(
CAST(FROM_BASE64(log_blob) AS VARCHAR),
'$.ui_mode'
) AS ui_mode
FROM tb_user_log
WHERE id = 101;🔥 핵심: CROSS JOIN UNNEST
Athena에서는 CROSS JOIN UNNEST를 사용하며, JSON 배열을 ARRAY 타입으로 먼저 변환(JSON_PARSE 후 CAST)해줘야 합니다.
SELECT
t.id,
tag_name
FROM
tb_user_log t
CROSS JOIN UNNEST(
CAST(
JSON_EXTRACT(CAST(FROM_BASE64(t.log_blob) AS VARCHAR), '$.tags')
AS ARRAY(VARCHAR)
)
) AS t(tag_name) -- t(컬럼명) 형태로 별칭 지정
WHERE t.id = 101;💡 개념 정리: UNNEST vs FLATTEN, 도대체 뭐가 다른가요?
SQL을 짜다 보면 어디는 UNNEST를 쓰고, 어디는 FLATTEN을 써서 헷갈리실 겁니다. 결론부터 말하면 "목적은 같지만, 제공하는 정보의 깊이가 다르다"고 볼 수 있습니다.
1. UNNEST (PostgreSQL, BigQuery, Athena)
- 의미: "둥지(Nest)에서 꺼낸다"는 뜻입니다.
- 동작: 배열
['A', 'B']를 풀면 딱 값인A,B만 뱉어냅니다. - 특징: 심플합니다. 값만 필요할 때 씁니다. 만약 배열의 순서(Index)가 필요하면
WITH OFFSET같은 추가 문법을 써야 합니다.
2. FLATTEN (Snowflake)
- 의미: "납작하게 편다"는 뜻입니다.
- 동작: Snowflake의
FLATTEN은 단순히 값만 뱉는 게 아니라, 하나의 테이블 뷰를 만들어줍니다. - 특징:
FLATTEN을 실행하면 자동으로 다음 컬럼들을 제공합니다.VALUE: 배열 안의 실제 값INDEX: 배열의 순서 (0, 1, 2...)KEY: (Object인 경우) 키 이름PATH: 데이터의 경로SEQ: 입력 레코드의 시퀀스
- 요약:
UNNEST보다 훨씬 수다스럽고 친절합니다. 별도의 함수 없이도 인덱스나 경로를 바로 알 수 있어 복잡한 JSON 분석에 더 유리합니다.
한 줄 요약:
- Postgre/BigQuery/Athena:
UNNEST로 푼다. (배열 → 행)- Snowflake:
FLATTEN으로 푼다. (배열 → 행 + 메타데이터 풍부함)
마치며
Base64로 감춰진 데이터를 SQL로 다루는 것은 '비상용 칼'과 같습니다. 매우 유용하지만 조심해서 써야 합니다.
- 성능 이슈: 위 쿼리들은 인덱스를 전혀 타지 못합니다. 디코딩과 파싱 비용이 비싸기 때문에, 수십만 건 전체 조회(
SELECT *)는 DB CPU를 폭발시킬 수 있습니다. - 백업 필수:
UPDATE쿼리를 날리기 전에, 반드시SELECT문으로 변경될 JSON 모양을 미리 확인(Preview)하고 실행하는 습관을 들여야 합니다.
'DB > SQL' 카테고리의 다른 글
| [GA활용] 전환율 구하기 기본 (0) | 2025.02.17 |
|---|---|
| [Concat] 문자열 다중 조합 (0) | 2025.02.17 |
| [Aggregation] 일간/주간 집계 (0) | 2025.02.14 |
| [Cohort-Pivot] 2개월 차에도 구매한 고객 찾기 (0) | 2025.02.11 |
| [SubQuery] 다양한 서브쿼리 (0) | 2025.02.05 |