MSSQL2016以上 处理JSON

https://docs.microsoft.com/zh-cn/sql/relational-databases/json/json-data-sql-server【 语法 https://docs.microsoft.com/zh-cn/sql/t-sql/functions/openjson-transact-sql

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]
<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

jsonExpression：json字符串表达式，path：路径表达式，with_clause：需要提取的列，
理解path：比如下面的json格式

{
    "people": [{
        "name": "John",
        "surname": "Doe"
    }, {
        "name": "Jane",
        "surname": null,
        "active": true
    }]
}

】一：数据解析json字符串

--JSON字符串
DECLARE @strJson NVARCHAR(4000)=N'{"root":[{"ID":1,"name":"张三","Chinese":90,"Math":80},{"ID":2,"name":"李四","Chinese":75,"Math":90},{"ID":3,"name":"王五","Chinese":68,"Math":100}]}';
--ISJSON()内置函数判断是否json字符串
 SELECT ISJSON(@strJson) AS IsJsonStr
--解析json字符串
SELECT * FROM OPENJSON(@strJson,'$.root')
 WITH(
ID INT,name NVARCHAR(50),Chinese INT,Math INT
) AS root
--路径选取第一个，结果只有张三这一行
SELECT * FROM OPENJSON(@strJson,'$.root[0]')
 WITH(
ID INT,name NVARCHAR(50),Chinese INT,Math INT
) AS root

二：数据库返回json字符串

--插入表数据
create table t1(ID int IDENTITY,name nvarchar(50),Chinese int ,Math int)
insert into t1 values ('张三',90,80),('李四',75,90),('王五',68,100)
select * from t1
select *  from t1 for json PATH,ROOT('root')

--返回的结果注意ROOT会添加一个根节点
{"root":[{"ID":1,"name":"张三","Chinese":90,"Math":80},{"ID":2,"name":"李四","Chinese":75,"Math":90},{"ID":3,"name":"王五","Chinese":68,"Math":100}]}
--当然可以添加条件
select *  from t1 WHERE ID=1 for json PATH,ROOT('root')
--返回
{"root":[{"ID":1,"name":"张三","Chinese":90,"Math":80}]}
--此外我们可以将部分列放在一个节点处(将Chinese和Math放在Points)
select ID,name, Chinese as [Points.Chinese], Math as [Points.Math] FROM t1 for json path
--结果
[{"ID":1,"name":"张三","Points":{"Chinese":90,"Math":80}},{"ID":2,"name":"李四","Points":{"Chinese":75,"Math":90}},{"ID":3,"name":"王五","Points":{"Chinese":68,"Math":100}}]

三：使用内置函数JSON_VALUE:JSON_VALUE 函数从 JSON 字符串中提取标量值

DECLARE @jsonInfo NVARCHAR(MAX),@town NVARCHAR(50)

SET @jsonInfo=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }' 
 SET @town = JSON_VALUE(@jsonInfo, '$.info.address.town')  
 --Bristol
 SELECT @town

四:JSON_QUERY( expression [ , path ] )从 JSON 字符串中提取对象或数组。

SELECT  JSON_QUERY(@jsonInfo,'$.info.address')
--返回
{  "town":"Bristol", "county":"Avon","country":"England"}