随笔- 224 文章- 15 评论- 3 阅读- 63109

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 转行记录

 1 Select top 3 '数据库中加载' Dsp, MatID,Code,Name from MMF
 2 
 3 Declare @Infos NVarchar(Max)
 4 
 5 Select @Infos=(Select top 3 MatID,Code,Name from MMF for Json Path)
 6 
 7 Select @Infos 返回值
 8 
 9 Select '解析JSON' Dsp, * from OpenJson(@Infos) with(
10  MatID NVarchar(50),
11  Code NVarchar(50),
12  Name NVarchar(50)
13 )

-- 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