Dokumentacja SQLite z przykładami: Obsługa JSON

Q: Jak odpytać kolumnę JSON w SQLite?

Użyj json_extract(column, '$.path') albo skróconego operatora ->>. Na przykład SELECT data ->> '$.name' FROM users wyciąga pole name z dokumentu JSON zapisanego w data. Ścieżki używają $ dla korzenia, .field dla kluczy obiektu i [i] dla indeksów tablicy.

Q: Czy można zaindeksować pole JSON w SQLite?

Tak: utwórz indeks na wyrażeniu z wyciąganą ścieżką: CREATE INDEX idxuseremail ON users(json_extract(data, '$.email')). Zapytania, które używają tego samego wyrażenia w klauzuli WHERE, skorzystają z indeksu. Dla często odpytywanych pól kolumna generowana z indeksem bywa czytelniejsza.

Q: Jaka jest różnica między -> a ->> w SQLite?

-> zwraca wartość JSON (nadal zakodowaną jako JSON, więc napisy wracają w cudzysłowach), a ->> zwraca wartość SQL (tekst lub liczbę, bez cudzysłowów). Używaj ->>, gdy chcesz surowej wartości do wyświetlenia lub porównania, a ->, gdy łączysz kolejne operacje JSON.

SQLite nie ma typu JSON i to nie problem

SQLite nie ma osobnego typu kolumny dla JSON. JSON trafia do zwykłej kolumny TEXT, a zestaw wbudowanych funkcji, nazywany razem rozszerzeniem JSON1, potrafi go parsować, odpytywać i modyfikować. JSON1 jest dostępne w każdej współczesnej wersji SQLite, więc nie trzeba nic instalować.

Model myślowy: przechowuj dokument jako tekst, a do zaglądania do środka używaj funkcji.

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;

Dwa wiersze, każdy z dokumentem JSON w zwykłej kolumnie tekstowej. Teraz potrzebujemy sposobów, żeby sięgnąć do wnętrza tych dokumentów.

Wyciąganie pól przez json_extract i ->>

json_extract(column, path) wyciąga wartość z dokumentu JSON. Ścieżka zaczyna się od $ (korzeń) i używa .field dla kluczy obiektu oraz [i] dla indeksów tablicy.

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;

Pisanie wszędzie json_extract(data, '$.name') szybko się nudzi, więc SQLite daje dwa operatory:

-> zwraca wartość zakodowaną jako JSON (napisy wracają z cudzysłowami).
->> zwraca wartość SQL (tekst lub liczbę, bez cudzysłowów).

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 wraca jako "Ada" (nadal JSON), a name_text jako Ada. Używaj ->>, gdy chcesz wartości do porównania lub wyświetlenia. Używaj ->, gdy wynik trafi do kolejnej funkcji JSON.

Filtrowanie po polach JSON

Skoro umiesz wyciągać, umiesz też filtrować. Wyrażenie trafia do klauzuli WHERE jak każde inne:

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;

To działa, ale w tabeli jakiejkolwiek wielkości jest wolne: każdy wiersz trzeba sparsować, żeby sprawdzić warunek. Za chwilę naprawimy to indeksem.

Budowanie JSON: json_object i json_array

W drugą stronę możesz budować JSON w samym zapytaniu:

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, ...) buduje obiekt. json_array(v1, v2, ...) buduje tablicę. Przydają się do składania odpowiedzi API bezpośrednio w SQL i bez problemu się zagnieżdżają:

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

Aktualizacja JSON: json_set, json_insert, json_replace

Trzy blisko spokrewnione funkcje modyfikują dokument JSON i zwracają jego nową wersję:

json_set(doc, path, value): ustawia ścieżkę, tworząc ją, jeśli jej brak, i nadpisując, jeśli istnieje.
json_insert(doc, path, value): wstawia tylko wtedy, gdy ścieżka jeszcze nie istnieje.
json_replace(doc, path, value): aktualizuje tylko wtedy, gdy ścieżka już istnieje.

Te funkcje nie zmieniają dokumentu w miejscu, tylko zwracają nowy, który zwykle zapisujesz z powrotem przez 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;

Zwróć uwagę, że json_set przyjmuje w jednym wywołaniu kilka par ścieżka/wartość. Żeby usunąć klucz, użyj json_remove(doc, path).

Rozwijanie tablic przez json_each

json_each to funkcja tablicowa: przyjmuje tablicę (lub obiekt) JSON i zwraca jeden wiersz na element. Dzięki temu „znajdź użytkowników z tagiem admin”, niewygodne w czystym SQL, staje się zwykłym złączeniem:

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';

Każdy wiersz z users jest łączony z elementami swojej tablicy tags. json_each udostępnia przydatne kolumny, m.in. key, value, type i fullkey. Jego rodzeństwo, json_tree, rekurencyjnie przechodzi przez cały dokument, łącznie z każdym zagnieżdżonym węzłem, co przydaje się przy przeszukiwaniu dokumentów o nieznanym kształcie.

Indeksowanie pól JSON

Zapytanie WHERE data ->> '$.active' = 1 z przykładu wyżej działa, ale SQLite musi sparsować każdy wiersz, żeby sprawdzić warunek. Dla pól, po które często sięgasz, zbuduj indeks na wyrażeniu:

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';

Indeks musi używać dokładnie tego samego wyrażenia co zapytanie. json_extract(data, '$.email') w indeksie i data ->> '$.email' w zapytaniu nie zostaną dopasowane, a indeks będzie leżał nieużywany. Wybierz jedną formę i się jej trzymaj.

Dla pól odpytywanych bez przerwy lepiej czyta się kolumna generowana:

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';

Dla piszących zapytania email wygląda jak zwykła kolumna, a przy tym automatycznie pozostaje zsynchronizowane z JSON.

Walidacja JSON

json_valid(text) zwraca 1, jeśli tekst da się sparsować jako JSON, a w przeciwnym razie 0. Połącz to z ograniczeniem CHECK, żeby odrzucać złe dane już przy zapisie:

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');

Pierwsze wstawienie się udaje, drugie kończy się błędem ograniczenia. Bez tego sprawdzenia źle sformatowany JSON spokojnie leży w tabeli, aż kilka miesięcy później jakieś wywołanie json_extract wybuchnie.

JSON a JSONB

Od SQLite 3.45 istnieje binarna reprezentacja o nazwie JSONB: te same dane, wstępnie sparsowane do zwartej postaci binarnej, żeby funkcje nie parsowały ich przy każdym wywołaniu. Rodzina funkcji jsonb_* (jsonb_extract, jsonb_set, jsonb_object, ...) zwraca JSONB zamiast tekstu, a kolumny JSONB można odpytywać tymi samymi operatorami.

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;

Używaj zwykłego JSON (tekstu), gdy chcesz, żeby dokumenty były czytelne w zrzutach i łatwe do podejrzenia. Sięgaj po JSONB, gdy tabela jest duża, często odpytywana, a koszt parsowania faktycznie widać w profilowaniu. Nie przechodź na nie domyślnie: czytelność zwykłego JSON jest bardzo cenna przy debugowaniu.

Kiedy JSON to dobry wybór

Kolumny JSON sprawdzają się, gdy:

Kształt danych różni się między wierszami (np. dane zdarzeń, logi audytowe, webhooki integracji).
Zapisujesz w cache odpowiedź zewnętrznego API i chcesz zachować ją w nienaruszonym stanie.
Pole jest rzadko odpytywane i prawie nigdy nie filtrujesz po nim.

Nie pasują, gdy:

Używasz JSON, żeby uniknąć projektowania schematu. Jeśli każdy wiersz ma te same pola, to są kolumny.
Musisz często filtrować albo łączyć po wartości. Prawdziwa kolumna z indeksem za każdym razem wyprzedzi wyszukiwanie po ścieżce JSON.
Potrzebujesz kluczy obcych. JSON nie ma integralności relacyjnej.

Najlepiej sprawdza się połączenie obu: kolumny skalarne dla pól, od których zależą zapytania i ograniczenia, a obok kolumna JSON na długi ogon zmiennych danych.

Dalej: wyszukiwanie pełnotekstowe

JSON daje elastyczność po stronie przechowywania. Następna strona omawia FTS5, silnik wyszukiwania pełnotekstowego w SQLite, który daje prawdziwe wyszukiwanie tekstu z rankingiem i wyróżnianiem, daleko wykraczające poza możliwości LIKE.

Najczęściej zadawane pytania

Jak SQLite przechowuje JSON?

SQLite nie ma osobnego typu JSON: JSON jest przechowywany jako zwykły TEXT. Wbudowane rozszerzenie JSON1 (domyślnie wkompilowane od wersji 3.38) udostępnia funkcje takie jak json_extract, json_set i json_each, które parsują ten tekst i na nim operują. Od wersji 3.45 jest też binarny format JSONB do szybszego wielokrotnego dostępu.

Jak odpytać kolumnę JSON w SQLite?

Użyj json_extract(column, '$.path') albo skróconego operatora ->>. Na przykład SELECT data ->> '$.name' FROM users wyciąga pole name z dokumentu JSON zapisanego w data. Ścieżki używają $ dla korzenia, .field dla kluczy obiektu i [i] dla indeksów tablicy.

Czy można zaindeksować pole JSON w SQLite?

Tak: utwórz indeks na wyrażeniu z wyciąganą ścieżką: CREATE INDEX idx_user_email ON users(json_extract(data, '$.email')). Zapytania, które używają tego samego wyrażenia w klauzuli WHERE, skorzystają z indeksu. Dla często odpytywanych pól kolumna generowana z indeksem bywa czytelniejsza.

Jaka jest różnica między -> a ->> w SQLite?

-> zwraca wartość JSON (nadal zakodowaną jako JSON, więc napisy wracają w cudzysłowach), a ->> zwraca wartość SQL (tekst lub liczbę, bez cudzysłowów). Używaj ->>, gdy chcesz surowej wartości do wyświetlenia lub porównania, a ->, gdy łączysz kolejne operacje JSON.

Powiązane zagadnienia