SQL Server之JSON 函数详解

sql server 2005开始支持xml数据类型，提供原生的xml数据类型、xml索引及各种管理或输出xml格式的函数。随着json的流行，sql server2016开始支持json数据类型，不仅可以直接输出json格式的结果集，还能读取json格式的数据。

1 概述

本篇文件将结合msnd简要分析sqlserver中json函数，主要包括isjson,json_value,json_modify,json_query。

2 具体内容

2.1 json 函数

使用本节中页面中描述的功能来验证或更改json文本或提取简单或复杂的值。

有关在sql server中内置json支持的更多信息，请参阅json数据（sql server）。

2.2 isjson

测试字符串是否包含有效的json。

2.2.1 例子

2.3 json_value

从json字符串中提取标量值。

要从json字符串而不是标量值中提取对象或数组，请参阅json_query（transact-sql）。有关json_value和json_query之间的差异的信息，请参阅比较json_value和json_query。

2.4 json_query

从json字符串中提取对象或数组。

要从json字符串而不是对象或数组中提取标量值，请参阅json_value（transact-sql）。有关json_value和json_query之间的差异的信息，请参阅比较json_value和json_query。

2.5 json_modify

更新json字符串中的属性值，并返回更新的json字符串。

小结

3 参考文献

【01】https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/expressions-transact-sql

4 版权

感谢您的阅读，若有不足之处，欢迎指教，共同学习、共同进步。

以下是一些补充

下面是我们熟悉的select及输出格式，后面对json的演示基于此sql：

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放在里面：

二、 解析json格式的数据

1、使用openjson()函数：

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

三、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 字符串中提取对象或数组。

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

四、注意事项

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

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

好了这篇文章就介绍到这了，需要的朋友可以参考一下。