SQLite JSON: json_extract, json_set ve json_each

SQLite'ta JSON Tipi Yok — ve Bu Hiç Sorun Değil

SQLite'ta JSON için ayrı bir kolon tipi bulunmuyor. JSON verisini sıradan bir TEXT kolonuna yazıyorsunuz; geri kalanı ise toplu olarak JSON1 eklentisi diye anılan yerleşik fonksiyonların işi — bu fonksiyonlar veriyi okuyor, sorguluyor ve güncelliyor. JSON1, modern SQLite sürümlerinin hepsiyle birlikte geldiği için ekstra bir kurulum yapmanıza gerek yok.

Mantığı şöyle düşünün: dokümanı metin olarak saklayın, içine bakmak için fonksiyonları kullanın.

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;

İki satır var, her biri düz bir metin kolonunda bir JSON belgesi tutuyor. Şimdi bu belgelerin içine uzanmanın yollarına ihtiyacımız var.

json_extract ve ->> ile alan çıkarma

json_extract(column, path) bir JSON belgesinden değer çekip getirir. Path $ ile başlar (kök) ve nesne anahtarları için .alan, dizi indeksleri için [i] kullanır.

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;

Her seferinde json_extract(data, '$.name') yazmaktan sıkıldığınızı düşünerek SQLite size iki operatör sunuyor:

• -> JSON kodlanmış değer döndürür (stringler tırnak işaretleriyle birlikte gelir).
• ->> SQL değeri döndürür (metin ya da sayı, tırnaksız).

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 sonucu "Ada" şeklinde döner (yani hâlâ JSON), name_text ise düz Ada olarak gelir. Bir değeri karşılaştırmak ya da ekrana basmak istiyorsanız ->> kullanın. Sonucu başka bir JSON fonksiyonuna girdi olarak vereceksiniz -> tercih edin.

JSON Alanlarına Göre Filtreleme

Değer çıkarabildiğinize göre artık filtreleme de yapabilirsiniz. İfadeyi, diğer koşullarda olduğu gibi WHERE cümlesine yazmanız yeterli:

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;

Bu çalışıyor, ama tablo biraz büyüdüğünde işler yavaşlıyor — çünkü koşulu değerlendirmek için her satırın tek tek parse edilmesi gerekiyor. Birazdan bunu bir indeksle çözeceğiz.

JSON üretmek: json_object ve json_array

Tersine, sorgu içinde JSON da oluşturabilirsiniz:

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, ...) ile bir nesne, json_array(v1, v2, ...) ile de bir dizi oluşturursunuz. API yanıtlarını doğrudan SQL içinde derlemek için bire birdir; üstelik iç içe kullanmaktan da gocunmazlar:

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

JSON Güncelleme: json_set, json_insert, json_replace

Bir JSON belgesini değiştirip yeni halini döndüren, birbirine çok yakın üç fonksiyon var:

• json_set(doc, path, value) — yolu ayarlar; yol yoksa oluşturur, varsa üzerine yazar.
• json_insert(doc, path, value) — yalnızca yol mevcut değilse ekler.
• json_replace(doc, path, value) — yalnızca yol zaten varsa günceller.

Bu fonksiyonlar belgeyi yerinde değiştirmez; size yeni bir belge döndürür. Genelde sonucu UPDATE ile geri yazarsınız:

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 fonksiyonunun tek çağrıda birden fazla path/value çifti alabildiğini unutmayın. Bir anahtarı silmek için json_remove(doc, path) kullanın.

json_each ile Dizileri Satırlara Açmak

json_each, tablo döndüren bir fonksiyondur: girdi olarak bir JSON dizisi (veya nesnesi) alır ve her bir eleman için bir satır üretir. Bu sayede düz SQL'de epey zahmetli olan "admin etiketine sahip kullanıcıları bul" gibi sorgular, sıradan bir join'e dönüşür:

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 tablosundaki her satır, kendi tags dizisinin elemanlarıyla join'lenir. json_each; key, value, type ve fullkey gibi işine yarayacak sütunları sana açar. Kardeşi json_tree ise belgeyi baştan sona, iç içe geçmiş tüm düğümleri dahil ederek dolaşır — yapısını önceden bilmediğiniz belgelerde arama yaparken çok kullanışlıdır.

SQLite JSON Kolonlarında İndeksleme

Yukarıdaki WHERE data ->> '$.active' = 1 sorgusu çalışır çalışmasına, ama SQLite koşulu değerlendirmek için her satırı tek tek ayrıştırmak zorunda kalır. Sıkça sorguladığınız alanlar için bir ifade indeksi (expression index) oluştur:

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

İndeks, sorgunuzdaki ifadenin birebir aynısını kullanmak zorunda. İndekste json_extract(data, '$.email'), sorguda ise data ->> '$.email' yazarsanız ikisi eşleşmez ve indeks boşu boşuna durur — birini seçip ona sadık kalın.

Sürekli sorguladığınız alanlar için generated column çok daha okunaklı bir çözüm:

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 alanı sorgu yazan biri için sıradan bir kolon gibi görünür ama arka planda JSON ile otomatik olarak senkron kalır.

JSON doğrulama

json_valid(text) fonksiyonu, metin geçerli bir JSON ise 1, değilse 0 döner. Bunu bir CHECK kısıtıyla birleştirirseniz, hatalı veriyi daha yazma anında reddedebilirsiniz:

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

İlk INSERT çalışır; ikincisi ise CONSTRAINT hatası verir. Bu kontrol olmadan, bozuk JSON tabloda sessiz sedasız oturur ve aylar sonra bir json_extract çağrısı patlayana kadar kimsenin haberi olmaz.

JSON ve JSONB farkı

SQLite 3.45 ile birlikte JSONB adında ikili (binary) bir format geldi — aynı veri, ama önceden ayrıştırılmış kompakt bir biçimde tutuluyor. Böylece fonksiyonlar her çağrıda baştan parse etmek zorunda kalmıyor. jsonb_* ailesindeki fonksiyonlar (jsonb_extract, jsonb_set, jsonb_object, ...) metin yerine JSONB döndürür ve JSONB kolonları da aynı operatörlerle sorgulanabilir.

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;

Dökümlerde okunaklı, gözle inceleyebileceğiniz belgeler istiyorsanız düz JSON (text) kullanın. Tablo büyüdüğünde, sorgu trafiği arttığında ve profilde parse maliyeti gerçekten göze batmaya başladığında JSONB'ye geçin. Varsayılan olarak JSONB seçmeyin — düz JSON'un hata ayıklama sırasındaki okunabilirliği çok değerli.

JSON Ne Zaman Doğru Tercihtir

JSON kolonları şu durumlarda parlar:

• Satırdan satıra şekil değişiyorsa (event payload'ları, denetim logları, entegrasyon webhook'ları gibi).
• Harici bir API cevabını olduğu gibi saklamak istiyorsanız.
• Alan nadiren sorgulanıyor ve neredeyse hiç filtreye girmiyorsa.

Şu durumlarda ise yanlış seçimdir:

• Şema tasarımından kaçmak için JSON'a sığınıyorsanız. Her satırda aynı alanlar varsa, bunlar kolon olmalı.
• Bir değer üzerinden sık sık filtre veya join yapmanız gerekiyorsa. İndeksli gerçek bir kolon, JSON path araması karşısında her zaman önde gider.
• Foreign key ihtiyacınız varsa. JSON'da ilişkisel bütünlük yoktur.

İşin tatlı noktası ikisini birlikte kullanmak: sorguları ve kısıtları yönlendiren alanlar için skaler kolonlar, değişken verinin uzun kuyruğu için yanlarına bir JSON kolonu.

Sırada: Full-Text Search

JSON size depolama tarafında esneklik sağlıyor. Sıradaki sayfa FTS5'i — SQLite'ın tam metin arama motorunu — anlatıyor; LIKE ile yapabileceklerinizin çok ötesinde, sıralama ve vurgulama da sunan gerçek bir metin araması elde ediyorsunuz.