欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server之JSON 函数详解

程序员文章站 2022-04-16 16:37:49
sql server 2005开始支持xml数据类型,提供原生的xml数据类型、xml索引及各种管理或输出xml格式的函数。随着json的流行,sql server2016开始支持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 函数详解

有关在sql server中内置json支持的更多信息,请参阅json数据(sql server)

2.2 isjson

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

SQL Server之JSON 函数详解

2.2.1 例子

SQL Server之JSON 函数详解

SQL Server之JSON 函数详解

2.3 json_value

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

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

SQL Server之JSON 函数详解

SQL Server之JSON 函数详解

SQL Server之JSON 函数详解

2.4 json_query

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

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

SQL Server之JSON 函数详解

SQL Server之JSON 函数详解

2.5 json_modify

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

SQL Server之JSON 函数详解

SQL Server之JSON 函数详解

SQL Server之JSON 函数详解

小结

SQL Server之JSON 函数详解

3 参考文献

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

4 版权

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

以下是一些补充

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

SQL Server之JSON 函数详解

2、for json auto,root('') :为josn加上根节点
若要为for json加上root key,可以用root选项来自定义root 节点的名称:

SQL Server之JSON 函数详解

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的层次结构。

SQL Server之JSON 函数详解

4、for json path+root输出:为josn加上根节点

SQL Server之JSON 函数详解

5、include_null_values:值null的字段需要显示出现。
为null的数据在输出json时,会被忽略,若想要让null的字段也显示出来,可以加上选项include_null_values,该选项也适用于auto。

SQL Server之JSON 函数详解

6、列的别名,可以增加带有层级关系的节点。
比如下面的sql,增加了一个“sn”节点,把栏位sernum和climat放在里面:

SQL Server之JSON 函数详解

二、 解析json格式的数据

1、使用openjson()函数:

SQL Server之JSON 函数详解

2、通过with选项,自定义输出列:

SQL Server之JSON 函数详解

三、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可以使用索引进行优化。

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

相关标签: SQL Server JSON