PostgreSQL'de JSONB Sorgulama: Operatörler, Filtreler ve İndeks

JSON mu JSONB mi? Ne zaman hangisi?

PostgreSQL iki JSON veri türü sunar. json türü, girdiyi metin olarak birebir saklar ve her işlemde yeniden ayrıştırır; boşlukları, anahtar sırasını ve yinelenen anahtarları korur. jsonb ise ayrıştırılmış ikili biçimde tutulur. Kaydı biraz daha yavaştır ama işlenmesi belirgin biçimde hızlıdır ve indekslenebilir. jsonb boşlukları ve anahtar sırasını korumaz; yinelenen anahtarda yalnızca son değeri tutar. PostgreSQL belgeleri, çoğu uygulamanın JSON veriyi jsonb olarak saklamasını önerir.

Pratik kural: ham kaydı olduğu gibi arşivlemek gerekiyorsa (örneğin dış sistemden gelen yanıtın aslını saklamak) json, sorgulanacak veri için jsonb kullanılır. Ayrıca her şeyi JSONB'ye doldurmayın. Sık süzülen, birleştirilen ve kısıtlanan alanlar (kimlik, tarih, tutar) normal sütun olarak kalmalı; ayrıntılar değişken olan özellikler JSONB sütununa konabilir. Bu karma tasarım hem esnekliği hem sorgu performansını korur.

Örnek tablo ve alan okuma: ->, ->>, #>, #>>

Aşağıdaki örneklerde sipariş verisi şu tabloda tutulsun:

CREATE TABLE siparisler ( id bigserial PRIMARY KEY, veri jsonb NOT NULL );

Bir kayıtta veri sütununun içeriği örneğin {"durum": "teslim", "alici": {"ad": "Elif", "sehir": "Ankara"}, "kalemler": [{"product": "defter", "unit": "paket"}]} gibi olabilir.

Alan okumak için iki temel operatör vardır. -> işareti jsonb döndürür, ->> ise metin döndürür:

SELECT veri -> 'alici' AS alici_nesnesi, veri ->> 'durum' AS durum FROM siparisler;

İç içe yolu tek seferde okumak için #> ve #>> kullanılır; yol, anahtarlardan oluşan bir dizi olarak verilir:

SELECT veri #>> '{alici,sehir}' AS sehir FROM siparisler;

Belgeye göre bu operatörler yapı eşleşmediğinde (alan yoksa) hata vermez, NULL döndürür. Bu rahatlık iyi bir şeydir ama yazım hatasını da gizler: anahtar adını yanlış yazarsanız sorgu hata değil boş sonuç verir.

Filtreleme: tür dönüşümü, içerme (@>) ve varlık (?)

->> operatörü her zaman metin döndürdüğü için sayısal ya da tarihsel karşılaştırmada dönüşüm gerekir:

SELECT id FROM siparisler WHERE (veri ->> 'toplam_tutar')::numeric > :esik;

JSONB'ye özgü iki operatör filtrelemeyi kolaylaştırır. İçerme operatörü @>, soldaki belgenin sağdaki belgeyi içerip içermediğini sınar:

SELECT id FROM siparisler WHERE veri @> '{"durum": "iptal"}';

Varlık operatörü ? ise bir metnin üst düzey anahtar ya da dizi elemanı olarak bulunup bulunmadığını sınar:

SELECT id FROM siparisler WHERE veri ? 'indirim_kodu';

Dikkat edilecek nokta: ? yalnızca üst düzeyde bakar; iç içe bir nesnedeki anahtarı bulmaz ve nesne değerlerini dikkate almaz. İç içe alan için önce o alanı çıkarın: veri -> 'alici' ? 'telefon'. İçerme operatöründe de iç içe yapı soldaki belgedeki konumuyla eşleşmelidir; kök seviyesinde olmayan bir anahtarı kök seviyesinde aramak sonuç vermez.

Dizileri açmak ve JSONPath ile sorgulamak

Siparişlerdeki kalem listesi gibi dizileri satırlara açmak için jsonb_array_elements işlevi kullanılır. Her dizi elemanı ayrı bir satır olur:

SELECT k ->> 'product' AS product, count(*) AS siparis_sayisi FROM siparisler s, jsonb_array_elements(s.veri -> 'kalemler') AS k GROUP BY k ->> 'product';

Böylece JSON içindeki liste, olağan SQL ile gruplanabilir ve toplanabilir: hangi ürün kaç siparişte yer almış, tek bir GROUP BY ile görülür. Nesnenin anahtar ve değerlerini satıra çevirmek için jsonb_each işlevi vardır.

Daha karmaşık koşullar için JSONPath dili kullanılır. @? operatörü bir yolun sonuç döndürüp döndürmediğini, @@ operatörü bir yol koşulunun doğru olup olmadığını sınar; jsonb_path_query işlevi ise yolun seçtiği tüm öğeleri satır olarak getirir. JSONPath ifadesi, kök belgeden başlayıp köşeli parantezle dizide gezinen ve soru işaretiyle süzgeç koşulu alan bir yol yazımıdır. Örneğin kalemler dizisinde belirli bir ürünü içeren siparişleri süzgeçli yolla bulabilirsiniz. Operatörlerin tam sözdizimi için PostgreSQL belgesindeki JSON işlevleri bölümüne bakın.

İndeksleme: GIN, jsonb_path_ops ve ifade indeksi

JSONB sütununda filtre yaptığınızda tablo büyüdükçe tam tarama yavaşlar. JSONB'ye özgü çözüm GIN indeksidir:

CREATE INDEX siparis_veri_gin ON siparisler USING GIN (veri);

Varsayılan işleç sınıfı (jsonb_ops), ?, ?|, ?&, @>, @? ve @@ operatörlerini destekler. Alternatif jsonb_path_ops sınıfı yalnızca @>, @? ve @@ operatörlerini destekler; ama belgeye göre genellikle çok daha küçük ve içerme sorgularında daha hızlıdır. Anahtar varlığı sorguları (?) bu sınıfla indekslenemez:

CREATE INDEX siparis_veri_yol ON siparisler USING GIN (veri jsonb_path_ops);

İkinci önemli nokta, indeksin hangi ifadeye uygulandığıdır. WHERE veri -> 'etiketler' ? 'acil' gibi bir sorgu, veri sütunundaki GIN indeksini kullanamaz; çünkü ? operatörü indekslenen sütuna değil, çıkarılan ifadeye uygulanıyor. Belgelerdeki çözüm, o ifade için indeks kurmaktır:

CREATE INDEX siparis_etiket_gin ON siparisler USING GIN ((veri -> 'etiketler'));

Sorgunun indeksi gerçekten kullanıp kullanmadığını EXPLAIN ile doğrulayın. Çok sık süzülen tek bir alan varsa, o alanı normal sütuna çıkarıp B-ağacı indeksi vermek çoğunlukla daha sade ve hızlıdır.

Güncelleme ve JSON üretme: jsonb_set, jsonb_build_object

JSONB belgesinin tamamını yeniden yazmadan tek alanı değiştirmek için jsonb_set kullanılır. Yol bir metin dizisi olarak verilir:

UPDATE siparisler SET veri = jsonb_set(veri, '{durum}', '"kargoda"') WHERE id = :siparis_id;

Yeni değerin kendisi geçerli bir JSON olmalıdır; metin değerler bu yüzden çift tırnakla sarılır. Belgeye göre yolun ara adımları yoksa hedef belge değişmeden döner; son anahtar yoksa varsayılan olarak eklenir. Bu davranış, yanlış yol yazdığınızda güncelleme "başarılı" görünüp hiçbir şey değişmemesine yol açabilir; etkilenen satırı kontrol edin.

Tersi yönde, ilişkisel sütunlardan JSON üretmek için jsonb_build_object kullanılır; anahtar ve değer çiftlerini sırayla alır:

SELECT jsonb_build_object('id', id, 'durum', veri ->> 'durum') FROM siparisler;

Bu işlev, uygulamanın ihtiyaç duyduğu yanıt biçimini doğrudan veritabanında hazırlamak için kullanışlıdır. Çok büyük JSON belgelerini sık güncellemek ise maliyetlidir; bu durumda sık değişen alanları ayrı sütunlara ya da tablolara ayırmak düşünülmelidir.

Sık hatalar ve kontrol listesi

Sık yapılan hatalar: ->> sonucunu dönüştürmeden sayı gibi karşılaştırmak (metin sıralaması beklenmedik sonuç verir), anahtar adı yanlış yazılınca boş sonuç gelmesine aldırmamak, ? operatörünün iç içe alanlara bakmadığını unutmak, indeksi kurup EXPLAIN ile kullanıldığını doğrulamamak, JSONB'yi tüm şemanın yerine koymak.

Kontrol listesi: • Sorgulanacak veri için json değil jsonb seçildi mi? • Sık süzülen alanlar normal sütunda mı? • Dönüştürme gereken yerlerde tür dönüşümü yapıldı mı? • Filtre için @> mi ? mi gerektiği netleşti mi? • GIN indeksi kurulduysa işleç sınıfı sorgu türüne uygun mu? • Dizi içindeki öğeler gerekiyorsa jsonb_array_elements ile açıldı mı? • Hangi alanların şeması değişken, hangilerinin sabit olduğu yazıldı mı?

Bu beceriyi Mindset programlarında nerede çalışırsınız?

PostgreSQL ile Veri Analizi eğitimi, ilişkisel veritabanı ve SQL bilen veri analistleri, arka uç geliştiricileri ve veri mühendisleri için uygundur. JSON sorgulama, yarı yapılandırılmış verinin (uygulama günlükleri, dış sistem yanıtları, esnek ürün özellikleri) ilişkisel analize taşındığı durumlarda gündeme gelir.

Modül başlıklarında PostgreSQL mimarisi ve veri modelleme, ileri veri tipleri ve JSON sorgulama, indeksleme stratejileri ve sorgu optimizasyonu, ileri analitik sorgular ile PL/pgSQL ile programatik geliştirme yer alır. Kurumsal ekipler, JSONB sorgulama ile indeksleme başlıklarını birlikte ele alarak yarı yapılandırılmış veri üzerinde hızlı sorgu tasarımını çalışabilir.