RiCo技術農場

NULL輸出

CreditNoteReason欄位含有NULL，使用for json輸出如下

SELECT TOP 5 'RiCo' as [UserName]
      ,[CustomerID]
      ,[BillToCustomerID]
      ,[OrderID]      
      ,[CreditNoteReason]    
  FROM [Sales].[Invoices]
  for json auto

可以看到預設會忽略NULL，如要包含NULL，必須使用INCLUDE_NULL_VALUES選項。

 

SELECT TOP 5 'RiCo' as [UserName]
      ,[CustomerID]
      ,[BillToCustomerID]
      ,[OrderID]      
      ,[CreditNoteReason]    
  FROM [Sales].[Invoices]
  for json auto,INCLUDE_NULL_VALUES

 

 

移除方括號

使用WITHOUT_ARRAY_WRAPPER選項進行移除

SELECT TOP 5 'RiCo' as [UserName]
      ,[CustomerID]
      ,[BillToCustomerID]
      ,[OrderID]      
      ,[CreditNoteReason]    
  FROM [Sales].[Invoices]
  for json auto,WITHOUT_ARRAY_WRAPPER

 

特殊字元

下圖資料表對寫過.NET的開發人員應該不陌生

SELECT  
  'VALUE\    /  
  "' as [KEY\/"],  
  CHAR(0) as '0',  
  CHAR(1) as '1',  
  CHAR(31) as '31'  
FOR JSON PATH

 

提高JSON_VALUE搜尋效能

現在版本還沒有JSON索引(類似XML索引)，但我猜以後應該會推出純JSON索引，

以前沒有XML索引時，我會搭配計算資料行來處理，現在這方法當然也可以用在JSON。

ReturnedDeliveryData欄位儲存JSON格式內容，假設我們要搜尋ReceivedBy="Nils Kalnins"

 

原本查詢效能

SELECT'RiCo' as [UserName] ,JSON_VALUE(ReturnedDeliveryData, '$.ReceivedBy') 
  FROM [Invoices]
  WHERE JSON_VALUE(ReturnedDeliveryData, '$.ReceivedBy') = N'Nils Kalnins'; 

使用叢集索引掃描，整體成本:5.894、Logical Reads:8232。

 

新增計算資料行改善效能(參考[SQL Server]提高xml查詢效能)

--新增計算資料行
  alter table [Invoices] add ReceivedBy as JSON_VALUE(ReturnedDeliveryData, '$.ReceivedBy') PERSISTED
  --依照TSQL建立正確索引
  CREATE INDEX idx_ReceivedBy ON dbo.[Invoices](ReceivedBy)
  --改寫查詢
  SELECT'RiCo' as [UserName] ,ReceivedBy 
  FROM [Invoices]
  WHERE ReceivedBy = N'Nils Kalnins';

使用索引搜尋，整體成本:0.0034、Logical Reads:5。

 

Enjoy SQL Server 2016

 

參考

Format Query Results as JSON with FOR JSON (SQL Server)

[SQL Server]提高xml查詢效能