SQL Serverda JSON veri ile çalışma
Günümüzde artık çoğu data
json formatta tutulmaya başlanıyor.Çünkü developer için datayı bu şekilde
göndermek çok kolay çünkü yeni bir alan eklemek ile ilgili hiçbir endişe yok ve
datayı da saklıyor win-win durum söz konusu.Fakat bu analiz yapmak isteyenler
için zorlayıcı bir hale gelebiliyor.
İşte aslında burada bu
JSON fonksiyonlarını ve bunlardan istediğimiz verileri nasıl alabileceğimizi
göreceğiz.Örnek olarak bir tablo oluşturdum ve uzunca bir örnek JSON data
oluşturttum.Bir çok site var sizde benzer jsonlar ürettirebilirsiniz JSON
Generator yazarak.Ben https://json-generator.com/ sitesini kullandım.
create table json_test
(
id int,
json_data nvarchar(max),
notjson_data nvarchar(max)
)
insert into dbo.json_test values (1,'
[
{
"_id": "66bb99724449934db2fa9cc5",
"index": 0,
"guid": "438a3a2f-114e-4afa-ab78-f9ba31dd25c9",
"isActive": false,
"balance": "$2,026.56",
"picture": "http://placehold.it/32x32",
"age": 35,
"eyeColor": "brown",
"name": "Miranda Horton",
"gender": "male",
"company": "MACRONAUT",
"email": "mirandahorton@macronaut.com",
"phone": "+1 (908) 500-3849",
"address": "298 Cameron Court, Chestnut, South Carolina,
2417",
"registered": "2023-08-01T07:10:58 -03:00",
"latitude": -15.860973,
"longitude": -160.799826,
"tags": [
"quis",
"reprehenderit",
"dolore",
"mollit",
"labore",
"elit",
"eu"
],
"friends": [
{
"id": 0,
"name": "Chris Morrison"
},
{
"id": 1,
"name": "Stein Manning"
},
{
"id": 2,
"name": "Summers Norton"
}
],
"greeting": "Hello, Miranda Horton! You have 9 unread
messages.",
"favoriteFruit": "apple"
},
{
"_id": "66bb99720797a80b7dc0a591",
"index": 1,
"guid": "31e754af-b1b9-4906-a51c-e1f40b345850",
"isActive": false,
"balance": "$2,582.52",
"picture": "http://placehold.it/32x32",
"age": 20,
"eyeColor": "blue",
"name": "Baldwin Blackwell",
"gender": "male",
"company": "LIQUIDOC",
"email": "baldwinblackwell@liquidoc.com",
"phone": "+1 (888) 489-3186",
"address": "210 Beayer Place, Finderne, Virginia,
6791",
"registered": "2017-02-14T04:35:37 -03:00",
"latitude": -20.942119,
"longitude": 113.878654,
"tags": [
"eu",
"laborum",
"id",
"et",
"duis",
"ut",
"et"
],
"friends": [
{
"id": 0,
"name": "Brianna May"
},
{
"id": 1,
"name": "Daniel Pruitt"
},
{
"id": 2,
"name": "Clara Finch"
}
],
"greeting": "Hello, Baldwin Blackwell! You have 2 unread
messages.",
"favoriteFruit": "banana"
}
]',
'test data'
)
Verimi tabloya ekledikden
sonra başlayabilirim.SQL Server’ın JSON tipi için özel olarak ürettiği 7
fonksiyon var şu anda bunların üzerinden tek tek geçeceğiz.
1.
ISJSON
2.
JSON_ARRAY
3.
JSON_MODIFY
4.
JSON_OBJECT
5.
JSON_PATH_EXISTS
6.
JSON_QUERY
7.
JSON_VALUE
ISJSON
Bu fonksiyon çok kolay bir fonksiyon verdiğiniz sütundaki
veriler Json tipinde mi değil mi size bunu dönüyor.Bunun içinde JSON ise 1
değil ise 0 dönüyor.
JSON_ARRAY
JSON oluşturmak için kullandığımız bir fonksiyondur.Biz
parçalamak istiyoruz ama karşınıza böyle bir case çıkabilir bilmekte fayda var.
İster bir tablonun sütunlarını alıp yapabilirsiniz
isterseniz kendi değerlerinizi verebilirsiniz.
Mesela bir tablonun belirli sütunları ile oluşturmak
isterseniz bu şekilde yapabilirsiniz.
SELECT JSON_ARRAY(BusinessEntityID,PersonType,FirstName,LastName)
FROM [AdventureWorks2022].[Person].[Person]
Böyle bir çıktı elde edersiniz.Sütun sayısı size kalmıştır.
İsterseniz elbette tüm satırlara default değerde
ekleyebilrisiniz.
SELECT JSON_ARRAY(BusinessEntityID,PersonType,FirstName,LastName,'Tüm satırlara Hüseyin Ekle')
FROM [AdventureWorks2022].[Person].[Person]
Bu dönen datanın nvarchar(max) tipinde olduğunu
unutmayın.Yalnız bizim döndürdüğümüz bu değerler bir stringdir aslında iç içe
JSON yani Json içinde bir JSON objesi döndürmek isterseniz ise bir sonraki
fonksiyonumuz olan JSON_OBJECT’i kullanmanız gerekir.
SELECT JSON_ARRAY(BusinessEntityID,PersonType,JSON_OBJECT('ad_sutunu':FirstName,'soyad_sutunu':LastName))
FROM [AdventureWorks2022].[Person].[Person]
Dikkat ederseniz bir iç içe JSON söz konusu ve içteki jsonın
key’ini ben belirledim ama value tablodan geldi!
JSON_MODIFY
Var olan bir JSON2ı güncellemenize imkan verir bunu yine
ister default bir değer ile isterseniz de tablodaki bir sütun ile yapabilirsiniz
kolaylıkla.
3 tane parametre alır:
Sütun,path(yani değiştirilecek değer),yeni değer
Bizim örneğimiz üzerinden gidersek bizim örneğimizde2 tane
farklı element barındıran bir JSON’dı.Örneğin ilk dizideki index değerine
bakarsanız bunu 0 görürsünüz ben bunu 15 yapmak istiyorum bu durumda işte bu
fonksiyonu kullanabiliriz.
select JSON_DATA,JSON_MODIFY(JSON_DATA,'$[0].index',15) from dbo.json_test
Dikkat ederseniz index artık 15 geliyor.
Veya
select JSON_DATA,JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin') from dbo.json_test
Yukardaki sorgu ile ilk dizideki friends dizisindeki 2.
Arkadaşın ismini hüseyin yapmak istediğimizde yukarıdaki kodu yazabiliriz.
Burada dikkatini çeken [0] ve [1] kullanımları
olmuştur.JSON’da diziler 0’dan başlar yani 0 aslında 1.ci elementi ifade 1 ise
2.elementi ifade eder.
Ve friends bir dizi idi bu dizinin ikinci elementine gitmek
için friends[1] kullanımı yapmanız gerekir.
Burada genelde biz JSON değiştirdik mi eskisi ile update
etmek isteriz bunun içinde şöyle bir kod ile rahatlıkla yapabilirsiniz.
update dbo.json_test set JSON_DATA=JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin')
tabi Where koymayı unutmayın siz 😊
Birden çok alanı tek seferde güncellemek isterseniz de iç
içe JSON_MODIFY kullanabilirsiniz.Örneğin:
select JSON_DATA,JSON_MODIFY(JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin'),'$[0].index',25) from dbo.json_test
JSON_OBJECT
Bir JSON objesi döndürür.JSON_ARRAY ile arasındaki fark biri
dizi diğeri objedir.Dizilerde key value ilişkisi olmaz indis sırası olur.Fakat
JSON objelerinde bir key value ilişkisi vardır.Aslında Pythondaki
list,dictionary ile aynı gibi düşünebilirsiniz.
Hatta dikkat ederseniz JSON_ARRAY [ ] içinde veri
döndürürken JSON_OBJECT { } içerisinde veri döndürür.
Dolayısıyla bir JSON_OBJECT içerisine n tane veri gönderebilirsiniz ama key vermek
zorundasınız.
select JSON_OBJECT('name':'Hüseyin','surname':'Albayrak')
{"name":"Hüseyin","surname":"Albayrak"}
Gördüğünüz gibi istersek bir tablodan da value’leri
getirebiliriz.Keyleri siz vereceksiniz değişemez.
SELECT JSON_OBJECT('urun_Adi':Name,'liste_fiyat':ListPrice)
FROM [AdventureWorks2022].[Production].[Product]
JSON_PATH_EXISTS
Verilen path JSON içerisinde var mı yok mu onu döner.Var ise
1 değil ise 0 döner.Önemsiz gibi gözükse de çok önemli bir fonksiyondur
özellikle bir loop vb yapıda bu fonksiyonuz hataları çok ciddi engelleyecektir.
select
JSON_PATH_EXISTS(JSON_DATA,'$[0]'), --1 döndü çünkü ilk element vasr
gerçektende
JSON_PATH_EXISTS(JSON_DATA,'$[156]'), --0 döndü çünkü 158 elementi yok.
JSON_PATH_EXISTS(JSON_DATA,'$[0].age'),--1 döndü age sütunu var
JSON_PATH_EXISTS(JSON_DATA,'$[0].age[0]'),--0 döndü çünkü age bir dizi değil
0.cı indisi yok
JSON_PATH_EXISTS(JSON_DATA,'$[0].friends[2].name')--1 döndü çünkü friends dizisinin 3
elementi var bu elementler json formatında yani name alanınına gidilebilir.
from dbo.json_test
JSON_QUERY
Bir JSON içerisinden bir obje veya dizi döndürmemizi
sağlar.Burada önemli nokta son değeri bu fonksiyon ile döndüremezsiniz sadece
obje veya dizi döndürürsünüz bunu unutmayın!
select
JSON_QUERY(JSON_DATA,'$[0]'),
JSON_QUERY(JSON_DATA,'$[0].age'),
JSON_QUERY(JSON_DATA,'$[0].friends[2]') ,
JSON_QUERY(JSON_DATA,'$[0].friends')
from dbo.json_test
Bakın 2.sütun değer döndürüyor dizi veya obje gelmediği için
NULL geliyor fakat diğerleri hepsi dolu geldi.
JSON_VALUE
Ve aslında hepimizin beklediği fonksiyon belkide çünkü artık
JSON’dan klasik değer döndüğünü göreceğiz.
Bakın yukarıdaki aynı sorguyu sadece JSON_VALUE ile yazdım
ve sonuç tam tersi
select
JSON_VALUE(JSON_DATA,'$[0]'),
JSON_VALUE(JSON_DATA,'$[0].age'),
JSON_VALUE(JSON_DATA,'$[0].friends[2]') ,
JSON_VALUE(JSON_DATA,'$[0].friends')
from dbo.json_test
Çünkü bu fonksiyon sadece scalar dediğimiz klasik data
tiğpindekileri döner.
select
JSON_VALUE(JSON_DATA,'$[0].index'),
JSON_VALUE(JSON_DATA,'$[0].age'),
JSON_VALUE(JSON_DATA,'$[0].friends[2].name') ,
JSON_VALUE(JSON_DATA,'$[0].tags[0]')
from dbo.json_test
Hepsini scalar değer verdiğimizde döndürdüğünü görüyoruz.
JSON_ARRAYAGG
JSON_OBJECTAGG
Azure SQL’de kullanılanılabilir sadece
OPENJSON genelde Azure üzrinde kullanılır on-premde görmedim
hiç.
Umarım faydalı olmuştur 😊
