JSON 지원 — json_extract, json_each, JSONB

~14 min · json, json1, jsonb

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

필요할 때 SQLite 가 document store

SQLite 가 3.38 이후 기본값으로 JSON1 extension ship. TEXT 컬럼에 JSON 저장 + SQL 함수로 query:

json_extract(col, '$.path') — JSON path 로 값 추출.
json_each(col)/json_tree(col) — JSON 요소 iterate 하는 table-valued 함수.
json_set(col, '$.path', value)/json_remove/json_replace — 변경.
json_array/json_object/json_group_array — JSON 빌드.

3.45 (2024 년 1 월) 이후 SQLite 가 JSONB — access 마다 parse 더 빠른 binary 표현 — 도 지원. 컬럼이 크거나 자주 query 되면 JSONB 저장.

Tip: 추출된 JSON path 위 expression 인덱싱 = query 빠름: CREATE INDEX idx_meta_status ON events(json_extract(meta, '$.status')). 그러면 WHERE json_extract(meta, '$.status') = 'pending' 가 인덱스 사용.

Code

JSON1 패턴·sql

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  ts TEXT NOT NULL DEFAULT (datetime('now')),
  meta TEXT NOT NULL                       -- JSON
) STRICT;

INSERT INTO events(meta) VALUES
  (json_object('status', 'pending', 'tags', json_array('a','b'))),
  (json_object('status', 'done',    'tags', json_array('a')));

-- 값 추출
SELECT id, json_extract(meta, '$.status') AS status FROM events;

-- JSON 값으로 필터
SELECT * FROM events WHERE json_extract(meta, '$.status') = 'pending';

-- Array 요소 iterate
SELECT e.id, value AS tag
FROM   events e, json_each(e.meta, '$.tags');

빠른 필터링용 JSON path 인덱스·sql

CREATE INDEX idx_events_status
  ON events(json_extract(meta, '$.status'));

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE json_extract(meta, '$.status') = 'pending';
-- USING INDEX idx_events_status

External links

Exercise

임의 JSON 메타 저장하는 events 테이블. Shape 다양한 100k row insert. JSON 안에서 값 pull 하는 query 3 개, 가장 자주 query 하는 path 위 expression 인덱스 추가, EXPLAIN plan 변경 확인. JSONB 컬럼 (3.45+) 에 같은 데이터 저장 + query 속도 비교.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousFastAPI + aiosqlite 아키텍처 Next →Window Function + CTE

이 페이지에서 버그를 발견하셨거나 피드백이 있으세요?문제 신고

🔔 답글 알림 (로그인 필요)

로그인 — 댓글을 남기려면 로그인해 주세요.

아직 댓글이 없어요. 첫 댓글을 남겨보세요.