十四、SQLSERVER JSON

参考：

https://www.cnblogs.com/wangjiming/p/7407555.html

https://blog.csdn.net/weixin_30677617/article/details/97238134

 将 SQL Server 数据转换为 JSON 或导出 JSON

示例：

SELECT EquipmentId, EquipmentCode FROM Basal_Equipment FOR JSON PATH,ROOT('Equipment')

 结果：

{"Equipment":[
	{"EquipmentId":66,"EquipmentCode":"a1"},
	{"EquipmentId":73,"EquipmentCode":"aaaa"},
	{"EquipmentId":71,"EquipmentCode":"cc设备编码"},
	{"EquipmentId":67,"EquipmentCode":"EqCode1"}
]}

 判断是否为JSON

语法：

DECLARE @param <data type>
SET @param = <value>

IF (ISJSON(@param) > 0)  
BEGIN  
     -- Do something with the valid JSON value of @param.  
END

 示例：

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col) > 0 

 

JSON_VALUE 获取JSON字符串的数据

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(32)

SET @jsonInfo=N'{"info":[{"EquipmentId":"1"},{"EquipmentId":"2"}]}';

SET @town=JSON_VALUE(@jsonInfo,'$.info[0].EquipmentId'); -- 1
SET @town=JSON_VALUE(@jsonInfo,'$.info[0].EquipmentId'); -- 2

SELECT @town AS town

 JSON_MODIFY 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info

SET @info=JSON_MODIFY(@info,'$.name','Mike')
PRINT @info

 

 

将 JSON 数据导入 SQL Server 表

示例：