실행 가능한 SQLite 문서: JSON 함수

Q: SQLite는 JSON을 어떤 형태로 저장하나요?

SQLite에는 별도의 JSON 타입이 없습니다. JSON은 그냥 TEXT로 저장돼요. 대신 JSON1 확장(3.38부터 기본 내장)이 jsonextract, jsonset, json_each 같은 함수를 제공해서 그 텍스트를 파싱하고 조작할 수 있게 해줍니다. 3.45부터는 반복 접근이 빠른 바이너리 포맷인 JSONB도 추가됐습니다.

Q: JSON 컬럼은 어떻게 조회하나요?

json_extract(컬럼, '$.경로')를 쓰거나, 줄여서 ->> 연산자를 쓰면 됩니다. 예를 들어 SELECT data ->> '$.name' FROM users라고 하면 data 컬럼에 들어 있는 JSON 문서에서 name 필드만 꺼낼 수 있어요. 경로는 루트가 $, 객체 키는 .field, 배열 인덱스는 [i] 형식으로 씁니다.

Q: JSON 필드에도 인덱스를 걸 수 있나요?

네, 추출 경로를 그대로 식(expression) 인덱스로 만들면 됩니다. CREATE INDEX idxuseremail ON users(json_extract(data, '$.email'))처럼요. WHERE 절에서 똑같은 식을 쓰면 이 인덱스를 타게 됩니다. 자주 조회하는 필드라면 생성 컬럼(generated column)을 만들고 거기에 인덱스를 거는 방식이 더 깔끔할 때가 많습니다.

Q: `->`와 `->>`는 뭐가 다른가요?

->는 JSON 값을 그대로 반환합니다 — 즉 문자열은 따옴표가 붙은 JSON 인코딩 상태로 나와요. 반면 ->>는 SQL 값(텍스트나 숫자)을 따옴표 없이 풀어서 돌려줍니다. 화면에 출력하거나 비교 연산을 할 때는 ->>, JSON 연산을 계속 이어갈 때는 ->를 쓰면 됩니다.

SQLite에는 JSON 타입이 없다 — 그래도 괜찮다

SQLite에는 별도의 JSON 컬럼 타입이 없다. JSON은 그냥 일반 TEXT 컬럼에 저장하고, JSON1 확장이라고 부르는 내장 함수 모음이 그 안을 파싱하고, 조회하고, 수정하는 일을 맡는다. JSON1은 요즘 빌드되는 SQLite라면 기본으로 들어 있으니 따로 설치할 필요도 없다.

핵심만 정리하면 이렇다. 문서는 텍스트로 저장하고, 안을 들여다볼 때는 함수를 쓴다.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT
);

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

텍스트 컬럼에 JSON 문서가 담긴 두 개의 행이 있습니다. 이제 이 문서 안으로 파고들어 값을 꺼내는 방법이 필요합니다.

json_extract와 ->> 연산자로 필드 값 꺼내기

json_extract(column, path)는 JSON 문서에서 원하는 값을 뽑아주는 함수입니다. 경로(path)는 루트를 의미하는 $로 시작하고, 객체의 키는 .field, 배열의 인덱스는 [i] 형태로 표기합니다.

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT
    json_extract(data, '$.name')     AS name,
    json_extract(data, '$.tags[0]')  AS first_tag
FROM users;

매번 json_extract(data, '$.name')을 쓰는 건 금방 지치는 일이라, SQLite는 두 가지 연산자를 따로 마련해 두었습니다.

->는 JSON 인코딩된 값을 반환합니다(문자열은 따옴표가 그대로 붙어 옵니다).
->>는 SQL 값을 반환합니다(텍스트나 숫자, 따옴표 없음).

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json은 "Ada"처럼 JSON 형태 그대로 반환되고, name_text는 그냥 Ada로 나옵니다. 비교하거나 화면에 보여줄 값이 필요하다면 ->>를, 결과를 다시 다른 JSON 함수에 넘길 거라면 ->를 쓰면 됩니다.

JSON 필드로 필터링하기

값을 꺼낼 수 있으면 필터링도 가능합니다. 다른 컬럼과 마찬가지로 WHERE 절에 표현식을 그대로 넣으면 됩니다:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

이 방식도 동작은 하지만, 테이블 크기가 조금만 커져도 금방 느려집니다. 술어(predicate)를 평가하려고 모든 행을 일일이 파싱해야 하기 때문이죠. 이 문제는 잠시 뒤에 인덱스로 해결할 겁니다.

JSON 만들기: json_object와 json_array

반대로, 쿼리 안에서 직접 JSON을 만들어낼 수도 있습니다:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

json_object('k1', v1, 'k2', v2, ...)로 객체를 만들고, json_array(v1, v2, ...)로 배열을 만듭니다. SQL에서 바로 API 응답 형태를 조립할 때 유용하고, 중첩도 자연스럽게 됩니다:

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

JSON 업데이트: json_set, json_insert, json_replace

JSON 문서를 수정하고 새 버전을 반환하는, 서로 비슷하지만 동작이 미묘하게 다른 세 함수가 있습니다.

json_set(doc, path, value) — 해당 경로에 값을 설정합니다. 경로가 없으면 새로 만들고, 이미 있으면 덮어씁니다.
json_insert(doc, path, value) — 경로가 존재하지 않을 때만 값을 넣습니다.
json_replace(doc, path, value) — 경로가 이미 존재할 때만 값을 갱신합니다.

이 함수들은 원본을 직접 바꾸지 않고 새 문서를 반환하기 때문에, 보통 아래처럼 UPDATE로 다시 써넣는 방식으로 사용합니다.

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

json_set은 한 번 호출할 때 여러 개의 경로/값 쌍을 함께 넘길 수 있습니다. 키를 삭제할 때는 json_remove(doc, path)를 사용하세요.

json_each로 배열 펼치기

json_each는 테이블 값 함수입니다. JSON 배열(또는 객체)을 받아서 원소 하나당 한 행씩 돌려주죠. 덕분에 일반 SQL에서는 까다로웠던 "admin 태그가 달린 사용자 찾기" 같은 작업이 평범한 조인으로 풀립니다:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

users 테이블의 각 행은 자신의 tags 배열 요소들과 조인됩니다. json_each는 key, value, type, fullkey 같은 유용한 컬럼들을 노출하죠. 이 함수의 형제 격인 json_tree는 문서 전체를 재귀적으로 훑으면서 중첩된 모든 노드를 포함시켜 줍니다 — 구조를 알 수 없는 문서를 검색할 때 특히 유용합니다.

SQLite JSON 인덱스 만들기

위에서 본 WHERE data ->> '$.active' = 1 쿼리는 동작은 하지만, SQLite가 조건을 평가하려면 모든 행을 일일이 파싱해야 합니다. 자주 조회하는 필드라면 표현식 인덱스를 만들어 두는 게 좋습니다:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

인덱스는 쿼리에서 쓰는 표현식과 완전히 똑같아야 합니다. 인덱스는 json_extract(data, '$.email')로 만들어 놓고 쿼리에서는 data ->> '$.email'로 조회하면 매칭되지 않아서 인덱스가 그대로 놀게 됩니다. 둘 중 하나를 골랐으면 끝까지 그 방식만 쓰세요.

자주 조회하는 필드라면 생성 컬럼(generated column)으로 빼두는 편이 훨씬 깔끔합니다:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

email은 쿼리를 작성하는 입장에서는 평범한 컬럼처럼 보이지만, JSON 데이터와 자동으로 동기화됩니다.

JSON 유효성 검사

json_valid(text)는 해당 텍스트가 JSON으로 파싱되면 1을, 그렇지 않으면 0을 돌려줍니다. 이 함수를 CHECK 제약 조건과 함께 쓰면, 잘못된 데이터가 들어오는 시점에 아예 차단할 수 있습니다.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT CHECK (json_valid(payload))
);

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

첫 번째 INSERT는 성공하지만, 두 번째는 제약 조건 오류로 실패합니다. 이 체크가 없으면 잘못된 형식의 JSON이 테이블 안에 조용히 들어앉아 있다가 몇 달 뒤 어느 json_extract 호출에서 갑자기 터져버리는 일이 생깁니다.

JSON과 JSONB의 차이

SQLite 3.45부터는 JSONB라는 바이너리 표현 방식이 추가됐습니다. 같은 데이터를 미리 파싱해서 컴팩트한 바이너리 형태로 저장해 두기 때문에, 함수가 호출될 때마다 매번 다시 파싱할 필요가 없습니다. jsonb_extract, jsonb_set, jsonb_object 같은 jsonb_* 계열 함수들은 텍스트 대신 JSONB를 반환하며, JSONB 컬럼도 동일한 연산자로 조회할 수 있습니다.

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

일반 JSON(텍스트) 형식은 덤프할 때 사람이 읽기 좋고 내용을 확인하기 편할 때 사용하세요. 반면 테이블이 크고 조회가 빈번해서 파싱 오버헤드가 프로파일링에 실제로 잡히는 상황이라면 JSONB를 선택하면 됩니다. 기본값으로 무작정 바꾸지는 마세요. 디버깅할 때 일반 JSON의 가독성은 그 자체로 큰 가치가 있습니다.

SQLite JSON 컬럼이 어울리는 경우

JSON 컬럼이 빛을 발하는 상황은 다음과 같습니다.

행마다 데이터 구조가 제각각일 때 (이벤트 페이로드, 감사 로그, 외부 연동 웹훅 같은 경우).
외부 API 응답을 그대로 캐싱해서 원본을 보존하고 싶을 때.
거의 조회하지 않고 필터 조건으로도 쓸 일이 없는 필드일 때.

반대로 어울리지 않는 경우는 이렇습니다.

스키마 설계를 회피하려고 JSON을 쓰는 경우. 모든 행에 같은 필드가 들어간다면 그건 그냥 컬럼으로 만들어야 합니다.
특정 값으로 자주 필터링하거나 조인해야 할 때. 인덱스가 걸린 일반 컬럼이 JSON 경로 조회보다 항상 빠릅니다.
외래 키가 필요한 자리. JSON에는 관계 무결성이 없습니다.

가장 이상적인 방식은 두 가지를 섞어 쓰는 것입니다. 쿼리와 제약 조건을 좌우하는 핵심 필드는 스칼라 컬럼으로 빼두고, 가변적인 부수 데이터는 옆에 JSON 컬럼으로 함께 두는 구성이죠.

다음: 전문 검색(Full-Text Search)

JSON은 저장 측면의 유연성을 줍니다. 다음 페이지에서는 SQLite의 전문 검색 엔진인 FTS5를 다룹니다. LIKE로는 도저히 흉내 낼 수 없는, 랭킹과 하이라이팅까지 갖춘 제대로 된 텍스트 검색을 구현할 수 있습니다.

자주 묻는 질문

SQLite는 JSON을 어떤 형태로 저장하나요?

SQLite에는 별도의 JSON 타입이 없습니다. JSON은 그냥 TEXT로 저장돼요. 대신 JSON1 확장(3.38부터 기본 내장)이 json_extract, json_set, json_each 같은 함수를 제공해서 그 텍스트를 파싱하고 조작할 수 있게 해줍니다. 3.45부터는 반복 접근이 빠른 바이너리 포맷인 JSONB도 추가됐습니다.

JSON 컬럼은 어떻게 조회하나요?

json_extract(컬럼, '$.경로')를 쓰거나, 줄여서 ->> 연산자를 쓰면 됩니다. 예를 들어 SELECT data ->> '$.name' FROM users라고 하면 data 컬럼에 들어 있는 JSON 문서에서 name 필드만 꺼낼 수 있어요. 경로는 루트가 $, 객체 키는 .field, 배열 인덱스는 [i] 형식으로 씁니다.

JSON 필드에도 인덱스를 걸 수 있나요?

네, 추출 경로를 그대로 식(expression) 인덱스로 만들면 됩니다. CREATE INDEX idx_user_email ON users(json_extract(data, '$.email'))처럼요. WHERE 절에서 똑같은 식을 쓰면 이 인덱스를 타게 됩니다. 자주 조회하는 필드라면 생성 컬럼(generated column)을 만들고 거기에 인덱스를 거는 방식이 더 깔끔할 때가 많습니다.

->와 ->>는 뭐가 다른가요?

->는 JSON 값을 그대로 반환합니다 — 즉 문자열은 따옴표가 붙은 JSON 인코딩 상태로 나와요. 반면 ->>는 SQL 값(텍스트나 숫자)을 따옴표 없이 풀어서 돌려줍니다. 화면에 출력하거나 비교 연산을 할 때는 ->>, JSON 연산을 계속 이어갈 때는 ->를 쓰면 됩니다.

SQLite JSON 다루기: json_extract, json_set, json_each