iis服务器助手广告广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 后端开发 > 其他教程 >SQL Server中JSON函数的用法详解
  • 604

0
分享到

SQL Server中JSON函数的用法详解

2024-04-02 19:04:59 604人浏览 薄情痞子
摘要

目录一、 将查询结果输出JSON格式1、FOR jsON AUTO：SELECT语句的结果以JSON输出。2、FOR JSON AUTO，Root（’&rsquo

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函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持编程网。

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: SQL Server中JSON函数的用法详解

本文链接: https://www.lsjlt.com/news/149386.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • SQL Server中JSON函数的用法详解
    目录一、 将查询结果输出JSON格式1、FOR JSON AUTO：SELECT语句的结果以JSON输出。2、FOR JSON AUTO，Root（’&rsquo...
    99+
    2022-11-13
  • SQL Server中字符串函数的用法详解
    在开发T-SQL时，经常会需要对字符串进行各种各样的操作，下面介绍常用的字符串函数。 一、编码转换 1、获取字符的ASCII码：ascii ASCII(espression)...
    99+
    2022-11-13
  • SQL Server中的JSON函数怎么使用
    这篇文章主要介绍“SQL Server中的JSON函数怎么使用”，在日常操作中，相信很多人在SQL Server中的JSON函数怎么使用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”SQL...
    99+
    2023-06-30
  • SQL Server中JSON 函数如何使用
    SQL Server中JSON 函数如何使用，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 JSON 函数使用本节中...
    99+
    2022-10-18
  • SQL Server中JSON 函数的作用是什么
    这期内容当中小编将会给大家带来有关SQL Server中JSON 函数的作用是什么，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。2.1 JSON 函数使用本节中页面中描述...
    99+
    2022-10-18
  • SQL Server中row_number函数的常见用法示例详解
    row_number函数是在SQL Server中用来给每行数据生成一个唯一的编号。它常用于对数据进行排序、分组和分页操作。以下是r...
    99+
    2023-08-14
    SQL Server
  • sql server中quotename()函数的用法
    在SQL Server中，QUOTENAME()函数用于将标识符（如表名、列名等）转换为带有方括号的格式，以便在查询中使用。QUOT...
    99+
    2023-09-14
    sql server
  • SQL Server stuff函数的用法
    1、作用删除指定长度的字符，并在指定的起点处插入另一组字符。2、语法STUFF ( character_expression , start , length ,character_expression2 ...
    99+
    2022-10-18
  • SQL中 patindex函数的用法详解
    返回pattern字符串在表达式expression里第一次出现的位置，起始值从1开始算。 pattern字符串在expression表达式里没找就返回0，对所有有效的文本和字符串就是有效的数据类型。 描述...
    99+
    2022-10-18
  • SQL Server中元数据函数的用法
    1、获取数据库标识符：DB_ID DB_ID函数用于获取当前数据库的唯一ID(int数据类型)，数据库ID用于服务器上唯一区分书库。 语法格式： DB_ID (['database_...
    99+
    2022-11-13
  • SQL Server DATEDIFF() 函数用法
    定义和用法 DATEDIFF(datepart,startdate,enddate) startdate 和 enddate 参数是合法的日期表达式。datepart 参数可以...
    99+
    2022-11-12
  • SQL中CONVERT()函数用法详解
    在SQL中，CONVERT()函数用于将一个数据类型的值转换为另一个数据类型的值。它的语法如下：CONVERT(data_type,...
    99+
    2023-09-01
    SQL
  • SQL Server中索引的用法详解
    索引是一种数据结构，用于提高数据库中数据的查询效率。SQL Server中的索引可以分为聚集索引和非聚集索引两种类型。聚集索引决定了...
    99+
    2023-08-17
    SQL Server
  • sql server数据库中raiserror函数的用法
    SQL Server数据库中的RAISERROR函数用于在存储过程或触发器中引发一个错误，并将错误信息传递给客户端。它可以用于自定义...
    99+
    2023-09-01
    SQL
  • Sql Server 开窗函数Over()的使用实例详解
    利用over()，将统计信息计算出来，然后直接筛选结果集 declare @t table( ProductID int, ProductName varchar(20), ProductTy...
    99+
    2022-10-18
  • SQL Server中交叉联接的用法详解
    目录1、交叉联接(cross join)的概念2、交叉联接的语法格式3、交叉查询的使用场景3.1 交叉联接可以查询全部数据3.2 交叉联接优化查询性能4、总结 今天给大家介绍SQLS...
    99+
    2022-11-12
  • sql语法中的concat()函数详解
    目录1.concat()2.concat_ws()3.group_concat()1.concat() concat()函数用于将两个字符串连接起来，形成一个单一的字符串...
    99+
    2022-08-08
    sqlconcat()函数 sqlconcat()
  • Sql Server中REPLACE函数的使用
    在SQL Server中，REPLACE函数用于替换字符串中的指定字符或子字符串。REPLACE函数的语法如下：REPLAC...
    99+
    2023-08-15
    Sql Server
  • SQL Server中的数据类型详解
    目录哪些对象需要数据类型一、 整数数据类型1、bit2、tinyint3、smallint4、int （integer）5、bigint二、 浮点数据类型1、real： 近似数值型2...
    99+
    2022-11-13
  • SQL Server DATEDIFF()函数的用法是什么
    SQL Server DATEDIFF()函数用于计算两个日期之间的差异，并返回指定的日期部分的差值。它的语法如下：DATEDIFF...
    99+
    2023-09-05
    SQL Server
软考高级职称资格查询
iis服务器助手广告
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作