MariaDB JSON Fonksiyonları: JSON_VALUE, JSON_EXTRACT, JSON_SET ve JSON_TABLE

MariaDB JSON tipi gerçekte nedir?

Belgelere göre JSON takma adı, MySQL'den ifade tabanlı replikasyon yapabilmek ve MySQL'in döküm dosyalarını okuyabilmek için eklendi. MariaDB veriyi ikili bir biçime çevirmez, LONGTEXT olarak saklar; belgeler bunun gerekçesi olarak JSON tipinin SQL:2016 standardıyla çeliştiğini ve kendi ölçümlerine göre performansın en az eşdeğer olduğunu belirtir. Yazdığınız metin harfi harfine korunur; boşluklar, anahtar sırası ve tekrarlanan anahtarlar değişmez.

CREATE TABLE t (j JSON);

DESC t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j     | longtext | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

JSON takma adıyla tanımlanan her sütuna otomatik bir CHECK (json_valid(...)) kısıtı eklenir. Geçersiz bir belge yazmaya çalıştığınızda 4025 numaralı kısıt hatası alırsınız. Aynı kısıtı elle herhangi bir LONGTEXT sütuna da ekleyebilirsiniz. Kısıtların genel davranışı için foreign key ve CHECK kısıtlamaları yazısına bakın.

INSERT INTO json_example VALUES
  ('Invalid', '{');

ERROR 4025 (23000): CONSTRAINT `json_example.example` failed for `test`.`json_example`

JSONPath: belgenin içinde yol yazmak

JSON fonksiyonlarının çoğu belgenin hangi parçasıyla çalışacağını bir yol ifadesiyle öğrenir. Yol her zaman bağlam öğesini temsil eden $ ile başlar. Ardından adımlar gelir: .ad bir nesne üyesini, ."boşluklu ad" geçerli tanımlayıcı olmayan bir üyeyi, .* tüm üyeleri seçer. Dizilerde [N] sıfırdan sayılan N'inci öğeyi, [*] tüm öğeleri seçer. Geçerli öğe nesne değil de dizi ise üye seçici hiçbir şey seçmez; tersi de geçerlidir.

MariaDB 10.9'dan itibaren diziler için negatif indeks ([-1] son öğe), last anahtar sözcüğü ([last-1]) ve aralık gösterimi ([0 to 2]) desteklenir. ** joker adımı ise tüm alt öğeleri özyinelemeli olarak dolaşır; belgelere göre son adım olamaz ve ardından bir üye ya da dizi seçici gelmelidir. Örneğin '$**.price' belgenin herhangi bir derinliğindeki tüm price üyelerini bulur.

MariaDB yalnızca lax yol modunu destekler ve mod belirtilmezse bu kullanılır. Belgelere göre SQL standardındaki strict modu ve filtre ifadeleri desteklenmez. Aralıkta başlangıç bitişten büyükse (örneğin [4 to 2]) bu imkânsız bir aralık sayılır ve sonuç NULL olur.

Değer okumak: JSON_VALUE, JSON_QUERY ve JSON_EXTRACT

Üç okuma fonksiyonu arasındaki fark, döndürdükleri değerin türüdür. JSON_VALUE() yalnızca skaler (metin, sayı, mantıksal) bir değer döndürür ve tırnaksız verir; yol bir nesneye veya diziye denk geliyorsa NULL döner. JSON_QUERY() tam tersine yalnızca nesne veya dizi döndürür. Belgelerdeki karşılaştırma bunu net gösterir:

SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Monty" }';

SELECT JSON_QUERY(@json,'$.x'), JSON_VALUE(@json,'$.x');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.x') | JSON_VALUE(@json,'$.x') |
+-------------------------+-------------------------+
| [0,1]                   | NULL                    |
+-------------------------+-------------------------+

SELECT JSON_QUERY(@json,'$.z'), JSON_VALUE(@json,'$.z');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.z') | JSON_VALUE(@json,'$.z') |
+-------------------------+-------------------------+
| NULL                    | Monty                   |
+-------------------------+-------------------------+

JSON_EXTRACT(belge, yol[, yol ...]) her türden değeri JSON gösterimiyle döndürür. Birden fazla eşleşme olabiliyorsa (birden fazla yol verdiyseniz ya da joker kullandıysanız) sonuçlar eşleşme sırasıyla bir diziye sarılır. Hiçbir yol eşleşmezse NULL döner; yol veya belge geçersizse hata oluşur. Metin değerler tırnaklı geldiği için karşılaştırma ve gösterimde genellikle JSON_UNQUOTE() ile birlikte kullanılır.

SET @json = '[1, 2, [3, 4]]';

SELECT JSON_EXTRACT(@json, '$[2]');
+-----------------------------+
| JSON_EXTRACT(@json, '$[2]') |
+-----------------------------+
| [3, 4]                      |
+-----------------------------+

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"id":"1", "name":"Name"}', '$.name')) `name`;
+------+
| name |
+------+
| Name |
+------+

Bir değerin belgede bulunup bulunmadığını sormak için JSON_CONTAINS(belge, değer[, yol]) kullanılır. Fonksiyon bulursa 1, bulamazsa 0, parametrelerden biri NULL ise NULL döndürür. Aranan değerin kendisi de bir JSON metni olarak verilir; yani sayı 2'yi aramak için '2', bir nesneyi aramak için '{"C": 1}' yazılır. Yol verirseniz arama yalnızca o noktada yapılır ve yolda joker kullanılamaz. Belgedeki örnekte {"A": 0, "B": {"C": 1}, "D": 2} belgesinde '2' değeri $.A yolunda 0, $.D yolunda 1 sonucunu verir. Yalnızca bir yolun var olup olmadığını öğrenmek isterseniz JSON_CONTAINS_PATH() ve JSON_EXISTS() da vardır.

SET @json = '{"A": 0, "B": {"C": 1}, "D": 2}';

SELECT JSON_CONTAINS(@json, '{"C": 1}', '$.B');
+-----------------------------------------+
| JSON_CONTAINS(@json, '{"C": 1}', '$.B') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

Doğrulama: JSON_VALID ve JSON_SCHEMA_VALID

JSON_VALID(değer) geçerli bir belge için 1, geçersiz için 0, NULL için NULL döndürür. Yalnızca söz diziminin doğru olup olmadığına bakar; hangi anahtarların bulunması gerektiğini denetlemez. Bunun için MariaDB 11.1'den itibaren JSON_SCHEMA_VALID(şema, belge) kullanılabilir. Fonksiyon belgelere göre JSON Schema Draft 2020'yi destekler; dış kaynaklar ve hyper-schema anahtar sözcükleri desteklenmez, date veya email gibi biçimler yalnızca açıklama (annotation) olarak ele alınır. Belge şemaya uymuyorsa hangi kuralın bozulduğunu söylemez, yalnızca yanlış döner.

CREATE TABLE ayarlar (
  id INT PRIMARY KEY,
  veri JSON CHECK (JSON_SCHEMA_VALID('{
    "type": "object",
    "properties": {
      "tema": {"type": "string", "maxLength": 20},
      "sayfa_boyutu": {"type": "number", "minimum": 1}
    },
    "required": ["tema"]
  }', veri))
);

Bu tabloya tema anahtarı olmayan ya da sayfa_boyutu değeri sayı olmayan bir belge yazıldığında CHECK kısıtı çalışır ve satır reddedilir. Belgelerdeki örnekte bu durumda 4025 numaralı kısıt hatası görülür.

Belgeyi değiştirmek: JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE

JSON değiştiren fonksiyonlar sütunu yerinde güncellemez; yeni belgeyi döndürür. Sütunu güncellemek için sonucu UPDATE ile geri yazarsınız. Dört fonksiyon, yolun belgede bulunup bulunmamasına göre farklı davranır:

SELECT JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4);
+-----------------------------------------------------+
| JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4) |
+-----------------------------------------------------+
| { "A": 1, "B": [2, 4]}                              |
+-----------------------------------------------------+

SELECT JSON_REMOVE('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C');
+-------------------------------------------------------+
| JSON_REMOVE('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C') |
+-------------------------------------------------------+
| {"A": 1, "B": 2}                                      |
+-------------------------------------------------------+

Bu fonksiyonlar yollarda * veya ** jokerine izin vermez; belgelere göre JSON_SET, JSON_INSERT ve JSON_REPLACE bu durumda hata verir, JSON_REMOVE ise NULL döndürüp uyarı üretir. Birden fazla yol-değer çifti verdiğinizde soldan sağa işlenir ve her adım bir öncekinin sonucunu kullanır. JSON_INSERT örneğinde dikkat edilmesi gereken bir ayrıntı da vardır: değer olarak '[3, 4]' metnini verirseniz belgeye dizi değil metin olarak eklenir; belgedeki çıktı bunu "C":"[3, 4]" olarak gösterir.

UPDATE urunler
SET ozellikler = JSON_SET(ozellikler, '$.stok', 15, '$.renk', 'mavi')
WHERE urun_id = 7;

JSON üretmek: JSON_OBJECT, JSON_ARRAYAGG ve JSON_DETAILED

İlişkisel veriden JSON üretmek için JSON_OBJECT(anahtar, değer, ...) ve JSON_ARRAY(...) kullanılır. JSON_OBJECT tek sayıda parametre aldığında veya bir anahtar NULL olduğunda hata verir. Toplama fonksiyonu olan JSON_ARRAYAGG() bir gruptaki değerleri diziye toplar; DISTINCT, ORDER BY ve LIMIT kabul eder. Dönen değerin en büyük uzunluğu group_concat_max_len değişkeniyle sınırlıdır ve belgelere göre pencere fonksiyonu olarak kullanılamaz.

SELECT JSON_OBJECT("id", 1, "name", "Monty");
+---------------------------------------+
| JSON_OBJECT("id", 1, "name", "Monty") |
+---------------------------------------+
| {"id": 1, "name": "Monty"}            |
+---------------------------------------+

SELECT kategori,
       JSON_ARRAYAGG(JSON_OBJECT('id', urun_id, 'ad', ad) ORDER BY ad) AS urunler
FROM urunler
GROUP BY kategori;

Okunabilir çıktı için JSON_DETAILED() (veya takma adı JSON_PRETTY()) iç içe yapıları girintili gösterir; tersine, gereksiz boşlukları atmak için JSON_COMPACT() vardır. Gruplamanın genel kuralları için GROUP BY ve toplama fonksiyonları yazısına bakabilirsiniz.

JSON_TABLE ile JSON dizisini satırlara açmak

MariaDB 10.6'dan beri bulunan JSON_TABLE, bir JSON belgesini ilişkisel bir tabloya dönüştüren tablo fonksiyonudur. SELECT ifadesinin FROM kısmında ve çok tablolu UPDATE/DELETE ifadelerinde tablo yerine kullanılabilir. İlk parametre belge, ikincisi satır kaynağını gösteren bağlam yoludur; COLUMNS kısmında her sütunun adı, tipi ve o satıra göre göreli yolu yazılır.

SET @json='
[
  {"name":"Laptop", "color":"black", "price":"1000"},
  {"name":"Jeans",  "color":"blue"}
]';

SELECT * FROM json_table(@json, '$[*]'
  COLUMNS(
   name  VARCHAR(10) path '$.name',
   color VARCHAR(10) path '$.color',
   price DECIMAL(8,2) path '$.price' )
) AS jt;
+--------+-------+---------+
| name   | color | price   |
+--------+-------+---------+
| Laptop | black | 1000.00 |
| Jeans  | blue  |    NULL |
+--------+-------+---------+

Sütun türleri dört çeşittir. PATH sütunları değeri yoldan okur. FOR ORDINALITY sütunu satırları 1'den başlayarak numaralandırır. EXISTS PATH sütunu yol varsa 1, yoksa 0 üretir. NESTED PATH ise iç içe bir diziyi ayrı satırlara açar; yol hiçbir düğüm bulamazsa tüm sütunları NULL olan tek bir satır üretilir. Belgedeki örnekte üç bedeni olan bir pantolon üç satıra, iki bedeni olan tişört iki satıra açılır, bedeni olmayan ürün ise NULL bedenle bir kez görünür.

Değer bulunamadığında veya yapısal bir hata oluştuğunda ne olacağı ON EMPTY ve ON ERROR ile belirlenir; ikisinin de seçenekleri NULL, DEFAULT 'metin' ve ERROR'dır ve yazılmazlarsa NULL varsayılır. Belgelere göre buradaki JSON hatası yalnızca bir dizi veya nesneyi skaler bir sütuna yazmaya çalışmaktır; sayı olmayan bir değeri INT sütuna koymak gibi tip dönüşüm sorunları ON ERROR'u tetiklemez, CAST gibi uyarı üretir. Bir dizi veya nesneyi tek sütunda olduğu gibi almak için o sütunu JSON tipinde tanımlayabilirsiniz (10.6.9 ve sonrası).

SELECT u.urun_id, jt.etiket
FROM urunler AS u,
     JSON_TABLE(u.ozellikler, '$.etiketler[*]'
       COLUMNS (etiket VARCHAR(30) PATH '$' DEFAULT 'yok' ON EMPTY)
     ) AS jt;

JSON alanlarını indekslemek

JSON metni üzerinde doğrudan indeks kurulamaz; belgelere göre MariaDB ifade üzerinde indeks (CREATE INDEX ... ((ifade))) de henüz desteklemez. Önerilen yol, ilgilendiğiniz alanı çıkaran bir sanal sütun tanımlamak ve indeksi o sütuna koymaktır. SQL güçlü tipli, JSON ise zayıf tipli olduğu için sayısal alanları CAST ile açıkça dönüştürmeniz gerekir.

ALTER TABLE t1
  ADD COLUMN vcol1 INT AS (cast(json_value(json_data, '$.column1') AS INTEGER)),
  ADD INDEX(vcol1);

MariaDB 11.8'den itibaren optimizer, WHERE koşulunda sanal sütunun adını değil de tanımındaki ifadenin aynısını yazdığınızda bunu tanır ve indeksi kullanır. Belgelerin şartı, ifadenin sanal sütun tanımıyla birebir aynı olmasıdır. MariaDB 12.1 ile bu iyileştirme ORDER BY ve GROUP BY içinde kullanılan sanal sütun ifadelerine ve tek tablolu UPDATE/DELETE ifadelerine de genişletildi.

Metin alanlarında bir ayrıntı daha vardır: JSON sütununun karşılaştırma kuralı (collation) utf8mb4_bin olduğundan JSON_VALUE() sonucu da büyük-küçük harf duyarlı karşılaştırılır. Belgeler, büyük-küçük harf duyarsız arama isteniyorsa hem sanal sütunda hem sorguda COLLATE utf8mb4_uca1400_ai_ci gibi bir karşılaştırma kuralı belirtmeyi önerir. Karşılaştırma kuralları ve Türkçe karakterler için karakter seti ve collation, indeks türleri için MariaDB index oluşturma yazılarına bakın.

Uygulamalı mini örnek: ürün özellikleri

Aşağıdaki tasarım, her üründe değişen özellikleri bir JSON sütununda tutar, sık filtrelenen marka alanını sanal sütunla indeksler ve stok durumunu JSON_VALUE ile okur. Tablo ve alan adları bize aittir; bu yüzden çıktıyı göstermiyoruz, ama her fonksiyon belgede anlatılan imzayla kullanılmıştır.

CREATE TABLE urunler (
  urun_id INT PRIMARY KEY,
  ad VARCHAR(100) NOT NULL,
  kategori VARCHAR(50) NOT NULL,
  ozellikler JSON,
  marka VARCHAR(50) AS (JSON_VALUE(ozellikler, '$.marka')),
  INDEX (marka)
);

INSERT INTO urunler (urun_id, ad, kategori, ozellikler) VALUES
  (1, 'Kulaklık', 'ses', '{"marka": "Ornek", "stok": 4, "etiketler": ["kablosuz", "bluetooth"]}');

SELECT ad, CAST(JSON_VALUE(ozellikler, '$.stok') AS INTEGER) AS stok
FROM urunler
WHERE marka = 'Ornek';

Bu yaklaşım, şeması sık değişen alanları esnek tutarken önemli alanları ilişkisel dünyada sorgulanabilir kılar. Yine de her şeyi JSON'a koymak doğru değildir: birleştirme, yabancı anahtar ve kısıt gerektiren alanlar normal sütunlarda daha güvenlidir.

Sık yapılan hatalar

• 12.3'te -> operatörünü kullanmak: Bu operatörler 13.1'de geldi. 12.3'te JSON_EXTRACT() ve JSON_UNQUOTE() kullanın.
• JSON_VALUE ile nesne okumaya çalışmak: Yol bir dizi veya nesneye denk geliyorsa sonuç NULL olur; yapısal değer için JSON_QUERY() veya JSON_EXTRACT() kullanın.
• JSON_EXTRACT sonucunu metinle karşılaştırmak: Metin değerler tırnaklı döner. JSON_VALUE() veya JSON_UNQUOTE() ile tırnakları kaldırın.
• Sayısal alanları CAST etmemek: JSON_VALUE() metin döndürür; sayısal sıralama ve indeksli karşılaştırma için CAST(... AS INTEGER) kullanın ve sanal sütundaki ifadeyle aynısını yazın.
• Değiştiren fonksiyonların sütunu güncellediğini sanmak: JSON_SET() yeni bir belge döndürür; değişiklik için UPDATE ... SET sutun = JSON_SET(sutun, ...) yazmanız gerekir.
• Dizi eklerken metin vermek: JSON_INSERT(@j, '$.C', '[3, 4]') değeri metin olarak ekler. Dizi eklemek için değeri JSON_ARRAY(3, 4) ile üretin.

Sık Sorulan Sorular

MariaDB JSON tipi MySQL ile uyumlu mu?

Sözdizimi düzeyinde büyük ölçüde evet: JSON sütun tanımı ve temel fonksiyonlar iki tarafta da çalışır. Fark depolamadadır; MariaDB metin olarak, MySQL ikili biçimde saklar. Bu yüzden MySQL'den satır tabanlı replikasyon JSON sütunlarında çalışmaz ve 12.3'te -> operatörü yoktur.

JSON sütununda arama hızlı olur mu?

Doğrudan JSON metninde arama her satırı ayrıştırmayı gerektirir. Sık sorgulanan alanlar için sanal sütun ve indeks tanımlayın; 11.8'den itibaren optimizer aynı ifadeyi WHERE içinde görünce indeksi kendiliğinden kullanır.

JSON belgesi ne kadar derin olabilir?

Belgelere göre JSON_DEPTH() açısından MariaDB 12.2'den itibaren bir derinlik sınırı yoktur; 12.2 öncesinde en büyük derinlik 32'ydi.

JSON_TABLE ile JOIN yapabilir miyim?

Evet. JSON_TABLE'ın ilk parametresi, kendisinden önce gelen tabloların sütunlarına başvurabilir; bu sayede her satırdaki JSON dizisini o satırla eşleştirerek açabilirsiniz. Birleştirme türleri için JOIN ve alt sorgular yazısına bakın.

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: MariaDB öğrenme rehberi · MariaDB veri tipleri · MariaDB index oluşturma · utf8mb4 ve collation · MariaDB vector arama · MySQL'den MariaDB'ye geçiş · SQL nedir