[SQL SERVER]SQL2016-JSON(2)

2016-11-26

1922
0
SQL Server 2016
2020-11-29

上一篇示範了如何建立JSON物件，這一篇來看看SQL Server在JSON資料結構處理這一部分，

提供了多少實用functions呢?

ISJSON:驗證是否為正確JSON內容

declare @myjson nvarchar(4000) = N'[{
 "CustomerID" : 1,
 "UserName": "RiCo",
 "Status": true,
 "ExpectedDeliveryDate": "2013-01-01T12:00:00"
 }]';
 select ISJSON(@myjson),  -- returns 1
        ISJSON(N'foo') -- returns 0

自行組JSON字串一定要仔細，因為只返回0和1，

這表示如果字串內容有誤，SQL SERVER並不會明確告訴錯在那裏。

JSON_VALUE:從JSON字串中擷取指定數值

declare @myjson nvarchar(4000) = N'{
 "UserID" : 1,
 "UserName" : "RiCo",
 "Orders": [ 
   { "ExpectedDeliveryDate":"2013-01-01T12:00:00", "Status":true, "CustomerID":1 },
   { "ExpectedDeliveryDate":"2014-01-01T12:00:00", "Status":false,"CustomerID":2, "Options":
     [{ "DF":true,"EX":false }] }
  ]
 }';
 select ISJSON(@myjson),
  UserID = JSON_VALUE(@myjson, '$.UserID'),
  UserName = JSON_VALUE(@myjson, '$.UserName'),
  ExpectedDeliveryDate1 = JSON_VALUE(@myjson, '$.Orders[0].ExpectedDeliveryDate'),
  CustomerID1 = JSON_VALUE(@myjson, '$.Orders[0].CustomerID'),
  Status2 = JSON_VALUE(@myjson, '$.Orders[1].Status'),
  Options_DF = JSON_VALUE(@myjson, '$.Orders[1].Options[0].DF'),
  Options_EX = JSON_VALUE(@myjson, '$.Orders[1].Options[0].EX');

OPENJSON:將輸入的JSON字串，轉換為關聯表格結果

declare @myjson nvarchar(4000) = N'{
 "UserID" : 1,
 "UserName" : "RiCo",
 "Orders": [ 
   { "ExpectedDeliveryDate":"2013-01-01T12:00:00", "Status":true, "CustomerID":1 },
   { "ExpectedDeliveryDate":"2014-01-01T12:00:00", "Status":false,"CustomerID":2, "Options":
     [{ "DF":true,"EX":false }] }
  ]
 }';
select * from OPENJSON(@myjson);

declare @myjson2 nvarchar(4000) = N'{
 "UserID" : 1,
 "UserName" : "RiCo",
 "Phone" : "0939123456" ,
 "Age" : 35,
 "BirthDay" : "1901-01-01"
 }';
select * from OPENJSON(@myjson2);

也可透過with自訂資料表結構。

declare @myjson2 nvarchar(4000) = N'{
 "UserID" : 1,
 "UserName" : "RiCo",
 "Phone" : "0939123456" ,
 "Age" : 35,
 "BirthDay" : "1901-01-01"
 }';
select * from OPENJSON(@myjson2)
with 
 (
   UserID int, 
   UserName nvarchar(20),
   Phone char(10),
   Age tinyint,
   BirthDay date,
   NewDate date '$.BirthDay' --對應原本 BirthDay 欄位 
 );

JSON_MODIFY:更新JSON字串內容

create table myorders
(
OrderID int not null,
Attributes nvarchar(4000),
constraint PK_myorders primary key(OrderID),
constraint IsValidJSON check(isjson(Attributes)=1)
)

insert myorders
select 1,N'{ "CustomerID":1, "Name":"RiCo","ExpectedDeliveryDate":"2013-01-02","options": [{"Dev":"VS2013"}] }'
union all
select 2,N'{ "CustomerID":2, "Name":"Sherry","ExpectedDeliveryDate":"2014-01-02","options": [{"Dev":"VS2015"}] }'

-- Update name  
update myorders set Attributes=JSON_MODIFY(Attributes,'$.Name','NewRiCo')
where OrderID=1
select result.* from (
select Attributes from  myorders where OrderID=1 ) base
cross apply openjson(base.Attributes) result

-- Insert phone 
update myorders set Attributes=JSON_MODIFY(Attributes,'$.Phone','0939123456')
where OrderID=1 
select result.* from (
select Attributes from  myorders where OrderID=1 ) base
cross apply openjson(base.Attributes) result

-- Delete phone
update myorders set Attributes=JSON_MODIFY(Attributes,'$.Phone',null)
where OrderID=1   
select result.* from (
select Attributes from  myorders where OrderID=1 ) base
cross apply openjson(base.Attributes) result

-- Add Dev 
update myorders set Attributes=JSON_MODIFY(Attributes,'append $.options[0].DEV','Azure')
where OrderID=1 
select result.* from (
select Attributes from  myorders where OrderID=1 ) base
cross apply openjson(base.Attributes) result

JSON_QUERY:針對大型JSON字串，可以透過該function擷取JSON物件中陣列

select OrderID, options = JSON_QUERY(Attributes, '$.options')
from myorders;

結果還是有包含JSON，我們可以透過openjson來消除。

select base.OrderID, y.[key], y.[value]
from myorders as base
cross apply OPENJSON(JSON_QUERY(Attributes, '$.options')) AS x
cross apply OPENJSON(x.[value], '$') AS y;