SQL Server中JSON函数的用法详解

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

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

官方文档：https://docs.microsoft.com/zh-cn/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

下面是我们熟悉的SELECT及输出格式，后面对JSON的演示基于此SQL：

一、将查询结果输出JSON格式

1、FOR JSON AUTO：SELECT语句的结果以JSON输出。

要将SELECT语句的结果以JSON输出，最简单的方法是在后面加上FOR JSON AUTO:

2、FOR JSON AUTO，Root（’’）：为JOSN加上根节点

若要为FOR JSON加上Root Key，可以用ROOT选项来自定义ROOT 节点的名称：

3、FOR JSON PATH输出：可通过列别名来定义JSON对象的层次结构

若要自定义输出JSON格式的结构时，必须使用JSONPATH。

FOR JSON Auto，自动按照查询语句中使用的表结构来创建嵌套的JSON子数组，类似于For Xml Auto特性。
FOR JSON Path，通过列名或者列别名来定义JSON对象的层次结构，列别名中可以包含“.”，JSON的成员层次结构将会与别名中的层次结构保持一致。
这个特性非常类似于早期SQL Server版本中的For Xml Path子句，可以使用斜线来定义xml的层次结构。

4、FOR JSON PATH+ROOT输出：为JOSN加上根节点

5、INCLUDE_NULL_VALUES：值null的字段需要显示出现。

为NULL的数据在输出JSON时，会被忽略，若想要让NULL的字段也显示出来，可以加上选项INCLUDE_NULL_VALUES，该选项也适用于AUTO。

6、列的别名，可以增加带有层级关系的节点。

比如下面的SQL，增加了一个“SN”节点，把栏位SERNUM和CLIMAT放在里面：

演示实例：

select TOP (2) id,  Plies, Createtime from [dbo].[B3PliesData] ORDER BY ID  ;
--1178    3    2020-07-21 14:33:18.480
--1179    3    2020-07-21 14:36:27.457

select TOP (2) id,  Plies as [myObject.Plies], Createtime as [myObject.Createtime] from [dbo].[B3PliesData]  ORDER BY ID for json auto;
--[{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]

select TOP (2) id,  Plies, Createtime from [dbo].[B3PliesData]  ORDER BY ID for json auto ,root('myRoot') ;
--{"myRoot":[{"id":1178,"Plies":3,"Createtime":"2020-07-21T14:33:18.480"},{"id":1179,"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}]}

select TOP (2) id,  Plies as [myObject.Plies], Createtime as [myObject.Createtime]  from [dbo].[B3PliesData]   ORDER BY ID for json path;
--[{"id":1178,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:33:18.480"}},{"id":1179,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}}]

select TOP (2) id,  Plies, Createtime,null as mynull from [dbo].[B3PliesData]  ORDER BY ID  for json path,root('myRoot');
--{"myRoot":[{"id":1178,"Plies":3,"Createtime":"2020-07-21T14:33:18.480"},{"id":1179,"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}]}

select TOP (2) id,  Plies, Createtime,null as mynull from [dbo].[B3PliesData]  ORDER BY ID for json path,root('myRoot'),include_null_values;
--{"myRoot":[{"id":1178,"Plies":3,"Createtime":"2020-07-21T14:33:18.480","mynull":null},{"id":1179,"Plies":3,"Createtime":"2020-07-21T14:36:27.457","mynull":null}]}

二、解析JSON格式的数据

1、使用OPENJSON()函数：

2、通过WITH选项，自定义输出列：

实例演示：

-------------1、-------------
declare @json as varchar(8000)
set @json='[
{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},
{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]'
select * from openjson(@json);
--key    value    type
--0    {"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"}    5
--1    {"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}    5

-------------2、-------------
declare @json1 as varchar(8000)
set @json1='[
{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},
{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]
'
select * from openjson(@json1)
with(
id varchar(10) '$.id',
Plies  int '$."myObject.Plies"',
Createtime datetime '$."myObject.Createtime"'
);
--id    Plies    Createtime
--1178    3    2020-07-21 14:33:18.480
--1179    3    2020-07-21 14:36:27.457

-------------3、-------------
declare @json2 as varchar(8000)
set @json2='{"myRoot":[
{"id":1178,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:33:18.480"}},
{"id":1179,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}}
]}'

select * from openjson(@json2,'$.myRoot')
with(
id varchar(10) ,
Plies  int '$.myObject.Plies',
Createtime datetime '$.myObject.Createtime'
);
--id    Plies    Createtime
--1178    3    2020-07-21 14:33:18.480
--1179    3    2020-07-21 14:36:27.457

三、JSON函数

declare @param nvarchar(max);

set @param = N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }';

1、ISJSON：测试字符串是否包含有效 JSON。

print iif(isjson(@param) > 0, 'OK', 'NO');

返回：OK

2、JSON_VALUE ：从 JSON 字符串中提取标量值。

print json_value(@param, '$.info.address.town');
print json_value(@param, '$.info.tags[1]');

返回：Bristol，Water polo

3、JSON_QUERY ：从 JSON 字符串中提取对象或数组。返回类型为 nvarchar(max) 的 JSON 片段

print json_query(@param, '$.info');
{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
          "county":"Avon",  
          "country":"England"  
        },  
        "tags":["Sport", "Water polo"]  
}

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

print json_modify(@param, '$.info.address.town', 'London');

{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"London",  
         "county":"Avon",  
          "country":"England"  
        },  
        "tags":["Sport", "Water polo"]  
     },  
     "type":"Basic"  
  }

实例演示：

declare @param nvarchar(max);
set @param=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }';

print iif(isjson(@param)>0, 'OK', 'NO');
print json_query(@param);
print json_value(@param, '$.info.address.town');
print json_value(@param, '$.info.tags[1]');
print json_query(@param, '$.info');
print json_query('["2020-1-8","2020-1-9"]');
print json_modify(@param, '$.info.address.town', 'London');

四、注意事项

SQL2016 中的新增的内置JSON进行了简单介绍，主要有如下要点：

JSON能在SQLServer2016中高效的使用，但是JSON并不是原生数据类型;
如果使用JSON格式必须为输出结果是表达式的提供别名;
JSON_VALUE 和 JSON_QUERY 函数转移和获取Varchar格式的数据，因此必须将数据转译成你需要的类型。
在计算列的帮助下查询JSON可以使用索引进行优化。

到此这篇关于SQL Server使用JSON函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持米米素材网。

正文

SQL Server中JSON函数的用法详解

一、将查询结果输出JSON格式

1、FOR JSON AUTO：SELECT语句的结果以JSON输出。

2、FOR JSON AUTO，Root（’’）：为JOSN加上根节点

3、FOR JSON PATH输出：可通过列别名来定义JSON对象的层次结构

4、FOR JSON PATH+ROOT输出：为JOSN加上根节点

5、INCLUDE_NULL_VALUES：值null的字段需要显示出现。

6、列的别名，可以增加带有层级关系的节点。

二、解析JSON格式的数据

1、使用OPENJSON()函数：

2、通过WITH选项，自定义输出列：

三、JSON函数

1、ISJSON：测试字符串是否包含有效 JSON。

2、JSON_VALUE ：从 JSON 字符串中提取标量值。

3、JSON_QUERY ：从 JSON 字符串中提取对象或数组。返回类型为 nvarchar(max) 的 JSON 片段

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

四、注意事项

相关阅读

oracle下实现恢复一个丢失

mysql死锁是什么意思

Oracle数据库处理多媒体信

mysql installer community 5.7.1

Python实现连接MySql数据库及

查看Oracle的执行计划一句

修改oracle数据库用户名及

Oracle硬解析和软解析的区

发表评论

一、 将查询结果输出JSON格式

1、FOR JSON AUTO：SELECT语句的结果以JSON输出。

2、FOR JSON AUTO，Root（’’） ：为JOSN加上根节点

3、FOR JSON PATH输出：可通过列别名来定义JSON对象的层次结构

4、FOR JSON PATH+ROOT输出：为JOSN加上根节点

5、INCLUDE_NULL_VALUES：值null的字段需要显示出现。

6、列的别名，可以增加带有层级关系的节点。

二、 解析JSON格式的数据

1、使用OPENJSON()函数：

2、通过WITH选项，自定义输出列：

三、JSON函数

1、ISJSON：测试字符串是否包含有效 JSON。

2、JSON_VALUE ：从 JSON 字符串中提取标量值。

3、JSON_QUERY ：从 JSON 字符串中提取对象或数组。返回类型为 nvarchar(max) 的 JSON 片段

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

四、注意事项

相关阅读

oracle下实现恢复一个丢失

mysql死锁是什么意思

Oracle数据库处理多媒体信

mysql installer community 5.7.1

Python实现连接MySql数据库及

查看Oracle的执行计划一句

修改oracle数据库用户名及

Oracle硬解析和软解析的区

发表评论

一、将查询结果输出JSON格式

2、FOR JSON AUTO，Root（’’）：为JOSN加上根节点

二、解析JSON格式的数据