Javascript

Sql Server 处理 Json 相关技术小结

本文主要是介绍Sql Server 处理 Json 相关技术小结,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

Json 是一种流行的数据存储和传输格式,SQL Server2016 版本开始,通过系统内置的 Json函数 编写 SQL 语句即可 处理 Json 数据,以及 生成 Json 数据,给开发工作带来了极大的便利。

本篇博客文章先分别介绍各个技术细节,最后列举几个实际工作中可能会所遇到的案例,给大家介绍如何采用 SQL Server 的 Json 相关技术 进行处理解决。闲话不多说,直奔主题!!!

一、查询Json的数据

我们可以通过一个变量,将 Json 传入 SQL Server 中,SQL Server 是采用系统函数 OPENJSON() 这个函数来解析处理的。

对于没有 key,只有 value 值的 Json数组 字符串,OPENJSON()函数 会自动按照 Json 中各个 value 的先后顺序,从 0 开始生成 key ,如下所示:

image

这里需要解释的是 type 这个字段的含义,它表示 value 值的数据类型:
一共有6种type类型:
0 表示null值
1 表示字符串
2 表示数字
3 表示布尔值,
4 表示Json 数组
5 表示Json对象

我们也可以通过 OPENJSON()函数 利用路径表达式,去获取自己想要的一部分Json数据,如下图所示:

image

路径表达式以 $. 开头,上下层次之间用点(.)来连接,非常容易理解和编写。
需要注意的是:如果路径表达式的某个 key 的名称包含特殊字符,需要用 英文双引号 将 key 的名称包裹即可。

拿一个复杂的 Json 来举例吧,以展示 OPENJSON() 函数的强大解析能力。
对于复杂的 Json 字符串,我们也可以使用 OPENJSON() 函数,搭配路径表达式,同时采用 With 关键字进行灵活解析处理。
请看以下图:

image

With关键字 实际上是定义查询结果的表结构,可以指定每个字段的名称,字段类型,以及采用路径表达式要获取哪些 Json 数据作为字段的值,如果获取不到的话,值为null 。
如果 Json 结构很简单的话,并且 with 里面定义的字段名称 跟 Json 的字段名称相同的话,则可以省略路径表达式,如下图所示:

image

既然已经可以轻松将 Json 转换为查询的结果表了,那么剩下的工作就很容易了。
最后展示一个综合例子,让一个数据库表跟 json 进行 join 查询,如下图所示:

image

现在是不是感觉 SQL Server 解析 Json 变成自己想要的数据很容易了。(#^.^#)

二、存储 Json 和建立索引

用过 MongoDB 的小伙伴们都知道它是以 Json 结构存取数据的,但是 MongoDB 对 Json 数据的存取,采用的是其独有的一套语法,具有一定的学习成本,要是能够采用 SQL 语句操作存取 Json 数据那就完美了。

现在 SQL Server 也可以存储 Json ,并且针对 Json 的字段建立索引,也可以把 Json 存储在内存优化表中。这样不但实现了 MongoDB 的 Json 存取功能,同时也具有传统关系型数据库的所有优点,对于中小型项目(数据量不是很大)的案例场景,实在是太完美了。

我采用一个简单的 Json 例子讲解,方便大家理解。其实 Json 可以很复杂,结合我们上面所讲的 With 和路径表达式,可以解决绝大部分所遇到的问题。

我创建一个dbo.Data 表,里面存储的是一些港口样例数据,只有 2 个字段:
ID int类型,自增长,从 1 开始,步长为 1 。
Json varchar(max) 用来存储 Json 数据。

image

采用 JSON_VALUE(数据库字段名称,’路径表达式’) 可以提取一个表字段中 Json 的指定字段值,所以如果我想查询 法国 的港口,可以这样写 SQL 语句:

image

如果表中的数据量比较大,我们可以针对 Json 中的一些字段建立索引,提高查询效率。
假设我想针对 Json 中的国家名称建立索引,则可以通过以下方式创建:
• 必须首先创建一个“虚拟列”,用于返回你要用于筛选的值。
• 然后,需要对该虚拟列创建索引。
如下图所示:

image

我们发现 dbo.Data 表多了一个虚拟列:

image

此时,就可以高效率的通过国家名称来查询json 数据了,如下图所示:

image

当然这里只是用了比较简单的 json 格式,你可以在数据库里面存储格式复杂的 Json ,只要利用好路径表达式就可以了。

三、对Json进行修改和验证

我们可以采用 JSON_MODIFY(数据库字段名称,’路径表达式’,修改的结果值) 来修改数据库 Json 字段的值。
假如我想把数据库字段 ID 为 6 的 Json 中的 PortName,名称修改为 呵呵,可以这样写SQL语句:
(备注:这里请看截图中最左边的数据库字段 ID,不是 Json 数据中的 ID)

image

通过 ISJSON()函数 可以验证一个 json 字符串格式是否正确。

image

如果 ISJSON() 函数返回 1 表示 json 格式正确, 返回 0 表示不正确。

四、通过SQL生成Json

我们可以使用 SQL 语句,通过 For Json Path 来生成 Json 数据,如下图:

image
image

如果想给 Json 字段重新命名,则在编写 SQL 给字段命别名即可,如下图

image
image

如果想让生成的 json 具有一定的层级结构,那么只需要在给别名命名的时候,通过点(.)来表示层级结构即可,如下图:

image
image

可以使用 Root 给 Json 增加外层的 key 包裹,如下图所示:

image
image

如果 SQL 语句查询出来的字段值为 null 时,默认的情况下,该字段则不会生成 json 。
例如上面的 PortNameEn 如果第一行为 null 值,而其他行不为 null 值时,则生成的 json 第一行就没有 PortNameEn 这个字段,其他行生成的 json 有PortNameEn 这个字段。
如下图所示,假设所有行的 PortNameEn 都为 null 值时,则生成的 json 字段都不包含 PortNameEn 这个字段:

image
image

如果字段的值为 null ,也要生成把该字段包含在 json 中的话,也很容易使用 INCLUDE_NULL_VALUES 即可,如下图所示:

image
image

默认情况下生成的 json 都会用 [ ] 进行包裹,假如你只有一条数据,不想用 [ ] 进行包裹,则使用 WITHOUT_ARRAY_WRAPPER 即可,如下图所示:

image
image

假如我有一个 json 字符串,想要合并到一个 json 字符串中,必须使用系统内置函数 JSON_QUERY() 来实现,如下图:

image
image

五、实际案例应用

1 批量往数据库传入数据

对于 C# 来说如果想批量往 SQL Server 中传入数据的话,传统最佳方法就是采用表类型。
但是对于其它非 .net 编程语言的,就只能通过循环拼接大量的 SQL 语句来实现了。

即使 C# 表类型实现方案很好,但是前提条件是必须在 SQL Server 中提前创建好表类型。
如果批量入库的需求很多的话,就需要创建很多表类型,万一后续需求变更,有可能还需要修改表类型,也挺麻烦的。

现在无论使用任何编程语言,只需要把 json 字符串直接传入数据库即可实现。
假设 SQL语句中 或者 存储过程中,使用一个变量名称为 @Json 的参数变量,用来接收到外面传入的 json 字符串:

image

2 内存优化表编写公用的本地编译的函数

当使用 SQL Server 的内存优化表时,只有采用本地编译的模块操作内存优化表,才会得到最高的效率。
本地编译模块包括:本地编译存储过程,本地编译函数,本地编译触发器。

但是本地编译模块,相比普通的存储过程,函数,触发器来说,有一些限制条件。
比如本地编译模块只能操作内存优化表,不能操作磁盘表;
本地编译的函数,只能返回单个值,无法返回一个表等等。

如果一个项目,有很多本地编译的存储过程,中间有相同的业务逻辑,当然需要将其提取出来,编写成一个本地编译的函数,这样才是最佳方案。
如果需要本地编译的函数返回一个表的话,那是不支持的,我们就可以让本地编译的函数返回 json 字符串,这样就可以巧妙解决问题。

3 其它一些使用场景

如果你没有学习和使用过 MongoDB,只是想在一些中小型项目中,处理不是很大的数据量时,可以考虑采用 SQL Server。

平时可能会遇到这样的场景,有这样一些数据,比如日志,平时很少用到,只有在需要进行统计,或者程序出现问题的时候,才想起来使用。或者在记录日志的前期,并没有考虑太多的需求,比如该建立几张表,每张表的字段是哪些等等。

此时,最简单的方式就是,随便建立一张表,把自己能想到的字段先创建好,然后专门有一个字段,用来将一些信息存储为 json 格式的数据。等后续需要使用的话,可以针对 json 建立索引,以及通过 sql 语句查询 json ,处理 json 等。

另外还有一些数据格式,比较复杂,但是又不想建立额外的表来存储,比如员工的上下级管理,好多好多层级。如果采用一张表,通过 parentid 来表示层级关系的话,很不直观。采用 json 进行存储和提取就会比较容易。

另外还有一些配置信息,以前采用 xml 片段存储到数据库中,现在完全可以采用 json 进行存储和查询,非常方便。



这篇关于Sql Server 处理 Json 相关技术小结的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!