SQLite JSON Fonksiyonları: json_extract, json_set, json_each ve JSONB

JSON metni ve doğrulama: json(), json_valid(), JSON5

JSON bekleyen fonksiyonlara geçerli olmayan bir metin verirseniz çoğu hata fırlatır; istisnalar json_valid(), json_quote() ve json_error_position() fonksiyonlarıdır. json(X) metni doğrular ve gereksiz boşlukları atılmış küçültülmüş bir kopyasını döndürür. 3.42.0 sürümünden beri fonksiyonlar JSON5 uzantılarını da okur: tırnaksız anahtarlar, sondaki virgüller, tek tırnaklı metinler, onaltılık sayılar ve yorumlar gibi. Ürettikleri JSON ise her zaman standart (RFC 8259) biçimdedir; JSON5 girdisini standart JSON'a çevirmenin yolu json(X) çağırmaktır.

SELECT json(' { "ad" : "Ayşe", "etiket": [ "vip" ] } ');
-- çıktı: {"ad":"Ayşe","etiket":["vip"]}

-- JSON5: tırnaksız anahtar, sondaki virgül, artı işaretli sayı
SELECT json('{ad: "Can", puan: +5, liste: [1, 2,], }');
-- çıktı: {"ad":"Can","puan":5,"liste":[1,2]}

SELECT json_valid('{"x":1}'), json_valid('{x:1}'), json_valid('{x:1}', 6), json_valid('{"x":'),
       json_error_position('{"x":1,,}');
-- çıktı: 1|0|1|0|8

SELECT json('{"x":');
-- hata: malformed JSON

json_valid(X) varsayılan olarak yalnızca katı RFC 8259 metnine 1 der; bu yüzden tırnaksız anahtarlı metin için 0 döndü. İkinci argüman bir bit maskesidir: 1 katı JSON, 2 JSON5, 4 JSONB gibi görünen BLOB, 8 katı denetlenmiş JSONB anlamına gelir. Belge, bir değerin diğer JSON fonksiyonlarına güvenle verilip verilemeyeceğini sormak için çoğu zaman 6 değerinin (JSON5 metni veya JSONB) uygun olduğunu söyler. json_error_position() ise ilk sözdizimi hatasının 1 tabanlı karakter konumunu, geçerli girdi için 0 verir.

Örnek tablo ve CHECK ile doğrulama

Bir JSON sütununun her zaman geçerli kalmasını istiyorsanız CHECK (json_valid(veri)) kısıtı ekleyin. Aşağıdaki tablo, yazının geri kalanındaki örneklerde kullanılan iki siparişi içeriyor.

CREATE TABLE siparis(
  id   INTEGER PRIMARY KEY,
  veri TEXT CHECK (json_valid(veri))
);
INSERT INTO siparis(veri) VALUES
 ('{"musteri":{"ad":"Ayşe","sehir":"İzmir"},
    "urunler":[{"kod":"MS-100","adet":2,"fiyat":350},{"kod":"KB-200","adet":1,"fiyat":900}],
    "kargo":true,"not":null}'),
 ('{"musteri":{"ad":"Can","sehir":"Ankara"},
    "urunler":[{"kod":"CB-001","adet":3,"fiyat":120}],
    "kargo":false}');

INSERT INTO siparis(veri) VALUES ('{bozuk');
-- hata: CHECK constraint failed: json_valid(veri)

Çıkarma işleçleri ve json_extract() arasındaki fark

Üçü de JSON içindeki aynı alt bileşeni bulur; fark, sonucu hangi biçimde döndürdükleridir. Belgeye göre -> her zaman alt bileşenin JSON gösterimini, ->> ise her zaman SQL değerini (TEXT, INTEGER, REAL veya NULL) döndürür. Tek yollu json_extract() ikisinin karışımıdır: alt bileşen dizi veya nesneyse JSON metni, metin, sayı veya null ise SQL değeri verir. Birden fazla yol verilirse sonuçları bir JSON dizisi olarak toplar.

SELECT veri -> '$.musteri.ad', veri ->> '$.musteri.ad', json_extract(veri, '$.musteri.ad')
FROM siparis WHERE id = 1;
-- çıktı: "Ayşe"|Ayşe|Ayşe

SELECT veri -> 'kargo', veri ->> 'kargo', typeof(veri ->> 'kargo') FROM siparis WHERE id = 1;
-- çıktı: true|1|integer

SELECT veri -> '$.not', quote(veri ->> '$.not'), quote(veri -> '$.yok') FROM siparis WHERE id = 1;
-- çıktı: null|NULL|NULL

SELECT veri -> 'urunler' -> 0 ->> 'kod', veri ->> '$.urunler[#-1].kod' FROM siparis WHERE id = 1;
-- çıktı: MS-100|KB-200

SELECT json_extract(veri, '$.musteri'), json_extract(veri, '$.musteri.ad', '$.kargo') FROM siparis WHERE id = 1;
-- çıktı: {"ad":"Ayşe","sehir":"İzmir"}|["Ayşe",true]

SELECT json_extract('{"a":1}', 'a');
-- hata: bad JSON path: 'a'

Sonuçlarda dikkat edilecek noktalar şunlar. -> metni tırnaklı JSON metni olarak döndürdü, ->> ise tırnaksız SQL metni. JSON true değeri SQL tarafında 1 tamsayısına dönüşür, çünkü SQLite'ta ayrı bir mantıksal tip yoktur. JSON null ile hiç olmayan anahtar ->> ile ikisi de SQL NULL verir; aradaki farkı görmek için -> (null metni ile NULL) veya json_type() kullanın.

İşleçlerin sağ tarafı tam bir yol ('$.musteri.ad') olabileceği gibi PostgreSQL uyumluluğu için yalnızca bir anahtar adı ('kargo') veya dizi indeksi (0) de olabilir. Yolda # dizinin eleman sayısını temsil eder: [#-1] son elemandır. Sağ tarafta negatif tamsayı ile sondan indekslemek ise 3.47.0 ve sonrasında desteklenir. Fonksiyonlara verilen yol, tam olarak bir $ ile başlamalıdır; aksi hâlde bad JSON path hatası alırsınız.

Filtreleme ve tip denetimi

->> SQL değeri döndürdüğü için WHERE koşullarında doğrudan karşılaştırılabilir. json_type(X, P) yolun gösterdiği değerin tipini null, true, false, integer, real, text, array veya object olarak, yol yoksa NULL olarak verir. json_array_length(X, P) dizinin eleman sayısını döndürür; yol dizi olmayan bir değeri gösteriyorsa 0, hiçbir şey göstermiyorsa NULL döner.

SELECT id, veri ->> '$.musteri.ad' FROM siparis WHERE veri ->> '$.musteri.sehir' = 'Ankara';
-- çıktı: 2|Can

SELECT json_type(veri, '$.urunler'), json_type(veri, '$.kargo'), json_type(veri, '$.urunler[0].fiyat'),
       json_array_length(veri, '$.urunler')
FROM siparis WHERE id = 1;
-- çıktı: array|true|integer|2

Dizileri satırlara açmak: json_each() ve json_tree()

json_each(X) üst düzey dizinin veya nesnenin yalnızca doğrudan çocuklarını, json_tree(X) ise tüm alt yapıyı özyinelemeli olarak gezer ve her eleman için bir satır döndürür. İkinci argüman verilirse o yoldaki eleman üst düzey kabul edilir. Dönen tablonun sütunları key, value, type, atom, id, parent, fullkey ve path sütunlarıdır. atom yalnızca ilkel değerlerde doludur; bu yüzden WHERE atom IS NOT NULL ile yaprakları seçebilirsiniz. Belge id sütununun yalnızca her satır için farklı olduğunu garanti eder; hesaplanma biçimi sürümler arasında değişebilir.

SELECT s.id, u.value ->> 'kod', (u.value ->> 'adet') * (u.value ->> 'fiyat') AS satir_toplam
FROM siparis AS s, json_each(s.veri, '$.urunler') AS u;
-- çıktı:
-- 1|MS-100|700
-- 1|KB-200|900
-- 2|CB-001|360

SELECT s.id, sum((u.value ->> 'adet') * (u.value ->> 'fiyat')) AS toplam
FROM siparis AS s, json_each(s.veri, '$.urunler') AS u
GROUP BY s.id;
-- çıktı:
-- 1|1600
-- 2|360

SELECT key, value, type, fullkey FROM json_each('{"ad":"Can","etiket":["a","b"],"puan":4.5}');
-- çıktı:
-- ad|Can|text|$.ad
-- etiket|["a","b"]|array|$.etiket
-- puan|4.5|real|$.puan

SELECT fullkey, atom FROM json_tree('{"ad":"Can","etiket":["a","b"]}') WHERE atom IS NOT NULL;
-- çıktı:
-- $.ad|Can
-- $.etiket[0]|a
-- $.etiket[1]|b

İlk iki sorgu, sipariş satırlarını ilişkisel bir tablo gibi işlemenin yolunu gösteriyor: tablo ile json_each() aynı FROM listesine yazılır ve her sipariş kendi ürünleriyle birleşir. Bu kullanım belgede de örneklenir. Toplama ve gruplama ayrıntıları için GROUP BY yazısına, alt sorgu ve CTE ile birleştirme için WITH yazısına bakın.

JSON değiştirmek: json_set(), json_insert(), json_replace(), json_remove()

Üç değiştirme fonksiyonu da bir JSON değeri ve ardından yol-değer çiftleri alır, yeni bir JSON döndürür. Farkları yalnızca var olan bir değerin üzerine yazıp yazmadıkları ve olmayanı oluşturup oluşturmadıklarıdır. Düzenlemeler soldan sağa sırayla uygulanır; önceki bir düzenleme sonraki yolun ne bulacağını etkileyebilir.

SELECT json_set('{"a":1}', '$.a', 2, '$.b', 3),
       json_insert('{"a":1}', '$.a', 2, '$.b', 3),
       json_replace('{"a":1}', '$.a', 2, '$.b', 3);
-- çıktı: {"a":2,"b":3}|{"a":1,"b":3}|{"a":2}

SELECT json_set('{"etiket":["a"]}', '$.etiket[#]', 'b'), json_insert('[1,2]', '$[#]', 3);
-- çıktı: {"etiket":["a","b"]}|[1,2,3]

SELECT json_remove('{"a":1,"b":2,"c":3}', '$.b'), json_remove('[0,1,2,3]', '$[0]', '$[0]');
-- çıktı: {"a":1,"c":3}|[2,3]

UPDATE siparis SET veri = json_set(veri, '$.durum', 'kargoda') WHERE id = 2;
SELECT veri ->> 'durum' FROM siparis WHERE id = 2;
-- çıktı: kargoda

Dizinin sonuna eleman eklemek için yolda [#] kullanılır. json_remove() yolları sırayla işlediği için $[0] yolunu iki kez vermek ilk iki elemanı siler; bulunamayan yollar sessizce yok sayılır. Fonksiyonlar sütunu yerinde değiştirmez; sonucu bir UPDATE ile geri yazmanız gerekir. Dizinin başına veya ortasına eleman eklemek için belge 3.53.0 ile gelen json_array_insert() fonksiyonunu önerir; bu yazının sonundaki bölümde anlatılıyor.

Değer argümanları: metin mi, JSON mu?

JSON fonksiyonlarına verilen "değer" argümanları kural olarak düz metin kabul edilir ve tırnaklı bir JSON metnine dönüşür; metin geçerli bir JSON gibi görünse bile. Değer argümanı yalnızca başka bir JSON fonksiyonunun sonucuysa veya -> işlecinden geliyorsa JSON olarak eklenir. ->> sonucu her zaman metin sayılır. Bu kural, json_object() ve json_array() çağrılarını iç içe kurabilmenizi sağlar.

SELECT json_set('{}', '$.liste', '[1,2]'), json_set('{}', '$.liste', json('[1,2]')),
       json_set('{}', '$.liste', json_array(1, 2));
-- çıktı: {"liste":"[1,2]"}|{"liste":[1,2]}|{"liste":[1,2]}

SELECT json_object('ad', 'Ayşe', 'yas', 31, 'aktif', json('true'), 'notlar', json_array()),
       json_array(1, 'iki', NULL, 3.5);
-- çıktı: {"ad":"Ayşe","yas":31,"aktif":true,"notlar":[]}|[1,"iki",null,3.5]

SELECT json_patch('{"ad":"Can","tel":"0532","adres":{"il":"Ankara","ilce":"Çankaya"}}',
                  '{"tel":null,"adres":{"ilce":"Keçiören"}}');
-- çıktı: {"ad":"Can","adres":{"il":"Ankara","ilce":"Keçiören"}}

SELECT json_quote('metin'), json_quote(3.5), json_quote(NULL), json_quote('[1]'), json_quote(json('[1]'));
-- çıktı: "metin"|3.5|null|"[1]"|[1]

İlk satırdaki fark, uygulama kodundan gelen JSON metnini bir alana yazarken sık yapılan bir hatayı gösteriyor: metni doğrudan verirseniz dizi yerine "[1,2]" metni saklanır. Doğru yol metni json() ile sarmaktır. json_patch() RFC 7396 MergePatch algoritmasını uygular; nesnelerde ekleme, değiştirme ve null ile silme yapar, ama dizileri bölünmez bir bütün olarak ele aldığı için dizi elemanlarını tek tek değiştiremez.

Satırlardan JSON üretmek: json_group_array() ve json_group_object()

Toplama fonksiyonları sorgu sonucunu bir JSON dizisine veya nesnesine dönüştürür; bir API yanıtını doğrudan veritabanında hazırlamak için kullanışlıdır. Belgeye göre giriş satırı yoksa da geçerli, boş bir dizi veya nesne döner. json_group_object() adı NULL olan satırları atlar; 3.50.0 sürüm notları bu davranışın bir hata düzeltmesi olarak eklendiğini belirtir. json_group_array() ise NULL dahil tüm girdileri diziye koyar.

CREATE TABLE urun(id INTEGER PRIMARY KEY, ad TEXT, kategori TEXT, fiyat INTEGER);
INSERT INTO urun(ad, kategori, fiyat) VALUES
  ('Mouse', 'çevre', 350), ('Klavye', 'çevre', 900), ('Kablo', 'aksesuar', 120), ('Kılıf', NULL, 80);

SELECT json_group_array(ad) FROM urun;
-- çıktı: ["Mouse","Klavye","Kablo","Kılıf"]

SELECT json_group_object(ad, fiyat) FROM urun WHERE kategori = 'çevre';
-- çıktı: {"Mouse":350,"Klavye":900}

SELECT kategori, json_group_array(json_object('ad', ad, 'fiyat', fiyat))
FROM urun WHERE kategori IS NOT NULL GROUP BY kategori ORDER BY kategori;
-- çıktı:
-- aksesuar|[{"ad":"Kablo","fiyat":120}]
-- çevre|[{"ad":"Mouse","fiyat":350},{"ad":"Klavye","fiyat":900}]

-- Tuzak: yinelenen anahtar ve NULL anahtar
SELECT json_group_object(kategori, ad) FROM urun;
-- çıktı: {"çevre":"Mouse","çevre":"Klavye","aksesuar":"Kablo"}

SELECT json_group_array(ad) FROM urun WHERE 0;
-- çıktı: []

JSONB: ikili biçimde saklamak

3.45.0 sürümünden beri SQLite, JSON'un iç ayrıştırma ağacını JSONB adlı ikili biçimde BLOB olarak saklamanıza izin verir. JSON metni kabul eden her fonksiyon JSONB de kabul eder ve girdiyi yeniden ayrıştırmak zorunda kalmadığı için daha hızlı çalışır; biçim metinden biraz daha az yer de kaplar. jsonb_ ile başlayan fonksiyonlar sonucu JSONB olarak döndürür. Belge iki önemli uyarı yapar: biçim yalnızca SQLite'ın iç kullanımı içindir ve dışarıda kullanılmamalı veya çözümlenmeye çalışılmamalıdır; adı PostgreSQL'den esinlense de PostgreSQL'in JSONB biçimiyle ikili uyumlu değildir.

SELECT typeof(jsonb('{"a":1}')), json(jsonb('{"a":1}')), jsonb('{"a":1}') ->> 'a';
-- çıktı: blob|{"a":1}|1

SELECT length(jsonb('{"ad":"Ayşe","yas":31}')), length('{"ad":"Ayşe","yas":31}');
-- çıktı: 18|22

SELECT json_valid(jsonb('[1]')), json_valid(jsonb('[1]'), 4), json_valid(jsonb('[1]'), 6);
-- çıktı: 0|1|1

CREATE TABLE etkinlik(id INTEGER PRIMARY KEY, veri BLOB);
INSERT INTO etkinlik(veri) VALUES (jsonb('{"tur":"giris","kullanici":42}'));
UPDATE etkinlik SET veri = jsonb_set(veri, '$.ip', '10.0.0.5');
SELECT typeof(veri), veri ->> 'tur', json(veri) FROM etkinlik;
-- çıktı: blob|giris|{"tur":"giris","kullanici":42,"ip":"10.0.0.5"}

JSONB değeri okunabilir hâle getirmek için json() çağırırsınız; işleçler ve jsonb_set() gibi fonksiyonlar doğrudan JSONB üzerinde çalışır. json_valid() tek argümanla JSONB için 0 verir, çünkü varsayılan bayrak katı JSON metni arar; JSONB denetimi için 4 veya 6 bayrağını kullanın. Performans bölümünde belge, bir JSON fonksiyonuna başka bir JSON fonksiyonunun sonucunu verirken iç çağrının jsonb_ sürümünü kullanmanın genellikle daha verimli olduğunu, toplama fonksiyonlarının ise metinle çalıştığı için bu kuralın istisnası olduğunu belirtir.

JSON alanlarını indekslemek

Bir JSON alanına göre sık filtreleme yapıyorsanız o alanı çıkaran ifadeyi indeksleyebilirsiniz. Planlayıcının indeksi kullanabilmesi için sorgudaki ifadenin indeksteki ifadeyle aynı olması gerekir; aynı değeri döndüren farklı bir yazım indeksi kullanmaz.

CREATE TABLE siparis(id INTEGER PRIMARY KEY, veri TEXT);
CREATE INDEX siparis_sehir ON siparis(veri ->> '$.musteri.sehir');

EXPLAIN QUERY PLAN SELECT id FROM siparis WHERE veri ->> '$.musteri.sehir' = 'Ankara';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH siparis USING COVERING INDEX siparis_sehir (<expr>=?)

EXPLAIN QUERY PLAN SELECT id FROM siparis WHERE json_extract(veri, '$.musteri.sehir') = 'Ankara';
-- çıktı:
-- QUERY PLAN
-- `--SCAN siparis

->> ile kurulan indeks aynı ifadeyle yazılan sorguda kullanıldı, json_extract() ile yazılan eşdeğer sorgu ise tabloyu taradı. Projede tek bir yazım biçiminde karar kılın. İfade indekslerinin kuralları ve sınırları partial index ve ifade indeksi yazısında, alanı ayrı bir sütun olarak tutmanın yolu ise hesaplanan sütunlar yazısında anlatılıyor.

Okunabilir çıktı ve metin kaçışı

json_pretty() 3.46.0 ile eklendi ve json() gibi çalışır, ancak okunabilirlik için girinti ekler. İkinci argüman girinti metnidir; verilmezse her düzey için dört boşluk kullanılır. json_quote() bir SQL değerini JSON gösterimine çevirir (yukarıdaki örneklerde görüldü). Komut satırında JSON çıktısıyla çalışırken .mode json çıktı biçimi de işinize yarar.

SELECT json_pretty('{"a":[1,2],"b":{"c":true}}');
-- çıktı:
-- {
--     "a": [
--         1,
--         2
--     ],
--     "b": {
--         "c": true
--     }
-- }

Yeni sürümlerde: json_array_insert() ve jsonb_each()

SQLite 3.53.0 sürüm notları json_array_insert() ve jsonb_array_insert() fonksiyonlarını listeler. Bu fonksiyonlar json_replace() gibi çalışır ama var olan elemanın üzerine yazmak yerine diziyi genişletip yeni elemanı yolun gösterdiği konuma yerleştirir; yol bir dizi elemanıyla bitmezse hata verir. 3.51.0 ise jsonb_each() ve jsonb_tree() fonksiyonlarını ekledi; bunlar dizi ve nesne elemanlarının value sütununu metin yerine JSONB olarak döndürür. Aşağıdaki örnekler bu sürümleri gerektirir; çıktı yorumları belgedeki örneklerden alınmıştır, son sorgunun çıktısını ise belge göstermediği için vermiyoruz.

-- SQLite 3.53.0 ve sonrası gerekir (belgedeki örnekler)
SELECT json_array_insert('[1,2,3]', '$[1]', 'new');
-- çıktı: [1,"new",2,3]
SELECT json_array_insert('{"a":[1,2,3]}', '$.a[0]', 'new');
-- çıktı: {"a":["new",1,2,3]}

-- SQLite 3.51.0 ve sonrası gerekir
SELECT key, typeof(value) FROM jsonb_each('{"liste":[1,2],"ad":"x"}');

Sık yapılan hatalar

• JSON metnini değer argümanı olarak düz vermek: dizi veya nesne yerine tırnaklı metin saklanır; json() ile sarın.
• -> ile ->> işleçlerini karıştırmak: karşılaştırma ve hesaplama için SQL değeri döndüren ->> gerekir.
• Yolda $ işaretini unutmak: fonksiyonlarda yol $ ile başlamalıdır; yalnızca işleçler çıplak anahtar adını kabul eder.
• JSON true değerini metin sanmak: SQL tarafında 1 olarak gelir.
• JSON'u BLOB olarak saklamak: belge, JSON metnini BLOB olarak saklayan eski veritabanlarında değerlerin TEXT olarak güncellenmesini önerir, çünkü bazı BLOB değerler hem geçerli JSONB hem geçerli metin JSON olarak yorumlanabilir.
• İndeksi farklı bir yazımla sorgulamak: ifade indeksi yalnızca birebir aynı ifadeyle kullanılır.

Sık Sorulan Sorular

SQLite'ta JSON sütun tipi var mı?

Hayır. Belgeye göre geriye dönük uyumluluk nedeniyle yeni bir JSON tipi eklenemez; JSON metin olarak (veya JSONB olarak BLOB içinde) saklanır. Sütun tanımına JSON yazmak yalnızca bir tip adıdır ve affinity kurallarına göre yorumlanır; doğrulama için CHECK (json_valid(...)) ekleyin. Tip adlarının nasıl yorumlandığı type affinity yazısında.

JSON mı JSONB mi saklamalıyım?

Veriyi sık okuyup güncelliyorsanız JSONB ayrıştırma maliyetini ortadan kaldırır ve daha az yer kaplar. Veriyi başka araçlarla doğrudan okumanız veya insanların görmesi gerekiyorsa metin JSON daha pratiktir. Belgeye göre SQLite'ın JSONB biçiminde de çoğu işlem eleman sayısıyla doğrusal sürer; avantajı sabit zamanlı erişim değil, ayrıştırmanın atlanmasıdır.

İç içe ne kadar derin JSON işlenebilir?

Belgeye göre ayrıştırıcı özyinelemeli çalıştığı için 1000 düzeyden derin iç içe yapılar geçersiz kabul edilir.

Uygulamadan JSON değerini nasıl bağlamalıyım?

JSON metnini bir parametre olarak bağlayın ve sorguda gerekirse json(?) ile sarın. Parametreli sorgunun kendisi dil bağlayıcısına göre değişir; örneğin Node.js sqlite modülü yazımızda ve PHP PDO yazımızda anlatılıyor.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · Metin fonksiyonları · GROUP BY ve aggregate · İfade indeksi · Hesaplanan sütunlar · .mode çıktı biçimleri