SQLServer中构建JSON文档的全面指南

### SQL Server 中构建 JSON 文档的全面指南 #### 1. SQL Server 处理 JSON 的基础 SQL Server 提供了四个内置函数和 SELECT 查询的 FOR JSON 子句来处理和创建 JSON 文档。与 XML 方法不同，JSON 函数对键不敏感，但在 JSON 函数以及 ToString() 和 STAsText() CLR 函数中引用键成员时，键成员仍然是敏感的。以下是 SQL Server 中 JSON 内置函数的详细介绍： | JSON 过程 | 过程类型 | 描述 | | --- | --- | --- | | FOR JSON | 子句 | 构建 JSON 文档 | | ISJSON() | 函数 | 验证字符串是否为有效的 JSON | | JSON_VALUE() | 函数 | 从 JSON 文档中检索标量值 | | JSON_QUERY() | 函数 | 从 JSON 文档中检索对象或数组 | | JSON_MODIFY() | 函数 | 修改 JSON 文档 | | OPENJSON() | 表值函数 | 将 JSON 文档转换为包含行和列的表格式 | #### 2. 使用 AUTO 模式构建 JSON ##### 2.1 问题描述 想要自动构建 JSON 格式的结果。 ##### 2.2 解决方案 使用 FOR JSON 子句的 AUTO 模式可以返回 JSON 格式的行。以下是一个示例查询： ```sql SELECT TOP (2) CustomerName ,PrimaryContact ,AlternateContact ,PhoneNumber FROM WideWorldImporters.Website.Customers FOR JSON AUTO; ``` 查询结果示例： ```json [ { "CustomerName":"Tailspin Toys (Head Office)", "PrimaryContact":"Waldemar Fisar", "AlternateContact":"Laimonis Berzins", "PhoneNumber":"(308) 555-0100" }, { "CustomerName":"Tailspin Toys (Sylvanite, MT)", "PrimaryContact":"Lorena Cindric", "AlternateContact":"Hung Van Groesen", "PhoneNumber":"(406) 555-0100" } ] ``` ##### 2.3 工作原理 AUTO 模式会根据查询中 FROM 和 SELECT 子句所涉及的表自动确定 JSON 格式。如果查询基于单个表，如上述示例中的 `Website.Customers` 表，将返回简单的 JSON 输出。默认情况下，结果会在 SSMS 网格中显示为超链接，点击后会在 XML 编辑器中打开，但显示的是未格式化的单行字符串。对于小的 JSON 结果，可以手动格式化；但对于大的 JSON，手动格式化会非常耗时。 可以使用一些在线工具来格式化 JSON，例如 JSONFormatter（[https://jsonformatter.curiousconcept.com/](https://jsonformatter.curiousconcept.com/)），操作步骤如下： 1. 从 XML 编辑器中复制 JSON 结果。 2. 将其粘贴到 JSONFormatter 的验证窗口。 3. 点击“Process”按钮。 4. 格式化后的有效 JSON 将显示在“Formatted JSON Data”窗口中。 另外，JSON Editor Online（[http://www.jsoneditoronline.org/](http://www.jsoneditoronline.org/)）也是一个可选的格式化工具，但 JSONFormatter 会保留所有提交的 JSON 代码，方便后续查看。 当查询的 FROM 子句涉及多个表时，表和列的别名会影响 JSON 输出。以下是两个示例： - **无表和列别名的查询**： ```sql SELECT db_name() as [Database], sys.schemas.name, sys.objects.name, sys.columns.name FROM sys.objects JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id JOIN ( SELECT TOP (1) o.object_id, count(c.name) [name] FROM sys.columns c JOIN sys.objects o ON c.object_id = o.object_id WHERE type = 'u' GROUP BY o.object_id HAVING COUNT(c.name) < 6 ) countCol ON countCol.object_id = sys.objects.object_id WHERE type = 'u' FOR JSON AUTO; ``` JSON 输出： ```json { "Database":"WideWorldImporters", "name":"Purchasing", "sys.objects":[ { "name":"SupplierCategories", "sys.columns":[ { "name":"SupplierCategoryID" }, { "name":"SupplierCategoryName" }, { "name":"LastEditedBy" }, { "name":"ValidFrom" }, { "name":"ValidTo" } ] } ] } ``` - **有表和列别名的查询**： ```sql SELECT db_name() as [Database], [Schema].name as [SchemaName], [Table].name as [TableName], [Column].name as [ColumnName] FROM sys.objects [Table] JOIN sys.schemas [Schema] on [Table].schema_id = [Schema].schema_id JOIN sys.columns [Column] ON [Column].object_id = [Table].object_id JOIN ( SELECT TOP (1) o.object_id, COUNT(c.name) [name] FROM sys.columns c JOIN sys.objects o ON c.object_id = o.object_id where type = 'u' GROUP BY o.object_id HAVING COUNT(c.name) < 6) countCol ON countCol.object_id = [Table].object_id WHERE type = 'u' FOR JSON AUTO; ``` JSON 输出： ```json { "Database":"WideWorldImporters", "SchemaName":"Purchasing", "Table":[ { "TableName":"SupplierCategories", "Column":[ { "ColumnName":"SupplierCategoryID" }, { "ColumnName":"SupplierCategoryName" }, { "ColumnName":"LastEditedBy" }, { "ColumnName":"ValidFrom" }, { "ColumnName":"ValidTo" } ] } ] } ``` 对比两个输出可以发现，有别名的查询结果更具描述性。 #### 3. 构建 JSON 时处理 NULL 值 ##### 3.1 问题描述 希望在值为 NULL 时保留键元素名称。 ##### 3.2 解决方案 使用 FOR JSON 子句的 `INCLUDE_NULL_VALUES` 选项，当列的值为 NULL 时，JSON 输出中会显示对应的键元素。示例查询如下： ```sql USE [WideWorldImporters]; SELECT TOP (1) [CustomerName] ,[PrimaryContact] ,[AlternateContact] ,[PhoneNumber] FROM [Website].[Customers] where [AlternateContact] IS NOT NULL UNION ALL SELECT TOP (1) [CustomerName] ,[PrimaryContact] ,[AlternateContact] ,[PhoneNumber] FROM [Website].[Customers] where [AlternateContact] IS NULL FOR JSON AUTO, INCLUDE_NULL_VALUES; ``` JSON 输出： ```json [ { "CustomerName":"Tailspin Toys (Head Office)", "PrimaryContact":"Waldemar Fisar", "AlternateContact":"Laimonis Berzins", "PhoneNumber":"(308) 555-0100" }, { "CustomerName":"Eric Torres", "PrimaryContact":"Eric Torres", "AlternateContact":null, "PhoneNumber":"(307) 555-0100" } ] ``` ##### 3.3 工作原理 默认情况下，FOR JSON 子句会忽略具有默认值的元素，因此在 JSON 输出中可能会缺少键元素。如果上述查询不使用 `INCLUDE_NULL_VALUES` 选项，第二个结果中的 `"AlternateContact"` 键将不会显示： ```sql SELECT TOP (1) [CustomerName] ,[PrimaryContact] ,[AlternateContact] ,[PhoneNumber] FROM [Website].[Customers] where [AlternateContact] IS NOT NULL UNION ALL SELECT TOP (1) [CustomerName] ,[PrimaryContact] ,[AlternateContact] ,[PhoneNumber] FROM [Website ```