JSON 지원 — json_extract, json_each, JSONB · SQLite Quest

필요하면 SQLite도 document store가 돼

SQLite는 3.38부터 JSON1 extension을 기본으로 품고 나와. TEXT 컬럼에 JSON을 넣어두고 SQL 함수로 뒤질 수 있어.

json_extract(col, '$.path') — JSON path로 값을 꺼내.
json_each(col)와 json_tree(col) — JSON 요소를 하나씩 훑게 해주는 테이블 함수야.
json_set(col, '$.path', value)와 json_remove, json_replace — 값을 고쳐.
json_array, json_object, json_group_array — JSON을 만들어.

2024년 1월에 나온 3.45부터는 JSONB도 돼. 접근할 때마다 다시 파싱하지 않아도 되는 binary 표현이야. 컬럼이 크거나 자주 뒤진다면 JSONB로 저장해.

Tip: 꺼낸 JSON path 위에 표현식 인덱스를 걸면 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

Exercise

형태가 제각각인 JSON 메타데이터를 담는 events 테이블을 만들고 row를 10만 개 넣어. JSON 안에서 값을 끌어내는 query를 세 개 쓰고, 가장 자주 뒤지는 path 위에 표현식 인덱스를 걸어서 EXPLAIN plan이 바뀌는지 확인해. 그다음 같은 데이터를 JSONB 컬럼(3.45 이상)에도 넣고 query 속도를 견줘봐.

JSON 지원 — json_extract, json_each, JSONB

필요하면 SQLite도 document store가 돼

Code

External links

Exercise

Progress

댓글 0