JSON数据

Quber...大约 10 分钟数据库SqlServerJSON

JSON 数据

本章，我们主要针对 SqlServer 中的JSON 数据进行简单的介绍，方便我们日常中查阅！

注意事项

需要我们注意的是，SqlServer 对 JSON 的支持，是从SqlServer2016（13.x）+版本开始的，因此低于此版本是没法操作 JSON 数据的。

1、介绍

JSON 是一种流行的数据格式，用于在现代 Web 和移动应用程序中交换数据。 JSON 还可用于在日志文件或 Microsoft Azure Cosmos DB 等 NoSQL 数据库中存储非结构化数据。许多 REST Web 服务以 JSON 文本格式返回结果，或接受采用 JSON 格式的数据。例如，大多数 Azure 服务（如 Azure 搜索、Azure 存储和 Azure Cosmos DB）都提供返回或使用 JSON 的 REST 终结点。 JSON 也是用于通过 AJAX 调用在网页与 Web 服务器之间交换数据的主要格式。

JSON 函数首次是在 SQL Server 2016（13.x）中引入的，使用它们可在同一数据库中将 NoSQL 和相关概念合并。可以将经典关系列与同一表中包含格式化为 JSON 文本的文档的列合并，在关系结构中分析和导入 JSON 文档，或者将关系数据格式化为 JSON 文本。

下面是 JSON 文本的示例：

[
    {
        "name": "John",
        "skills": ["SQL", "C#", "Azure"]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

通过使用 SQL Server 内置函数和运算符，你可以对 JSON 文本执行以下操作：

分析 JSON 文本和读取或修改值；
将 JSON 对象数组转换为表格式；
在转换后的 JSON 对象上运行任意 Transact-SQL 查询；
将 Transact-SQL 查询的结果设置为 JSON 格式。

2、简单使用

接下来，我们就使用 SqlServer 提供的一些内置函数对 JSON 数据进行各种操作。

2.1、ISJSON

该函数的作用为，检查字符串是否为有效的 JSON，返回 bit 类型。

如下代码所示为检查各个字符串是否为有效的 JSON：

--ISJSON函数：检查字符串是否为有效的JSON，返回bit类型
SELECT
	ISJSON('') 是否为JSON,
	ISJSON(' ') 是否为JSON,
	ISJSON('1') 是否为JSON,
	ISJSON('@') 是否为JSON,
	ISJSON('{}') 是否为JSON,
	ISJSON('{a:1}') 是否为JSON,
	ISJSON('{"a":1}') 是否为JSON,
	ISJSON('{"name":"Quber","isDel":true}') 是否为JSON;

说明

该函数只包含一个参数，类型为字符串。

返回的类型为 bit。

具体详细介绍可参见官网ISJSON (Transact-SQL)open in new window

2.2、JSON_VALUE

该函数的作用为，获取 JSON 字符串中某个 Key 的值，返回sql_variant^[1]类型。

如下代码所示为获取某个 Key 对应的值：

--JSON_VALUE函数：获取JSON字符串中某个Key的值，返回sql_variant类型
DECLARE @JsonStr NVARCHAR(MAX);
SET @JsonStr=N'{
    "name": "Quber",
    "age": 33,
    "mobile": "13551360000",
    "techs": [
        {
            "id": 1,
            "name": ".Net",
            "infos": [
                "MVC",
                "Web Api",
                "WPF"
            ]
        },
        {
            "id": 2,
            "name": "Java",
            "infos": [
                "My Batis",
                "Spring Boot",
                "Spring Cloud"
            ]
        }
    ]
}';
SELECT
	JSON_VALUE(@JsonStr,'$.name') name的值,
	JSON_VALUE(@JsonStr,'$.techs[0].id') techs第一个对象中的id的值,
	JSON_VALUE(@JsonStr,'$.techs[1].name') techs第二个对象中的name的值,
	JSON_VALUE(@JsonStr,'$.techs[1]') 返回为空

说明

该函数只包含两个参数，第一个参数类型为 JSON 字符串，第二个参数类型为 key 路径字符串。

返回的类型为sql_variant^[1:1]。

具体详细介绍可参见官网JSON_VALUE (Transact-SQL)open in new window

注意

不知道大家注意到上述代码中最后一个查询没有，返回的是null，原因是该函数最终返回的是某个 Key 对应的具体值，而$.techs[1]代表的是一个对象。

如果要获取一个对象，我们可以使用JSON_QUERY函数去获取。

2.3、JSON_QUERY

该函数的作用为，获取 JSON 字符串中某个 Key 的对象或数组，返回sql_variant^[1:2]类型。

如下代码所示为获取某个 Key 对应的对象或数组：

--JSON_QUERY函数：获取JSON字符串中某个Key的对象或数组
DECLARE @JsonStr NVARCHAR(MAX);
SET @JsonStr=N'{
    "name": "Quber",
    "age": 33,
    "mobile": "13551360000",
    "techs": [
        {
            "id": 1,
            "name": ".Net",
            "infos": [
                "MVC",
                "Web Api",
                "WPF"
            ]
        },
        {
            "id": 2,
            "name": "Java",
            "infos": [
                "My Batis",
                "Spring Boot",
                "Spring Cloud"
            ]
        }
    ]
}';
SELECT
	JSON_QUERY(@JsonStr,'$.techs') 获取techs数组对象,
	JSON_QUERY(@JsonStr,'$.techs[0]') 获取techs数组中的第一个对象,
	JSON_QUERY(@JsonStr,'$.techs[1]') 获取techs数组中的第二个对象,
	JSON_QUERY(@JsonStr,'$.techs[1].name') 返回为空

说明

该函数只包含两个参数，第一个参数类型为 JSON 字符串，第二个参数类型为 key 路径字符串。

返回的类型为sql_variant^[1:3]。

具体详细介绍可参见官网JSON_QUERY (Transact-SQL)open in new window

注意

不知道大家注意到上述代码中最后一个查询没有，返回的是null，原因是该函数最终返回的是某个 Key 对应的具体对象或数组（和JSON_VALUE正好相反），而$.techs[1].name代表的是一个值。

2.4、JSON_MODIFY

该函数的作用为，修改 JSON 字符串中某个 Key 的值，返回nvarchar类型。

如下代码所示为修改某个 Key 对应的值：

--JSON_MODIFY函数：修改JSON字符串中某个Key的值
DECLARE @JsonStr NVARCHAR(MAX);
SET @JsonStr=N'{"name":"Quber","age":33,"mobile":"13551360000","techs":[{"id":1,"name":".Net","infos":["MVC","Web Api","WPF"]}]}';
SELECT JSON_MODIFY(@JsonStr,'$.name','Qubernet') 修改name;
SELECT JSON_MODIFY(@JsonStr,'$.techs[0].name','.Net++') 修改techs第一个对象中的name;
SELECT JSON_MODIFY(@JsonStr,'$.techs[0].infos[0]','MVC++') 修改techs第一个对象中infos第一个值;

说明

该函数只包含三个参数，第一个参数类型为 JSON 字符串，第二个参数类型为 key 路径字符串，第三个参数为修改的值。

返回的类型为nvarchar。

具体详细介绍可参见官网JSON_MODIFY (Transact-SQL)open in new window

2.5、OPENJSON

JSON 为数组的时候（数组中为对象）

该函数的作用为，将 JSON 集合转换为行集（Table），返回table类型。

如下代码所示为将 JSON 集合转换为行集（Table）：

--OPENJSON函数：将JSON集合转换为行集（Table）
DECLARE @JsonStr NVARCHAR(MAX);
SET @JsonStr=N'[
    {
        "name": "Quber",
        "age": 33,
        "mobile": "13551360000",
        "infos": {
            "id": 1,
            "email": "quber@163.com"
        }
    },
    {
        "name": "Jack",
        "age": 30,
        "mobile": "13587461234",
        "infos": {
            "id": 2,
            "email": "jack@163.com"
        }
    }
]';
SELECT *
FROM OPENJSON(@JsonStr) WITH (
    id INT 'strict $.infos.id',
    name NVARCHAR(50) '$.name',
    --age INT '$.age',
    age INT,--等价于age INT '$.age'
    mobile NVARCHAR(50) '$.mobile',
    email NVARCHAR(50) '$.infos.email'
);

JSON 为对象的时候

该函数的作用为，将 JSON 对象转换为行集（Table，返回 key、value 和 type 列），返回table类型。

如下代码所示为将 JSON 对象转换为行集（Table，返回 key、value 和 type 列）：

--OPENJSON函数：将JSON对象转换为行集（Table，返回key、value和type列）
DECLARE @JsonStr NVARCHAR(MAX);
SET @JsonStr=N'{
    "name": "Quber",
    "age": 33,
    "mobile": "13551360000",
    "infos": {
        "id": 1,
        "email": "quber@163.com"
    }
}';
SELECT * FROM OPENJSON(@JsonStr);
SELECT * FROM OPENJSON(@JsonStr,'$.infos');

JSON 为数组的时候（数组中为具体值）
该函数的作用为，将 JSON 对象转换为行集（Table，返回 key、value 和 type 列），返回table类型。
如下代码所示为将 JSON 对象转换为行集（Table，返回 key、value 和 type 列）：
```
--OPENJSON函数：将JSON对象转换为行集（Table，返回key、value和type列）
DECLARE @JsonStr NVARCHAR(MAX);
SET @JsonStr=N'[".Net", "Java", "Python", "TypeScript", 123, 12.34, true, [1,2,3], {"name":"Quber"}, null]';
SELECT * FROM OPENJSON(@JsonStr);
```
OPENJSON

说明

JSON 为数组的时候（数组中为对象）
该函数只使用了一个参数，该参数类型为 JSON 字符串（数组），同时需要结合WITH一起使用。
返回的类型为table。
OPENJSON 将 JSON 对象的数组转换为表，其中每个对象表示为一行，键/值对将作为单元返回。输出遵循下列规则：
- OPENJSON 将 JSON 值转换为 WITH 子句中指定的类型。
- OPENJSON 可以处理规则的键/值对以及分层组织的嵌套对象。
- 不需要返回 JSON 文本中包含的所有字段。
- 如果 JSON 值不存在，则 OPENJSON 将返回 NULL 值。
- 可以选择在类型规范后指定一个路径，以引用嵌套属性或按不同的名称引用属性。
- 路径中可选的 strict 前缀指定 JSON 文本中必须存在指定属性的值。
JSON 为对象的时候
该函数使用了两个参数，第一个参数为 JSON 字符串（对象），第二个参数为 key 路径字符串
返回的类型为table。
该函数返回了固定的 3 列，第一列的名称为 key（JSON 对象中的 Key 名称），第二列的名称为 value（JSON 对象中的 Key 对应的值），第三列的名称为 type（JSON 对象中 key 对应的值类型）
其中第三列 type 对应的值（int 类型）包含如下范围：
0： null 类型；
1：字符串类型；
2：数字类型；
3：布尔类型；
4：数组类型；
5：对象类型。
JSON 为数组的时候（数组中为具体值）
说明和【JSON 为对象的时候】一致，唯一的区别是第一列返回的值是索引号。

具体详细介绍可参见官网OPENJSON (Transact-SQL)open in new window

2.6、OPENROWSET

该函数的作用为，将文件中的内容导入到表中，返回table类型。

如下代码所示为将文件中的内容导入到表中：

--读取文件中的内容
SELECT BulkColumn FROM OPENROWSET(BULK 'D:\test.json',SINGLE_CLOB) AS a;

--读取文件中的内容，并返回name属性对应的值
SELECT JSON_VALUE(BulkColumn,'$.name') FROM OPENROWSET(BULK 'D:\test.json',SINGLE_CLOB) AS a;

说明

该函数只包含两个参数，第一个参数类型为文件的物理路径（格式为：BULK+空格+'文件物理路径'），第二个参数为读取的内容返回的编码方式（有 SINGLE_CLOB、SINGLE_BLOB 和 SINGLE_NCLOB 可选）。

返回的类型为table。

具体详细介绍可参见官网OPENROWSET (Transact-SQL)open in new window

3、路径表达式

首先我们来看下面的 SQL 语句：

SELECT
	JSON_VALUE(@JsonStr,'$.name') name的值,
	JSON_VALUE(@JsonStr,'$.techs[0].id') techs第一个对象中的id的值,
	JSON_VALUE(@JsonStr,'$.techs[1].name') techs第二个对象中的name的值,
	JSON_VALUE(@JsonStr,'$.techs[1]') 返回为空

在上面的 SQL 语句中，我们看到了$.name、$.techs[1]和$.techs[1].name字符，我们都称之为路径表达式。

路径表达式的作用就是，告诉函数我们要查找的 JSON Key 路径。

3.1、路径规则

具体规则如下所示：

以符号$开头；
用符号.可指定某个 key，如：$.name；
如果 key 中有空格，请使用双引号将 key 包括起来，如：$."user name"；
可以使用方括号[]获取某个数组中的对象或值，数组索引从 0 开始，如：$.techs[0]。

3.2、路径模式

在路径表达式的开头，我们可以使用lax和strict来声明路径模式，默认为lax。

lax： 在 lax 模式下，如果路径表达式包含错误，函数将返回空值。例如，如果请求值 $.name，但 JSON 文本不包含 name 键，函数将返回 null，但不会引发错误；
strict： 在 strict 模式下，如果路径表达式包含错误，函数将引发错误。

3.3、示例

下面我们如下 JSON 为例进行说明：

{
    "name": "Quber",
    "last name": "Net",
    "age": 33,
    "mobile": "13551360000",
    "techs": [
        {
            "id": 1,
            "name": ".Net",
            "infos": ["MVC", "Web Api", "WPF"]
        },
        {
            "id": 2,
            "name": "Java",
            "infos": ["My Batis", "Spring Boot", "Spring Cloud"]
        }
    ]
}

路径表达式	值	说明
$	{"name":"Quber","last name":"Net","age":33,"mobile":"13551360000","techs":[{"id":1,"name":".Net","infos":["MVC","Web Api","WPF"]},{"id":2,"name":"Java","infos":["My Batis","Spring Boot","Spring Cloud"]}]}	所有 JSON
$.name	Quber	路径中使用了双引号来包裹 Key
$."last name"	Net
$.techs[0]	{"id":1,"name":".Net","infos":["MVC","Web Api","WPF"]}	获取数组的第一个对象
$.techs[0].name	.Net
$.techs[0].infos	["MVC","Web Api","WPF"]
$.techs[0].infos[1]	Web Api	获取数组中第一个值
lax $.name1	null	宽松模式（默认），不会报错，返回 null
strict $.name1		严格模式，直接报错

具体详细介绍可参见官网JSON 路径表达式 (SQL Server)open in new window

一种数据类型，用于存储 SQL Server 支持的各种数据类型的值。即：可以存储字符串、数字、日期、布尔、二进制等各类数据。具体参见官方文档：sql_variant (Transact-SQL)open in new window ↩︎ ↩︎ ↩︎ ↩︎