SQLite'ta JSON Desteği ve Çalışma Mantığı
SQLite özel bir JSON veri tipi içermez; JSON içeriklerini doğrudan
TEXT tipi kolonlarda saklayabilirsiniz. Veri üzerinde işlem yapmak, sorgulama gerçekleştirmek veya güncelleme sağlamak için modern SQLite sürümlerinde dahili olarak gelen "JSON1" eklentisi kullanılır. Kurulum gerektirmeyen bu yapı sayesinde metin olarak kaydettiğiniz JSON verilerini SQLite fonksiyonlarıyla kolayca yönetebilirsiniz.
SQL:
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;
Her satırın içinde bir JSON belgesi barındırdığı bir tabloda, bu verilerin detaylarına erişmek için özel yöntemler izlemeniz gerekir.
Değer Çıkarma: json_extract ve Operatörler
Bir JSON içerisinden belirli bir değeri çekmek için
json_extract(column, path) fonksiyonundan yararlanılır. Buradaki yol (path) yapısı, $ kök dizinini temel alır; nesne anahtarları .alan, dizi elemanları ise [i] söz dizimiyle belirtilir.
SQL:
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;
Daha pratik bir kullanım için SQLite şu iki operatörü sunar:
•
-> Değeri JSON formatında döndürür (string veriler tırnaklarıyla birlikte gelir).•
->> Değeri ham SQL veri tipine dönüştürür (tırnaksız metin veya sayı).
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 çıktısı JSON yapısını korurken, name_text doğrudan değerin kendisini döndürür. Karşılaştırma yaparken veya çıktı alırken ->> tercih etmeniz, sonucu başka bir JSON fonksiyonunda kullanacaksanız -> kullanmanız önerilir.JSON İçeriğine Göre Veri Filtreleme
Elde ettiğiniz değerleri tıpkı standart sütunlarda yaptığınız gibi
WHERE bloğunda filtreleme amaçlı kullanabilirsiniz:
SQL:
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 yöntem işlevsel olsa da, tablo büyüdükçe her satırın ayrıştırılması (parse edilmesi) gerekeceği için sorgu süresi uzayacaktır. Bu sorunu indeksleme ile aşabilirsiniz.
JSON Verisi Oluşturma: json_object ve json_array
SQL sorgularınız içerisinde dinamik olarak JSON üretmek de mümkündür:
SQL:
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, ...) kullanarak nesneler, json_array(v1, v2, ...) kullanarak ise diziler oluşturabilirsiniz. Bu fonksiyonlar iç içe kullanılabilir; özellikle uygulama tarafına doğrudan API uyumlu yanıtlar hazırlarken oldukça kullanışlıdır:
SQL:
SELECT json_object(
'user', json_object('name', 'Ada', 'id', 1),
'roles', json_array('admin', 'beta')
) AS payload;
Veri Güncelleme İşlemleri
JSON verisini değiştirmek için kullanılan üç ana fonksiyon şunlardır:
•
json_set(doc, path, value) - Belirtilen yolu günceller; yoksa ekler, varsa üzerine yazar.•
json_insert(doc, path, value) - Sadece yol mevcut değilse yeni veri ekler.•
json_replace(doc, path, value) - Sadece mevcut olan veriyi günceller.Bu fonksiyonlar veriyi doğrudan orijinalinde değiştirmez, yeni halini döndürür. Genellikle
UPDATE komutuyla yeni değer tabloya geri yazılır:
SQL:
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 tek seferde birden fazla değişikliği kabul eder. Veriyi silmek istediğinizde ise json_remove(doc, path) kullanılır.Dizileri Satırlara Dönüştürme: json_each
json_each, bir tablo gibi çalışan özel bir fonksiyondur. Bir JSON dizisini veya nesnesini girdi olarak alır ve her bir eleman için ayrı satırlar oluşturur. Bu sayede karmaşık sorguları standart JOIN işlemlerine dönüştürebilirsiniz:
SQL:
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 tablosu ile dizideki elemanlar eşleştirilerek; key, value, type gibi sütunlara erişim sağlanır. json_tree ise belgenin tamamını hiyerarşik olarak dolaşır, bu da yapısını bilmediğiniz JSON verilerini tararken oldukça pratiktir.İndeksleme Stratejileri
WHERE data ->> '$.active' = 1 gibi sorgularda performans artışı için ifade indeksi (expression index) kullanılması tavsiye edilir:
SQL:
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';
Dikkat edilmesi gereken nokta şudur: İndeks tanımlarken kullandığınız ifade ile sorguda yazdığınız ifade birebir aynı olmalıdır.
json_extract tanımlayıp ->> ile sorgulama yaparsanız indeks devreye girmez.Alternatif olarak, daha okunaklı bir çözüm olan "generated column" yapısını tercih edebilirsiniz:
SQL:
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';
Bu sayede
email sütunu, sanki normal bir kolonmuş gibi indekslenebilir ve otomatik güncellenir.Veri Doğrulama ve Kısıtlamalar
json_valid(text) fonksiyonu, içeriğin geçerli bir JSON olup olmadığını kontrol eder. Bunu CHECK kısıtı olarak tanımlayarak veritabanına hatalı JSON girmesini engelleyebilirsiniz:
SQL:
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');
Bu kısıtlama, yanlış formatlı verilerin kaydedilmesini anında reddeder; böylece ileride oluşabilecek okuma hatalarının önüne geçilir.
JSON ve JSONB Farkı
SQLite 3.45 sürümüyle gelen JSONB formatı, veriyi önceden ayrıştırılmış ikili bir yapıda tutar. Bu, fonksiyonların her sorguda metni baştan parse etmesini önleyerek performansı artırır.
jsonb_* fonksiyonları veriyi JSONB formatında döndürür.
SQL:
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;
Geliştirme aşamasında okunabilirlik için düz JSON, yüksek trafikli üretim ortamlarında performans için ise JSONB kullanılması önerilir.
JSON Hangi Durumlarda Kullanılmalı?
JSON kolonları şu durumlarda idealdir:
• Veri şeması değişkenlik gösteriyorsa (loglar, webhook verileri).
• API üzerinden gelen verileri olduğu gibi saklamanız gerekiyorsa.
• Veri çok nadiren filtreleniyorsa veya aranıyorsa.
Aşağıdaki durumlarda JSON yerine standart kolonlar kullanılmalıdır:
• Aynı alanlar her satırda mevcutsa (bu durumda şemanız zaten sabittir).
• Sık sık filtreleme ve join işlemi yapıyorsanız.
• Veriler arası ilişkisel bütünlük (Foreign Key) sağlamanız gerekiyorsa.
En iyi yaklaşım, sorgulanan alanları skaler kolonlarda, esnek ve az sorgulanan verileri ise yanındaki bir JSON kolonunda tutmaktır.
Sırada: Full-Text Search
JSON esnekliği sağlarken, bir sonraki aşama olan FTS5 motoru,
LIKE operatörünün çok daha ötesinde, sıralama ve vurgulama özellikli gelişmiş bir metin arama imkanı sunmaktadır.