Sql Server 处理 Json 相关技术小结
Json 是一种流行的数据存储和传输格式，SQL Server 从 2016 版本开始，通过系统内置的 Json函数 编写 SQL 语句即可 处理 Json 数据，以及 生成 Json 数据，给开发工作带来了极大的便利。
本篇博客文章先分别介绍各个技术细节，最后列举几个实际工作中可能会所遇到的案例，给大家介绍如何采用 SQL Server 的 Json 相关技术 进行处理解决。闲话不多说，直奔主题！！！
一、查询Json的数据
我们可以通过一个变量，将 Json 传入 SQL Server 中，SQL Server 是采用系统函数 OPENJSON() 这个函数来解析处理的。
对于没有 key，只有 value 值的 Json数组 字符串，OPENJSON()函数 会自动按照 Json 中各个 value 的先后顺序，从 0 开始生成 key ，如下所示：
这里需要解释的是 type 这个字段的含义，它表示 value 值的数据类型：
一共有6种type类型：
0 表示null值
1 表示字符串
2 表示数字
3 表示布尔值，
4 表示Json 数组
5 表示Json对象
我们也可以通过 OPENJSON()函数 利用路径表达式，去获取自己想要的一部分Json数据，如下图所示：
路径表达式以 $. 开头，上下层次之间用点（.）来连接，非常容易理解和编写。
需要注意的是：如果路径表达式的某个 key 的名称包含特殊字符，需要用 英文双引号 将 key 的名称包裹即可。
拿一个复杂的 Json 来举例吧，以展示 OPENJSON() 函数的强大解析能力。
对于复杂的 Json 字符串，我们也可以使用 OPENJSON() 函数，搭配路径表达式，同时采用 With 关键字进行灵活解析处理。
请看以下图：
With关键字 实际上是定义查询结果的表结构，可以指定每个字段的名称，字段类型，以及采用路径表达式要获取哪些 Json 数据作为字段的值，如果获取不到的话，值为null 。
如果 Json 结构很简单的话，并且 with 里面定义的字段名称 跟 Json 的字段名称相同的话，则可以省略路径表达式，如下图所示：
既然已经可以轻松将 Json 转换为查询的结果表了，那么剩下的工作就很容易了。
最后展示一个综合例子，让一个数据库表跟 json 进行 join 查询，如下图所示：
现在是不是感觉 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 数据。
采用 JSON_VALUE（数据库字段名称,’路径表达式’） 可以提取一个表字段中 Json 的指定字段值，所以如果我想查询 法国 的港口，可以这样写 SQL 语句：
如果表中的数据量比较大，我们可以针对 Json 中的一些字段建立索引，提高查询效率。
假设我想针对 Json 中的国家名称建立索引，则可以通过以下方式创建：
• 必须首先创建一个“虚拟列”，用于返回你要用于筛选的值。
• 然后，需要对该虚拟列创建索引。
如下图所示：
我们发现 dbo.Data 表多了一个虚拟列：
此时，就可以高效率的通过国家名称来查询json 数据了，如下图所示：
当然这里只是用了比较简单的 json 格式，你可以在数据库里面存储格式复杂的 Json ，只要利用好路径表达式就可以了。
三、对Json进行修改和验证
我们可以采用 JSON_MODIFY（数据库字段名称，’路径表达式’,修改的结果值） 来修改数据库 Json 字段的值。
假如我想把数据库字段 ID 为 6 的 Json 中的 PortName，名称修改为 呵呵，可以这样写SQL语句：
（备注：这里请看截图中最左边的数据库字段 ID，不是 Json 数据中的 ID）
通过 ISJSON()函数 可以验证一个 json 字符串格式是否正确。
如果 ISJSON() 函数返回 1 表示 json 格式正确， 返回 0 表示不正确。
四、通过SQL生成Json
我们可以使用 SQL 语句，通过 For Json Path 来生成 Json 数据，如下图：
如果想给 Json 字段重新命名，则在编写 SQL 给字段命别名即可，如下图
如果想让生成的 json 具有一定的层级结构，那么只需要在给别名命名的时候，通过点(.)来表示层级结构即可，如下图：
可以使用 Root 给 Json 增加外层的 key 包裹，如下图所示：
如果 SQL 语句查询出来的字段值为 null 时，默认的情况下，该字段则不会生成 json 。
例如上面的 PortNameEn 如果第一行为 null 值，而其他行不为 null 值时，则生成的 json 第一行就没有 PortNameEn 这个字段，其他行生成的 json 有PortNameEn 这个字段。
如下图所示，假设所有行的 PortNameEn 都为 null 值时，则生成的 json 字段都不包含 PortNameEn 这个字段：
如果字段的值为 null ，也要生成把该字段包含在 json 中的话，也很容易使用 INCLUDE_NULL_VALUES 即可，如下图所示：
默认情况下生成的 json 都会用 [ ] 进行包裹，假如你只有一条数据，不想用 [ ] 进行包裹，则使用 WITHOUT_ARRAY_WRAPPER 即可，如下图所示：
假如我有一个 json 字符串，想要合并到一个 json 字符串中，必须使用系统内置函数 JSON_QUERY() 来实现，如下图：
五、实际案例应用
1 批量往数据库传入数据
对于 C# 来说如果想批量往 SQL Server 中传入数据的话，传统最佳方法就是采用表类型。
但是对于其它非 .net 编程语言的，就只能通过循环拼接大量的 SQL 语句来实现了。
即使 C# 表类型实现方案很好，但是前提条件是必须在 SQL Server 中提前创建好表类型。
如果批量入库的需求很多的话，就需要创建很多表类型，万一后续需求变更，有可能还需要修改表类型，也挺麻烦的。
现在无论使用任何编程语言，只需要把 json 字符串直接传入数据库即可实现。
假设 SQL语句中 或者 存储过程中，使用一个变量名称为 @Json 的参数变量，用来接收到外面传入的 json 字符串：
2 内存优化表编写公用的本地编译的函数
当使用 SQL Server 的内存优化表时，只有采用本地编译的模块操作内存优化表，才会得到最高的效率。
本地编译模块包括：本地编译存储过程，本地编译函数，本地编译触发器。
但是本地编译模块，相比普通的存储过程，函数，触发器来说，有一些限制条件。
比如本地编译模块只能操作内存优化表，不能操作磁盘表；
本地编译的函数，只能返回单个值，无法返回一个表等等。
如果一个项目，有很多本地编译的存储过程，中间有相同的业务逻辑，当然需要将其提取出来，编写成一个本地编译的函数，这样才是最佳方案。
如果需要本地编译的函数返回一个表的话，那是不支持的，我们就可以让本地编译的函数返回 json 字符串，这样就可以巧妙解决问题。
3 其它一些使用场景
如果你没有学习和使用过 MongoDB，只是想在一些中小型项目中，处理不是很大的数据量时，可以考虑采用 SQL Server。
平时可能会遇到这样的场景，有这样一些数据，比如日志，平时很少用到，只有在需要进行统计，或者程序出现问题的时候，才想起来使用。或者在记录日志的前期，并没有考虑太多的需求，比如该建立几张表，每张表的字段是哪些等等。
此时，最简单的方式就是，随便建立一张表，把自己能想到的字段先创建好，然后专门有一个字段，用来将一些信息存储为 json 格式的数据。等后续需要使用的话，可以针对 json 建立索引，以及通过 sql 语句查询 json ，处理 json 等。
另外还有一些数据格式，比较复杂，但是又不想建立额外的表来存储，比如员工的上下级管理，好多好多层级。如果采用一张表，通过 parentid 来表示层级关系的话，很不直观。采用 json 进行存储和提取就会比较容易。
另外还有一些配置信息，以前采用 xml 片段存储到数据库中，现在完全可以采用 json 进行存储和查询，非常方便。
