14-ma’ruza. Xml va json ma’lumotlar bazasi

SELECT CustomerName, PhoneNumber, FaxNumber
FROM Sales.Customers
FOR JSON PATH

FOR JSON PATH so'zi so'rov natijalarini JSON matni sifatida formatlaydi. Ustun nomlari kalit sifatida ishlatiladi va uyali qiymatlar JSON qiymatlari sifatida yaratiladi.

[
{"CustomerName":"Eric Torres","PhoneNumber":"[phone]","FaxNumber":"[phone]"},

{"CustomerName":"Cosmina Vlad","PhoneNumber":"[phone]","FaxNumber":"[phone]"},
{"CustomerName":"Bala Dixit","PhoneNumber":"[phone]","FaxNumber":"[phone]"}
]

Natija to'plami JSON massivi sifatida formatlanadi, har bir satr alohida JSON ob'ekti sifatida formatlanadi.

PATH ustunlar taxalluslarida nuqta belgisi yordamida JSON natijalarining chiqish formatini sozlashingiz mumkinligini bildiradi. Quyidagi so'rov JSON formatidagi CustomerName kalit nomini o'zgartiradi va telefon va faks raqamlarini Cantact ichki ob'ektiga qo'yadi.
SELECT CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
FROM Sales.Customers
WHERE CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Ushbu so'rov natijasi quyidagicha.

{
"Name":"Nada Jovanovic",

"Contact":{
"Phone":"[phone]",
"Fax":"[phone]"
}
}

Ushbu misolda biz massiv o'rniga bitta JSON obyektini WITHOUT_ARRAY_WRAPPER parametrini ko'rsatib berdik. Ushbu parametr so'rov natijasi bitta obyekt ekanligini bilganingizda foydalanish mumkin.

FOR JSON operatorining asosiy qiymati shundan iboratki, u JSON obyektlari yoki JSON massivi sifatida joylashtirilgan murakkab iyerarxik ma'lumotlarni ma'lumotlar bazasidan qaytarishga imkon beradi. Quyidagi misol Customerga tegishli bo'lgan Orders jadvalidan qatorlarni Orders obyektlarining joylashtirilgan qatori sifatida qanday qo'shish kerakligini ko'rsatmoqda.

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,

Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
join Sales.Orders Orders
on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

Mijoz ma'lumotlarini olish uchun alohida so'rovlarni yuborish va keyin mos keladigan Customer ro'yxatini olish o'rniga, quyida keltirilgan namunada ko'rsatilganidek, bitta so'rov bilan kerakli barcha ma'lumotlarni olishingiz mumkin.

{
"Name":"Nada Jovanovic",

"Phone":"[phone]",
"Fax":"[phone]",
"Orders":[
{"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
{"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
{"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
]
}