2-ma’ruza. Ma’lumotlar bazasiga qo’yiladigan talablar. Rеja: Axborot tizimlari va ma'lumotlar bazasi

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"}
]
}