Dữ liệu JSON trong SQL Server
Dữ liêu JSON là một định dạng dữ liệu văn bản sử dụng để trao đổi dữ liệu trong ứng dụng WEB/Mobile hiện đại.
JSON cũng có thể sử dụng để lưu trữ dữ liệu không có cấu trúc trong log files hoặc database NoSQL như là Microsoft Azure Cosmos DB.
Nhiều nghiệp vụ REST sẽ trả về kết quả với định dạng JSON text hoặc nhận dữ liệu vào như một định dạng JSON.
Thông qua các hàm có sẵn của SQL Server, chúng ta có thể:
- Phân tích văn bản JSON, chỉnh sửa giá trị.
- Chyển đổi mảng trong JSON objects thành định dạng bảng.
- Chạy T-SQL Query với đối tượng JSON đã convert.
- Định dạng kết quả của câu lệnh T-SQL trong JSON format.
Một số hàm built-in xử lý JSON:
- ISJSON: kiểm tra tính hợp lệ của chuỗi JSON.
- JSON_VALUE: xuất ra giá trị đơn từ chuỗi JSON.
- JSON_QUERY: xuất ra đối tượng hoặc mảng từ chuỗi JSON
- JSON_MODIFY: thay đổi giá trị chuỗi JSON
Mục lục
Sửa đổi giá trị JSON
Để chỉnh sửa thành phần của văn bản JSON, hàm JSON_MODIFY được sử dụng để cập nhật giá trị của thuộc tính trong chuỗi JSON và trả về chuỗi JSON được cập nhật.
Ví dụ:
DECLARE @json nvarchar(max);
set @json = '{"info" : {"address": [{"town": "Belgrade"}, {"town" : "Paris"}, {"town": "Marid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town' , 'London');
select modifiedJson = @json
OPENJSON để chuyển đổi JSON thành dữ liệu hàng cột
Ví dụ:
declare @json nvarchar(max);
set @json = N'[
{"id": 2, "info": {"name": "john","surname": "smith"},"age": 25},
{"id": 5, "info": {"name": "john","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 '$.age',
dateofBirth datetime '$.dob'
)
Xuất dữ liệu SQL Server thành JSON
Ví dụ:
use AdventureWorks2019;
SELECT top 5 BusinessEntityID, FirstName as "info.name" , LastName as "info.surname", ModifiedDate as dob from Person.Person
for json PATH
Bài tập
Bài tâp ôn luyện:
1. Tạo kiểu mytype từ kiểu varchar(40) not null.
2. Tạo cơ sở dữ liệu bookdb với các bảng sau:
Book:
- BookCode: int, khoá chính , not null
- BookTitle: mytype
- Author: mytype
- Edition: int
- BookPrice: money
- Copies: int
Member:
- MemberCode: int, khóa chính, not null
- Name: mytype
- Address: mytype
- PhoneNumber: varchar(10)
IssueDetails:
- BookCode: int, khóa ngoại trỏ tới BookCode của bảng Book
- MemberCode: int, khóa ngoại trỏ tới MemberCode của bảng Member
- IssueDate: datetime
- ReturnDate: datetime
3. Sửa đổi bảng:
- Thêm ràng buộc check: giá sách(BookPrice) phải lớn hơn 10 cho bảng Book
- Thêm cột IssueID kiểu uniqueidentifier cho bảng issueDetails
- Sửa đổi kiểu dữ liệu cho trường PhoneNumber thành kiểu varchar(12) của bảng Member
4. Chèn dữ liệu vào bảng:
Bảng Book:
Bảng Member:
Bảng IssueDetails:
- Dam Vinh Hung mượn quyển Guide To Advance Java ngày 10/9/2005, trả 10/20/2005
- My Le mượn quyển Java By Example ngày 1/1/2006 trả 1/5/2006
- Kim Tu Long mượn quyển EPC ngày 1/10/2006 trả 1/15/2006
- Tai Linh mượn quyển RDBMS ngày 1/20/2006 trả 1/25/2006
- Ung Hoang Phuc mượn quyển HTML ngày 2/1/2006 trả 2/5/2006
- My Linh mượn quyển CF ngày 3/1/2006 trả 3/25/2006
5. Thực hiện các thao tác cập nhật
- Cập nhật bảng Member thêm trường Gender(giới tính) kiểu bit.
- Cập nhật giới tính cho Dam Vinh Hung, Kim Tu Long, Tai Linh, Ung Hoang Phuc la 1 cho My Le, My Linh là 0.
6. Thực hiện các truy vấn sau đây:
a. Đưa ra thống kê có mấy nam (gender = 1), mấy nữ (gender = 0).
b. Đưa ra tổng số sách trong thư viện.
c. Đưa ra những quyển sách có chữ cái 'C'.
d. Đưa ra những độc giả sống ở TP HCM.
e. Đưa ra những quyển sách được mượn năm 2006.
f. Tạo Unique Index trên cột Book Title của bảng Book.
g. Tạo View chứa mã người mượn, tên người mượn và thông tin chi tiết của những cuốn sách đã mượn bao gồm mã sách, tên sách, tác giả, tái bản, giá, copy, với điều kiện giới tính người mượn là nam.
h. Tạo thủ tục lưu trữ để tìm kiếm sách theo tên sách.
i. Tạo trigger để nếu xóa một người mượn trong bảng Member thì cũng xóa những thông tin của người mượn đó trong bảng IssueDetails.
k. Tạo trigger để nếu cập nhật mã sách trong bảng Book thì cũng cập nhật mã sách tương ứng trong bảng IssueDetails.
