json در اسکيول سرور

ابتدا این مطلب را بخوانید: جیسون در sql server

ما در این آموزش json در sql server به موارد زیرمی پردازیم:

• اجرای کوئری بر روی Json Text

• تغییر JSON values

• تبدیل SQL Server data به JSON

• موارد مورد استفاده برای داده های JSON در SQL Server

• ترکیب داده های ارتباطی و JSON 

• نگهداری و index کردن JSON data (داده های json) در sql server

• بارگذاری JSON files به sql server

• وارد کردن JSON data به جداول sql server

• آنالیز JSON data در sql server

• برگرداندن داده ها از یک جدول SQL Server فرمت شده به عنوان JSON 

JSON یک فرمت داده متنی است که برای تبادل داده ها در برنامه های کاربردی وب و موبایل مدرن استفاده می شود. JSON همچنین برای ذخیره سازی داده های بدون ساختار در فایل های ورودی یا پایگاه داده های NoSQL مانند Microsoft Azure Cosmos DB استفاده می شود. بسیاری از وب سرویسهای  REST نتایجی را که به عنوان متن JSON فرمت شده اند یا داده هایی را که به عنوان JSON قالب بندی شده اند، به نمایش می گذارند. به عنوان مثال، بیشتر خدمات Azure، مانند Azure Search، Azure Storage و Azure Cosmos DB، دارای نقطه پایان REST هستند که JSON را مصرف می کنند. JSON همچنین فرمت اصلی برای تبادل اطلاعات بین صفحات وب و سرورهای وب با استفاده از تماس AJAX است.

آموزش جیسون در sql server

ابتدا این مطلب را بخوانید: جیسون در sql server

ما در این آموزش json در sql server به موارد زیرمی پردازیم:

• اجرای کوئری بر روی Json Text

• تغییر JSON values

• تبدیل SQL Server data به JSON

• موارد مورد استفاده برای داده های JSON در SQL Server

• ترکیب داده های ارتباطی و JSON 

• نگهداری و index کردن JSON data (داده های json) در sql server

• بارگذاری JSON files به sql server

• وارد کردن JSON data به جداول sql server

• آنالیز JSON data در sql server

• برگرداندن داده ها از یک جدول SQL Server فرمت شده به عنوان JSON 

JSON یک فرمت داده متنی است که برای تبادل داده ها در برنامه های کاربردی وب و موبایل مدرن استفاده می شود. JSON همچنین برای ذخیره سازی داده های بدون ساختار در فایل های ورودی یا پایگاه داده های NoSQL مانند Microsoft Azure Cosmos DB استفاده می شود. بسیاری از وب سرویسهای  REST نتایجی را که به عنوان متن JSON فرمت شده اند یا داده هایی را که به عنوان JSON قالب بندی شده اند، به نمایش می گذارند. به عنوان مثال، بیشتر خدمات Azure، مانند Azure Search، Azure Storage و Azure Cosmos DB، دارای نقطه پایان REST هستند که JSON را مصرف می کنند. JSON همچنین فرمت اصلی برای تبادل اطلاعات بین صفحات وب و سرورهای وب با استفاده از تماس AJAX است.
توابع JSON در SQL Server شما را قادر به ترکیب NoSQL و مفاهیم ارتباطی در همان پایگاه داده می کند. حالا شما می توانید ستون های ارتباطی کلاسی را با ستون های حاوی اسناد فرمت شده به عنوان متن JSON در یک جدول مشابه، تجزیه و وارد کردن اسناد JSON در ساختارهای رابطه ای یا ترکیب داده های مرتبط با متن JSON ترکیب کنید.
یک نمونه از کدهای json:

[{
	"name": "John",
	"skills": ["SQL", "C#", "Azure"]
}, {
	"name": "Jane",
	"surname": "Doe"
}]

با استفاده از توابع و اپراتورهای ساخته شده در SQL سرور، می توانید موارد زیر را با متن JSON انجام دهید:

• تجزیه متن JSON و خواندن یا اصلاح مقادیر

• تبدیل آرایه های JSON objects به فرمت جدول

• اجرای هر کوئری Transact-SQL تبدیل شده به  JSON objects

• قالب بندی نتایج جستجوی کوئری Transact-SQLبه فرمت JSON

اجرای کوئری بر روی متن جیسون

• اجرای کوئری بر روی Json Text

اگر  Json Text در جداول دیتابیس ذخیره شده است، می توانید مقادیر موجود در متن JSON را با استفاده از توابع ساخته شده در زیر بخوانید و یا اصلاح کنید:

• (ISJSON (Transact-SQL تست اینکه آیا یک رشته حاوی JSON معتبر است.

• (JSON_VALUE (Transact-SQL مقدار scalar (مقادیر عددی) را از یک رشته JSON استخراج می کند.

• (JSON_QUERY (Transact-SQL یک شی یا یک آرایه را از یک رشته JSON استخراج می کند.

• (JSON_MODIFY (Transact-SQL یک مقدار را در یک رشته JSON تغییر می دهد.

در مثال زیر، کوئری از هر دو داده رابطه ای و JSON (ذخیره شده در ستون به نام jsonCol) از یک جدول استفاده می شود:

SELECT Name,Surname,
 JSON_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,
 JSON_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '
  +JSON_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,
 JSON_QUERY(jsonCol,'$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol)>0
 AND JSON_VALUE(jsonCol,'$.info.address.Town')='Belgrade'
 AND Status='Active'
ORDER BY JSON_VALUE(jsonCol,'$.info.address.PostCode')

برنامه ها و ابزارها بین مقادیر scalar و Value که از ستون های جدول و از ستون های JSON گرفته شده اند تفاوت نمی بینند. شما می توانید از مقادیر متن JSON در هر بخشی از یک کوئری (از جمله WHERE، ORDER BY، یا GROUP BY، window aggregates، و غیره) استفاده کنید. توابع JSON از دستور زبان جاوا اسکریپت برای ارجاع مقادیر درون متن JSON استفاده می کنند.

تغییر JSON values

• تغییر مقادیر JSON

اگر شما بخشی از متن JSON را تغییر دهید، می توانید از تابع (JSON_MODIFY (Transact-SQL برای به روز رسانی مقدار یک property در یک رشته JSON و بازگرداندن رشته JSON به روز شده استفاده کنید. مثال زیر مقدار یک property را در متغیری که حاوی JSON است به روز می کند:

DECLARE @json NVARCHAR(MAX);
SET @json = '{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json,'$.info.address[1].town','London');
SELECT modifiedJson = @json;

نتیجه:

{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}

 

برای اجرای کوئری بر روی json شما به کوئری سفارشی خاصی نیاز ندارید. برای اجرای کوئری بر روی JSON Data، می توانید از استاندارد T-SQL استفاده کنید. اگر شما باید یک کوئری یا گزارش در مورد داده های JSON ایجاد کنید، می توانید به راحتی با فراخوانی تابع OPENJSON rowset داده های JSON را به ردیف ها و ستون ها تبدیل کنید.
مثال زیر OPENJSON را فراخوانی می کند و آرایه ای از اشیاء را که در متغیر json@ ذخیره می شود را به یک مجموعه ی رشته ای تبدیل می کند که می تواند با یک عبارت SQL SELECT کوئری شود:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  
   
SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')

نتیجه به صورت زیر خواهدبود:

OPENJSON آرایه ای از اشیاء JSON را به یک جدول تبدیل می کند که در آن هر شی به عنوان یک ردیف نمایش داده می شود و مقادیر Key / Value را به عنوان سلول برگردانده می شود. خروجی قوانین زیر را دنبال می کند:

• OPENJSON مقادیر JSON را به نوعی که در بخش WITH مشخص شده است، تبدیل می کند.
• OPENJSON می تواند هر دو مقادیر Key / Value اشیاء سازماندهی شده سلسله مراتبی را در دست بگیرد.
• شما مجبور نیستید همه فیلدهای موجود در متن JSON را بازگردانید.
• اگر مقادیر JSON وجود نداشته باشد، OPENJSON مقدار NULL را باز می کند.
• شما می توانید به صورت اختیاری یک مسیر را پس از مشخص کردن نوع برای اشاره به یک ویژگی توزیع شده یا یک property با یک نام متفاوت مشخص کنید.
• پیشوند strict در مسیر مشخص می کند که مقادیر برای خواص مشخص شده باید در متن JSON وجود داشته باشند.

اسناد JSON ممکن است دارای زیرمجموعه ها و داده های سلسله مراتبی باشد که نمی توانند مستقیما به ستون های استاندارد ارتباط داده شوند. در این مورد، شما می توانید سلسله مراتب JSON را با اتصال به موجودیت parent با زیر آرایه ها (sub-arrays) مسطح کنید.
در مثال زیر، شیء دوم در آرایه دارای زیر آرایه است که نشان دهنده مهارت های فردی است. هر Sub-object را می توان با استفاده از تابع OPENJSON فراخوانی کرد:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"] }, "dob": "2005-11-04T12:00:00" }  
 ]'  
   
SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob',
	skills nvarchar(max) '$.info.skills' as json) 
	outer apply openjson( skills ) 
                     with ( skill nvarchar(8) '$' )

نتیجه کوئری بالا:

تبدیل SQL Server data به JSON

توجه داشته باشید که: تبدیل Data Azure SQL Data Warehouse به JSON یا exporting JSON پشتیبانی نمی شود.
می توانید SQL Server data یا نتایج نمایش داده شده SQL را با اضافه کردن عبارت FOR JSON به یک عبارت SELECT به عنوان JSON قالب بندی کنید. از JSON استفاده کنید تا فرمت خروجی JSON از برنامه های مشتری خود را به SQL Server منتقل کنید.
مثال زیر از حالت PATH با عبارت FOR JSON استفاده می کند:

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  
FOR JSON PATH

FOR JSON نتایج SQL را به عنوان متن JSON پردازش می کند که می تواند برای هر برنامه ای که JSON را درک کند، ارائه شود. گزینه PATH با استفاده از نام مستعار جدا شده از نقطه در بخش SELECT برای لایه اشیاء در نتایج کوئری استفاده می کند.
نتیجه:

[{
	"id": 2,
	"info": {
		"name": "John",
		"surname": "Smith"
	},
	"age": 25
}, {
	"id": 5,
	"info": {
		"name": "Jane",
		"surname": "Smith"
	},
	"dob": "2005-11-04T12:00:00"
}] 

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

ساده سازی مدل داده های پیچیده | Simplify complex data models
مدل داده های خود را با جیسون در چند جدول جداگانه در نظر بگیرید.

اطلاعات خرده فروشی و تجارت الکترونیک را ذخیره کنید | Store retail and e-commerce data
اطلاعات مربوط به محصولات را با طیف گسترده ای از ویژگی های متغیر در یک مدل انحرافی برای انعطاف پذیری ذخیره کنید.

پردازش log و داده های تله متری | Process log and telemetry data

بارگذاری، کوئری و تجزیه و تحلیل داده های ورود به سیستم ذخیره شده به عنوان فایل های JSON با تمام قدرت زبان Transact-SQL.

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

ساده سازی توسعه REST API 
داده های ارتباطی را از پایگاه داده خود به راحتی به فرمت JSON که توسط API های REST که از وب سایت شما پشتیبانی می کنند را تبدیل کنید.

ترکیب داده های ارتباطی و JSON 

• Combine relational and JSON data

SQL Server یک مدل ترکیبی برای ذخیره و پردازش داده های رابطه ای و JSON را با استفاده از زبان استاندارد Transact-SQL فراهم می کند. شما می توانید مجموعه ای از اسناد JSON خود را در جداول سازماندهی کنید، روابط بین آنها را ایجاد کنید، ستون های scalar ذخیره شده در جداول با هر دو مقادیر Key / Value انعطاف پذیر ذخیره شده در ستون های JSON را ترکیب کرده و مقادیر scalar و JSON را در یک یا چند جدول با استفاده از Transact-SQL استفاده کنید.
متن JSON در ستون varchar یا nvarchar ذخیره می شود و به صورت متن ساده نمایش داده می شود. هر ویژگی یا کامپوننت SQL Server که از متن پشتیبانی می کند از JSON پشتیبانی می کند، بنابراین تقریبا هیچ محدودیتی برای تعامل بین JSON و سایر ویژگی های SQL Server وجود ندارد. شما می توانید JSON را در جداول In-memory یا Temporal ذخیره کنید، مقادیر ردیف Security Level را روی متن JSON و غیره اعمال کنید.

Store and index JSON data in SQL Server

• نگهداری و index کردن JSON data (داده های json) در sql server

JSON فرمت متنی است، بنابراین اسناد JSON را می توان در ستون های NVARCHAR در پایگاه داده SQL ذخیره کرد. از آنجا که نوع NVARCHAR در تمام زیر سیستم های SQL Server پشتیبانی می شود می توانید اسناد JSON را در جداول با شاخص های CLUSTERED COLUMNSTORE، جداول حافظه بهینه سازی شده یا فایل های خارجی که می توانند با استفاده از OPENROWSET یا PolyBase خوانده شوند، قرار دهید.

بارگذاری JSON files به sql server

شما می توانید اطلاعاتی را که در فایل ها ذخیره شده اند را به عنوان JSON استاندارد یا line-delimited JSON فرمت کنید. SQL Server می تواند محتویات فایل های JSON را وارد کند، آن را با استفاده از توابع OPENJSON یا JSON_VALUE تجزیه می کند و آن را به جداول بارگذاری می کند.
اگر اسناد JSON شما در فایل های محلی، در درایوهای شبکه مشترک یا در مکان های فایل Azure که می توانند توسط SQL Server قابل دسترسی باشند، می توانید از bulk برای وارد کردن و بارگیری اطلاعات JSON خود در SQL Server استفاده کنید.

Bulk در sql server

bulk یک فایل داده را به یک جدول پایگاه داده یا یک فرمت مشخص شده توسط کاربر در SQL Server وارد می کند.

اگر فایلهای JSON delimited شما در ذخیره سازی Azure Blob یا سیستم فایل Hadoop ذخیره می شوند، می توانید از PolyBase برای بارگذاری متن JSON استفاده کنید، آن را در کد Transact-SQL تجزیه کنید و آن را در جداول بارگذاری کنید.

وارد کردن JSON data به جداول sql server

اگر شما باید داده JSON را از یک سرویس خارجی به SQL Server بارگیری کنید، می توانید از OPENJSON برای وارد کردن داده ها به SQL Server به جای تجزیه داده ها در لایه کاربرد استفاده کنید.

DECLARE @jsonVariable NVARCHAR(MAX)

SET @jsonVariable = N'[  
        {  
          "Order": {  
            "Number":"SO43659",  
            "Date":"2011-05-31T00:00:00"  
          },  
          "AccountNumber":"AW29825",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":1  
          }  
        },  
        {  
          "Order": {  
            "Number":"SO43661",  
            "Date":"2011-06-01T00:00:00"  
          },  
          "AccountNumber":"AW73565",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":3  
          }  
       }  
  ]'
  
INSERT INTO SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData;

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

آنالیز JSON data در sql server

اگر شما باید داده های JSON را برای اهداف گزارش شده فیلتر یا جمع کنید، می توانید OPENJSON را برای تبدیل JSON به فرمت رابطه ای استفاده کنید. سپس می توانید از استاندارد Transact-SQL و توابع داخلی برای تهیه گزارش استفاده کنید.

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
FROM   SalesOrderRecord AS Tab  
          CROSS APPLY  
     OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData  
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified

برگرداندن داده ها از یک جدول SQL Server فرمت شده به عنوان JSON 

اگر یک سرویس وب دارید که داده ها را از لایه پایگاه داده می گیرد و آن را در قالب JSON می گیرد یا اگر شما دارای چارچوب های جاوا اسکریپت یا کتابخانه ای هستید که داده ها را به صورت JSON قالب بندی می کنند، می توانید خروجی JSON را مستقیما در SQL Query قالب بندی کنید. به جای نوشتن کد یا داشتن یک کتابخانه برای تبدیل نتایج کوئری و سپس دسته بندی اشیاء به فرمت JSON، شما می توانید از FOR JSON برای انتقال فرمت JSON به SQL Server استفاده کنید.
به عنوان مثال، شما ممکن است بخواهید خروجی JSON را مطابق با مشخصات OData تولید کنید. سرویس وب انتظار دارد درخواست و پاسخ در فرمت زیر باشد:

Request: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

Response: {"@odata.context":"https://services.odata.org/V4/Northwind/Northwind.svc

/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,

"ProductName":"Chai"}

این URL OData نشان دهنده یک درخواست برای ستون ProductID و ProductName برای محصول با شناسه است. شما می توانید از FOR JSON برای فرمت خروجی  در SQL سرور استفاده کنید.

SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity'
 AS '@odata.context',   
 ProductID, Name as ProductName   
FROM Production.Product  
WHERE ProductID = 1  
FOR JSON AUTO

خروجی این پرس و جو متن JSON است که به طور کامل با مشخصات OData سازگار است. قالب بندی و escaping توسط SQL Server اداره می شود. SQL Server همچنین می تواند نتایج پرس و جو را در هر فرمت، مانند OData JSON یا GeoJSON، قالب بندی کند.
به این شکل شما می توانید از json در sql server استفاده کنید. امیدواریم از این آموزش لذت کافی را برده باشید.

"تهیه شده در مجموعه ABLY"