SQL SERVER -- JSON处理

MSSQL 数据库版本，数据库名，查询

select 
    LEFT(Convert(NVarchar(50),ServerProperty('productversion')), CHARINDEX('.', Convert(NVarchar(50),ServerProperty('productversion'))) - 1) MainVersion
    ,ServerProperty('productversion') ProductVersion
    ,ServerProperty('productlevel') ProductLevel
    ,ServerProperty('edition') Edition
    ,@@version FullVersion
    ,(SELECT compatibility_level FROM sys.databases WHERE name=DB_NAME()) CompatibilityLevel
    ,DB_NAME() DBName

--修改
-- ALTER DATABASE 数据库名称 SET COMPATIBILITY_LEVEL = 130

 

 

 

SQL Server 2005开始支持XML数据类型，提供原生的XML数据类型、XML索引及各种管理或输出XML格式的函数。

随着JSON的流行，SQL Server2016开始支持JSON数据类型，不仅可以直接输出JSON格式的结果集，还能读取JSON格式的数据

（JSON相关函数，参考：https://learn.microsoft.com/zh-cn/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver16 )

-- 行记录转JSON，以及 JSON 转行记录

Select top 3 '数据库中加载' Dsp, MatID,Code,Name from MMF

Declare @Infos NVarchar(Max)

Select @Infos=(Select top 3 MatID,Code,Name from MMF for Json Path)

Select @Infos 返回值

Select '解析JSON' Dsp, * from OpenJson(@Infos) with(
 MatID NVarchar(50),
 Code NVarchar(50),
 Name NVarchar(50)
)

 

 

 

-- JSON 字符串 多级转换

Declare @JsonStr Nvarchar(Max)='[{"State":0,"Name":"语文","ReMark":"了解国学信息","RepDtl":[{"ID":1,"Age":11},{"ID":2,"Age":12},{"ID":3,"Age":13}]},{"State":2,"Name":"数学","ReMark":"九天算术","RepDtl":[]},{"State":21,"Name":"英语","ReMark":"全球畅游","RepDtl":[{"ID":1,"Age":31},{"ID":2,"Age":32},{"ID":3,"Age":33}]}]'

 

 

转换处理 >> cross apply 

select Row_Number()Over(Order by tmpB.State,tmpC.ID) RIDX, tmpB.State,tmpC.ID,tmpB.Name,tmpC.Age,tmpB.ReMark
from OpenJson(@JsonStr) tmpA
cross apply openjson(tmpA.value) with (
State Integer,
Name NVarchar(50),
ReMark NVarchar(50),
RepDtl NVarchar(Max) '$.RepDtl' as Json
) tmpB
cross apply openjson(tmpB.RepDtl) with (
ID Integer,
Age Integer
) tmpC

 

>> outer apply  

 

>> OpenJson 后，各数据类型说明

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);