将SQL Server结果转换为JSON

In my article, Warehousing JSON Formatted Data in SQL Server 2016, we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes of populating a SQL Server based data warehouse. The increased popularity of JSON in modern web applications may create a requirement for data teams to expose some of their data to client applications (i.e. reporting tools, web services etc.) in a JSON format. In this article we take a look at how such a requirement can be implemented by data teams using SQL Server 2016 FOR JSON clause

在我的文章《 在SQL Server 2016中存储JSON格式的数据》中 ，我们介绍了可用于将JSON数据转换为行和列的T-SQL选项，以填充基于SQL Server的数据仓库。 JSON在现代Web应用程序中的日益普及可能会要求数据团队以JSON格式向客户端应用程序（即报告工具，Web服务等）公开其某些数据。 在本文中，我们将研究数据团队如何使用SQL Server 2016 FOR JSON子句来实现这种要求

SQL Server到JSON支持的数据类型 (SQL Server to JSON Supported Data Types)

Like many of the features in SQL Server, there are terms and conditions to using them and JSON is no different. Thus, it is important that we take note of the supported data types. SQL Server data stored in the following data types cannot be converted into JSON:

像SQL Server中的许多功能一样，有使用它们的条款和条件，JSON也不例外。 因此，重要的是要注意支持的数据类型。 无法将以以下数据类型存储SQL Server数据转换为JSON：

• Geometry

  几何

• Geography

  地理

• Common Language Runtime (CLR)

  通用语言运行时（CLR）

A breakdown of supported data types is shown in Table1  

表1显示了支持的数据类型的细分

FOR JSON T-SQL子句 (FOR JSON T-SQL Clause)

Although SQL Server’s support for XML allowed for graphical representation of the data via an editor (shown in Figure 1), attempting to view JSON data via an editor may be frustrating as JSON data is shown as an unformatted single row.

尽管SQL Server对XML的支持允许通过编辑器以图形方式表示数据（如图1所示），但尝试通过编辑器查看JSON数据可能会令人沮丧，因为JSON数据显示为未格式化的单行。

It is therefore advisable that whilst you teach yourself JSON in SQL Server that you find yourself a JSON editor. For the purposes of this discussion, I will be using JSONFormatter from curiousconcept.com. As can be seen in Figure 3, the JSON output from Figure 2 is now properly formatted.

因此，建议您在SQL Server中自学JSON的同时，还要找到一个JSON编辑器。 为了便于讨论，我将使用curiousconcept.com的JSONFormatter 。 如图3所示 ， 图2的JSON输出现已正确格式化。

There are two ways that relational results can be converted into JSON, namely, the AUTO and PATH options.

关系结果可以通过两种方式转换为JSON，即AUTO和PATH选项。

1. Convert Results Using AUTO Mode

   使用自动模式转换结果

   This is the simplest way to convert relational data into a JSON format as all that you have to do is to add FOR JSON AUTO clause at the end of your SELECT statement. In this mode, the structure of the JSON output is determined by a combination of the order of columns in your SELECT statement as well as the tables that are referenced by the SELECT statement. Figure 4 shows a T-SQL statement that converts the results from our fictitious Fruit Sales data mart into JSON.

   这是将关系数据转换为JSON格式的最简单方法，因为您要做的就是在SELECT语句的末尾添加FOR JSON AUTO子句。 在这种模式下，JSON输出的结构由SELECT语句中的列顺序以及SELECT语句所引用的表的组合决定。 图4显示了一条T-SQL语句，该语句将虚拟的Fruit Sales数据集市的结果转换为JSON。

    
   SELECT 	
   	sales.[Item Nr],sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
   	,sales.[Customer],sales.[MOP],sales.[Account Number]
   FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
   FOR JSON AUTO
    
   

   Figure 4 图4

   The results of the above scripts are shown in below in which a single array (represented by square bracket) was returned with rows held as objects (represented by curly braces).

   上面脚本的结果显示在下面，其中返回了单个数组（由方括号表示），其中行作为对象（由花括号表示）。

   
   

   
   

   Figure 5 图5

   The sample script provided in Figure 4 does not fully demonstrate the role of column and table ordering in FOR JSON AUTO clause as only a single table was used. Figure 6 shows the revised script with a join to another fictitious customer lookup dimension that stores additional information regarding customers that have purchased fruits.

   图4中提供的示例脚本没有完全演示FOR JSON AUTO子句中列和表顺序的作用，因为仅使用了一个表。 图6显示了修改后的脚本，该脚本与另一个虚构的客户查找维度结合在一起，该维度存储有关已购买水果的客户的其他信息。

    
   SELECT 	
   	sales.[Item Nr],sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
   	,sales.[Customer],sales.[MOP],sales.[Account Number],cust.[Name]
   	,cust.[DOB],cust.[Gender]
   FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
   LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust
   	ON sales.[Customer] = cust.[Customer]
   FOR JSON AUTO
    
   

   Figure 6 图6

   The execution of the above script results into an output that is shown in Figure 7. You will now notice that another child array (with its own objects) labelled cust appears in the output. The child array represents the information retrieved from the customer dimension.

   上面脚本的执行将导致输出， 如图7所示。 现在，您将注意到另一个标记为cust的子数组（具有其自己的对象）出现在输出中。 子数组表示从客户维度检索的信息。

   
   

   
   

   Figure 7 图7

   However, when we change the column order of the SELECT statement such that it begins with a column from the customer dimension as shown in Figure 8, we get a different output than the one in Figure 7 in that the child array is now based off the FruitSales dimension.

   但是，当我们更改SELECT语句的列顺序，使其从customer维中的一列开始时， 如图8所示，我们得到的输出与图7中的输出不同，因为子数组现在基于FruitSales维度。

    
   SELECT 	
   	cust.[Name],sales.[Item Nr],sales.[Transaction Date],sales.[Fruit]
   ,sales.[Quantity],sales.[Customer],sales.[MOP],sales.[Account Number]	,cust.[DOB],cust.[Gender]
   FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
   LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust
   	ON sales.[Customer] = cust.[Customer]
   FOR JSON AUTO
    
   

   Figure 8 图8
   
   

   
   

   Figure 9 图9

   Therefore, although using the AUTO mode is convenient, it often returns an inconsistent output whose ordering is subject to change based on the column ordering in the SELECT statement. To ensure that your JSON results are consistent, you will have to make use of the PATH mode.

   因此，尽管使用AUTO模式很方便，但它通常会返回不一致的输出，其输出可能会根据SELECT语句中的列顺序而改变。 为了确保JSON结果一致，您将不得不使用PATH模式。

2. Convert Results Using PATH Mode 使用PATH模式转换结果

   The PATH mode can be used in two ways:

   PATH模式可以以两种方式使用：

   1. Without a dot syntax

      没有点语法

   2. With a dot syntax

      带点语法

   When you are using it without a dot syntax, it works similar to the AUTO mode in that it will generate a JSON output based on the ordering of columns in your SELECT statement.

   当您不使用点语法使用它时，它的工作方式类似于AUTO模式，因为它将根据SELECT语句中列的顺序生成JSON输出。

    
   SELECT 	
   	sales.[Item Nr],sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
   	,sales.[Customer],sales.[MOP],sales.[Account Number],cust.[Name]
   	,cust.[DOB],cust.[Gender]
   FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
   LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust
   	ON sales.[Customer] = cust.[Customer]
   FOR JSON PATH
    
   

   Figure 10 图10

   However, as it can be seen in Figure 11, the PATH mode doesn’t automatically group information from joined tables into child arrays. In fact, all columns from the two tables are shown in the same root level.

   但是， 如图11所示 ，PATH模式不会自动将联接表中的信息分组到子数组中。 实际上，两个表中的所有列都显示在同一根级别。

   
   

   
   

   Figure 11 图11

   To organise the JSON output into child arrays, you will have to use the dot syntax as shown in Figure 12. The label before the dot represents the name of the object – in this case we have two objects named Sales and Cust.

   要将JSON输出组织到子数组中，您将必须使用点语法， 如图12所示。 点之前的标签代表对象的名称–在这种情况下，我们有两个名为Sales和Cust的对象。

    
   SELECT 	
   	sales.[Item Nr] AS [Sales.Item Nr]
   ,sales.[Transaction Date] AS [Sales.Transaction Date]
   	,sales.[Fruit] AS [Sales.Fruit],sales.[Quantity] AS [Sales.Quantity]
   	,sales.[Customer] AS [Sales.Customer],sales.[MOP] AS [Sales.MOP]
   	,sales.[Account Number] AS [Sales.Account Number],cust.[Name] AS [Cust.Name]
   	,cust.[DOB] AS [Cust.DOB],cust.[Gender] AS [Cust.Gender]	
   FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
   LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust
   	ON sales.[Customer] = cust.[Customer]
   FOR JSON PATH
    
   

   Figure 12 图12

   The execution results of Figure 12 are shown below.

   图12的执行结果如下所示。

   
   

   
   

   Figure 13 图13

   You can still organise your JSON PATH output into child arrays by converting your script into a nested join as shown in Figure 14.

   您仍然可以通过将脚本转换为嵌套联接，将JSON PATH输出组织为子数组， 如图14所示。

    
   SELECT 	
   	(
   		SELECT cust.[Name],cust.[DOB],cust.[Gender]
   		FROM [selectSIFISOBlogs].[DIM].[Customer] cust
   		WHERE cust.[Customer] = sales.[Customer]
   		FOR JSON PATH
   	) cust
   	,sales.[Item Nr],sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
   	,sales.[Customer],sales.[MOP],sales.[Account Number]
   FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
   FOR JSON PATH
    
   

   Figure 14 图14
   
   

   
   

   Figure 15 图15

   JSON子句的选项 (Options FOR JSON Clause)

   Both AUTO and PATH modes allow you to specify additional options such as ROOT, INCLUDE_NULL_VALUES and WITHOUT_ARRAY_WRAPPER.

   AUTO和PATH模式都允许您指定其他选项，例如ROOT ， INCLUDE_NULL_VALUES和WITHOUT_ARRAY_WRAPPER 。

   1. ROOT

      根

      The ROOT option is used assign a label to the top-level array. Figure 16 shows the application of the ROOT option.

      ROOT选项用于将标签分配给顶层阵列。 图16显示了ROOT选项的应用。

       
      SELECT 	
      	sales.[Item Nr] AS [Sales.Item Nr],sales.[Transaction Date] AS [Sales.Transaction Date]
      	,sales.[Fruit] AS [Sales.Fruit],sales.[Quantity] AS [Sales.Quantity]
      	,sales.[Customer] AS [Sales.Customer],sales.[MOP] AS [Sales.MOP]
      	,sales.[Account Number] AS [Sales.Account Number],cust.[Name] AS [Cust.Name]
      	,cust.[DOB] AS [Cust.DOB],cust.[Gender] AS [Cust.Gender]	
      FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
      LEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust
      	ON sales.[Customer] = cust.[Customer]
      FOR JSON PATH, ROOT ('TOP_LEVEL')
       
      

      Figure 16 图16

      As it can be seen in Figure 17, our main array is now titled TOP_LEVEL.

      从图17中可以看到，我们的主数组现在名为TOP_LEVEL 。

      
      

      
      

      Figure 17 图17

   2. INCLUDE_NULL_VALUES 

      INCLUDE_NULL_VALUES

      The default behaviour when using FOR JSON clause is that NULL values will not be included in your JSON output. This can be overridden by specifying the INCLUDE_NULL_VALUES option. To illustrate this point I have added a dummy fruit transaction in my SELECT statement as shown in Figure 18. You will notice that the last four values in my dummy transaction are NULL.

      使用FOR JSON子句时的默认行为是JSON输出中将不包含NULL值。 可以通过指定INCLUDE_NULL_VALUES选项来覆盖它。 为了说明这一点，我在SELECT语句中添加了一个虚拟水果事务， 如图18所示。 您会注意到我的虚拟事务中的最后四个值是NULL。

       
      SELECT 	
      	sales.[Item Nr],sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
      	,sales.[Customer],sales.[MOP],sales.[Account Number]
      FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
      UNION
      SELECT 12,20990101,'Dummy Fruit',NULL,NULL,NULL,NULL
      FOR JSON AUTO
       
      

      Figure 18 图18

      The output of this statement is shown in Figure 19 in which the last 4 values that were NULL are not included in the JSON output.

      该语句的输出如图19所示，其中JSON输出中不包括最后4个NULL值。

      
      

      
      

      Figure 19 图19

      To override this behaviour, you just need to include INCLUDE_NULL_VALUES option as shown in Figure 20.

      要覆盖此行为，只需包含INCLUDE_NULL_VALUES选项， 如图20所示。

       
      SELECT 	
      	sales.[Item Nr],sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
      	,sales.[Customer],sales.[MOP],sales.[Account Number]
      FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
      UNION
      SELECT 12,20990101,'Dummy Fruit',NULL,NULL,NULL,NULL
      FOR JSON AUTO, INCLUDE_NULL_VALUES
       
      

      Figure 20 图20

      As it can now be seen in Figure 21, after including the INCLUDE_NULL_VALUES option, our JSON output includes NULL values.

      现在如图21所示 ，在包含INCLUDE_NULL_VALUES选项之后，我们的JSON输出包括NULL值。

      
      

      
      

      Figure 21 图21

   3. WITHOUT_ARRAY_WRAPPER

      WITHOUT_ARRAY_WRAPPER

      By default every FOR JSON clause returns JSON data wrapped around square brackets – also known as, an array. There are instances whereby you want the square brackets excluded from the output because you may want to concatenate two or more JSON data. Figure 22 shows the application of the WITHOUT_ARRAY_WRAPPER option.

      默认情况下，每个FOR JSON子句都返回包装在方括号中的JSON数据-也称为数组。 在某些情况下，您希望将方括号从输出中排除，因为您可能希望连接两个或多个JSON数据。 图22显示了WITHOUT_ARRAY_WRAPPER选项的应用。

       
      SELECT 	
      	sales.[Item Nr],sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
      	,sales.[Customer],sales.[MOP],sales.[Account Number]
      FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
      FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
       
      

      Figure 22 图22

      The results of the above scripts are shown below.

      以上脚本的结果如下所示。

      
      

      
      

      Figure 23 图23

   Finally, you would have noticed in Figure 22 that I used a TOP clause to limit my selection into a single row. This is because when you use the WITHOUT_ARRAY_WRAPPER option against a dataset that has more than one row, you will run into JSON validation error (shown in below) caused by the missing square brackets. Thus, be careful in the way you use the WITHOUT_ARRAY_WRAPPER option as it may lead to unintended validation errors.

   最后，您会在图22中注意到，我使用了TOP子句将选择范围限制为一行。 这是因为当对具有多行的数据集使用WITHOUT_ARRAY_WRAPPER选项时，由于缺少方括号，您将遇到JSON验证错误（如下所示）。 因此，在使用WITHOUT_ARRAY_WRAPPER选项时要小心，因为它可能导致意外的验证错误。

   
   

   
   

   Figure 24 图24

   资料下载 (Downloads)

   Sample selectSIFISOBlogs database

   样本selectSIFISOBlogs数据库

   参考 (Reference)

   • INCLUDE_NULL_VALUES Option INCLUDE_NULL_VALUES选项
   • JSON Data in SQL Server 2016 SQL Server 2016中的JSON数据
   • WITHOUT_ARRAY_WRAPPER WITHOUT_ARRAY_WRAPPER
   
   
   

翻译自: https://www.sqlshack.com/convert-sql-server-results-json/