MSSQL ile JSON Verileri Sorgulama

SQL Server 2016 ile birlikte gelen OPENJSON fonksiyonu ile JSON formatındaki verileri sorgulama imkânı sunmaktadır. OPENJSON fonksiyonu bize 3 kolonlu bir tablo döndürmektedir. Bunlar key,value ve type formatındadır. Yazımda sizlere kısace OPENJSON fonksiyonunun kullanımı ve JSON api ile kullanımını anlatacağım

Ufak bir örnekle gösterecek olursak aşağıdaki gibi Key değerlerine göre karşılık gelen Value değerlerinin parçalanmış halini görüyoruz

Resim 1

DECLARE @JSONBODY NVARCHAR(MAX) ='
{
  "NULL": null,
  "STRING": "safayunus",
  "NUMBER": 100,
  "boolean": true,
  "array": [
    "msg 1",
    "msg 2",
    "msg 3"
  ],
  "object": {
    "A": 1,
    "B": 0,
    "C": 1
  }
}'


SELECT *
    FROM OPENJSON(@JSONBODY)

Burada type kolonu value kolonundaki değerin tipini belirtmektedir

0	Null
1	String
2	Number
3	True/false
4	Array
5	Object

OPENJSON fonksiyonu ile birlikte JSON verimizden istediğimiz value/değerleri alabilmekteyiz. Bunun için WITH yardımıyla gerçekleştirebiliyoruz.

SELECT *
    FROM OPENJSON(@JSONBODY)
	WITH (   
		    col1     INT	    '$.NUMBER'
		   ,col2     VARCHAR(50)    '$.STRING'  
		   ,col3     VARCHAR(50)    '$.object.A'  
		)

Resim 2

JSON verilerle işlem yaparken başka bir fonksiyon ise ISJSON fonksiyonu, bu fonksiyon elimizdeki JSON verisinin uygun formatta olup olmadığını kontrol etmemize yaramaktadır. Parametre olarak JSON verisini almaktadır sonuç olarak 1/0 döndürmektedir.

SELECT ISJSON(@JSONBODY)

Resim 3

Yukardaki örnekte görüldüğü gibi sol taraftaki JSON formatını manipüle edince bize uygun bir format olmadığını gösterdi.

Şimdiye kadar SQL Server üzerinde JSON verileri nasıl kullanabileceğimizi örnekler anlatmaya çalıştım. Asıl konumuza gelecek olursak SQL Server ile JSON API ile verilerin nasıl işleyeceğimizi anlatmaya çalışacağım.

Öncelikle SQL Server ile API iletişim kurdurmamız için OLE Automation Stored Procedurelerine ihtiyacımız bulunuyor.Varsayılan olarak kapalı gelen bu OLE SP lerini aşağıdaki komutlarla aktif hale getiriyoruz

Resim 4

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
GO

Örneğimizde döviz kurları üzerine bir çalışma yapacağız. Döviz kurlarını API aracılığıyla alıp tabloya yazacak bir yapı gerçekleştireceğiz.

Öncelikle verileri kaydedeceğimiz tabloyu oluşturalım.

CREATE TABLE [dbo].[DAILY_CURRENY](
	[DATE_] [date] NOT NULL,
	[EUR/TRY] [float] NOT NULL,
	[EUR/USD] [float] NOT NULL,
	[EUR/AUD] [float] NOT NULL,
	[EUR/CAD] [float] NOT NULL,
	[EUR/PLN] [float] NOT NULL
)

Sıra geldi OLE Automation SP leri aracılığıyla API den verileri çekip istediğimiz verileri almaya , öncelikle sp_OACreate  ile bir WinHTTP objesi oluşturuyoruz. sp_OAMethod store prosederü ile  oluşturduğumuz WinHTTP objesi ile API ye istek atmasını sağlıyoruz. Son olarak sp_OADestroy ile oluşturduğumuz objeleri siliyoruz

Declare @WinHttp as Int;
Declare @JSONBody as Varchar(8000);

Exec sp_OACreate 'WinHttp.WinHttpRequest.5.1', @WinHttp OUT;
Exec sp_OAMethod @WinHttp
               , 'open'
               , NULL
               , 'get'
               , 'http://data.fixer.io/api/latest?access_key=b5ed352efad06ab9ca16f0a0b8a34bd8&symbols=TRY,USD,AUD,CAD,PLN&format=1'
               , 'false'
Exec sp_OAMethod @WinHttp, 'send'
Exec sp_OAMethod @WinHttp, 'responseText', @JSONBody OUTPUT
Exec sp_OADestroy @WinHttp

IF ISJSON(@JSONBody) = 1
BEGIN

    SELECT * 
    FROM
        OpenJson(@JSONBody)
        WITH
        (
            DATE_ date '$.date'
          ,[EUR/TRY] float '$.rates.TRY'
          ,[EUR/USD] float '$.rates.USD'
          ,[EUR/AUD] float '$.rates.AUD'
          ,[EUR/CAD] float '$.rates.CAD'
          ,[EUR/PLN] float '$.rates.PLN'
        )
END
ELSE
BEGIN
SELECT 'JSON formatında hata mevcut'
END

Resim 5

Yazdıklarımızı bir stored prosedüre çevirerek daha kolay bir kullanıma ve tabloya gelen verileri insert edecek hale getiriyoruz.

Başta boş olan tablomuz DailyCurrency  stored prosedürünün çalışmasıyla kayıt attığını görüyoruz. İstersek bir SQL Job ile bu işlemi periyodik hale getirerek günlük kur bilgilerini otomatik olarak veri tabanımıza kayıt edebiliriz.

Bu yazımda sizlere SQL Server üzerinde JSON verilerin nasıl işleneceği ve örnek bir JSON API den alınan verilerin bir tabloya nasıl kayıt edilebileceğini anlatmaya çalıştım.Sizler de farklı API ler aracılığıyla bu işlemi gerçekleştirebilirsiniz.