استفاده از JSON در SQL

بیشتر اپلیکیشن های تحت وب امکان خروجی JSON را فراهم میکنند. از این خروجی ها میتواند برای تحلیل، گزارشات و .. استفاده نمود. راه های زیادی برای تبدیل اطلاعاتی در قالب JSON به فرمت هایی مانند DataTable در مانهای مختلف مانند سی شارپ وجود داره. اما کاری که ما میخواهیم در مقاله انجام دهیم این است که بتواین دادهای رشته ای در قالب JSON را در SQL به جداولی تبدیل کنیم که براحتی بتوان انواع کوئری های مورد نیاز را بر روی آن اعمال کنیم.

در این مثال فایل داده های ما شامل جزییات سفارشات می باشد. این اطلاعات شامل OrderID,CustometID,OrderStatus می باشد.

اولین کاری که باید انجام دهیم این است که منبع داده هایی که قرار است در قالب فایل JSON باشد به نحوی مشخص کنیم.

Openrowset تابعی از نوع Table Value است که این کار را برای ما انجام میدهد. این تابع یک table با یک ستون به نام BulkColumn بر میگرداند که شامل محتویات فایل JSON میباشد

دستور زیر فایل JSON را از مسیر E:\Temp\Data\Orders.JSON را لود میکند

SELECT * 
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j

البته پیشنهاد میشود که حتما محتوای فایل JSON را با استفاده از تابع ISJSON  بررسی کنید تا از JSON بودن آن اطمینان حاصل شود!

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j

Select @JSON

If (ISJSON(@JSON)=1)
Print 'Valid JSON'

حالا میتوانیم با دستور OpenJSON میتونیم محتوای فایل JSON را به بصورت نتایج رابطه ای Result Relational تبدیل کنیم. تابع OpenJSON به دو صورت می تواند اطلاعات را برگرداند.

حالت اول که حالت پپیشفرض نیز میباشدبصورت کلید/مقدار عمل میکند و به ازای هر المان در فایل JSON یه ستون برمیگرداند. در حالت دوم میتوان صراحتا تعیین کردن کدام المان ها را به عنوان ستون در نظر بگیرد و در خروجی نمایش دهد.

در زیر نمونه ای از حالت پیشفرض استفاده از تابع OpenJSON را میبینیم

حالا اگر بخواهیم فیلدها را صراحتا بیان کنیم و نوع انها را مشخص نماییم بصورت زیر عمل میکنیم

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON) 
WITH(OrderID int,CustomerID int,OrderStatus char(1)) as Orders

مثال هایی که تا الان ذکر کردیم ساده بودند. حالا بیاید تصور کنیم که فایلی که داریم بصورت تودرتو باشد به این معنی که برخی از المان ها JSON خود دارای ساختاری و الملن های دیگر هستند. برای نمونه این فایل JSON  را دانلود کنید و با استفاده از دستوراتی که یاد گرفتیم آنرا در SQL  لود میکنیم

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON)

میبینیم که دو المان links  و data که در بالاترین صطح هستند لود شده اند. شما میتواند با پیشوند $ به اطدلاعات سطوح پایینتر فایل نیز دسترسی داشته باشد

برای مثال با دستور زیر شما فقط نود links  را بازیابی میکنید

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.links') 
Go

و با دستور زیر فقط داده ها نود Data

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.data')
Go

اگر سعی کنیم تا محتوای نود Data را با دستور زیر بازیابی کنیم میبینیم که ستون های لینک و attribute هر دو مقدار NULL گرفته اند

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.data') 
With (type varchar(50),id int, Link varchar(4000),attributes varchar(4000)) as Dataset
Go

یکباری دیگه به ساختار فایل JSON مثالمون نگاه میکنیم

المان های links  و attribute هر دو دارای زیرمجموعه می باشند پس به تنهایی مقداری ندارند بلکه باید مقادری زیرمجموعه انها نمایش داده شود. بنابراین از کارکتر جادویی $  برای دسترسی به سطوح پایینتر یکبار دیگه استفاده میکینیم

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.data') 
With (type varchar(50),id int, 
Link varchar(4000) '$.links.self',
action varchar(4000) '$.attributes.action', 
background varchar(4000) '$.attributes.background', 
PetitionStatus varchar(50) '$.attributes.state', 
signature_count int '$.attributes.signature_count', 
CreatedDate varchar(50) '$.attributes.created_at'
) as Dataset
Go