将SQLServer结果转换为JSON|江阴雨辰互联

2023年7月13日发(作者：)

将SQLServer结果转换为JSONIn my article, , we had a look at available T-SQL options for converting JSON data into rows and columns for the purposesof populating a SQL Server based data warehouse. The increased popularity of JSON in modern web applications maycreate a requirement for data teams to expose some of their data to client applications (i.e. reporting tools, web servicesetc.) in a JSON format. In this article we take a look at how such a requirement can be implemented by data teams usingSQL Server 2016 FOR JSON clause在我的⽂章《，我们介绍了可⽤于将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 isimportant that we take note of the supported data types. SQL Server data stored in the following data types cannot beconverted into JSON:像SQL Server中的许多功能⼀样，有使⽤它们的条款和条件，JSON也不例外。因此，重要的是要注意⽀持的数据类型。⽆法将以以下数据类型存储SQL Server数据转换为JSON：A breakdown of supported data types is shown in

Table1

表1显⽰了⽀持的数据类型的细分SQL Server Data Typechar, nchar, varchar, nvarchar, date, datetime, datetime2, time, datetimeoffset,uniqueidentifier, moneyint, bigint, float, decimal, numericBitvarbinary, binary, image, timestamp, rowversionJSON Data TypestringnumberBooleanBASE64-encoded stringSQL Server数据类型char，nchar，varchar，nvarchar，⽇期，datetime，datetime2，时间，datetimeoffset，uniqueidentifier，货币int，bigint，float，⼗进制，数字位varbinary，binary，image，timestamp，rowversionJSON数据类型串数布尔型BASE64编码的字符串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 thepurposes of this discussion, I will be using . As can be seen in

Figure 3, the JSON output from

Figure 2 is now properlyformatted.因此，建议您在SQL Server中⾃学JSON的同时，还要找到⼀个JSON编辑器。为了便于讨论，我将使⽤。

如图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 JSONAUTO clause at the end of your SELECT statement. In this mode, the structure of the JSON output is determined by acombination of the order of columns in your SELECT statement as well as the tables that are referenced by theSELECT statement.

Figure 4 shows a T-SQL statement that converts the results from our fictitious Fruit Sales datamart 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] salesFOR JSON AUTO

Figure 4图4The results of the above scripts are shown in below in which a single array (represented by square bracket) wasreturned with rows held as objects (represented by curly braces).上⾯脚本的结果显⽰在下⾯，其中返回了单个数组（由⽅括号表⽰），其中⾏作为对象（由花括号表⽰）。Figure 5图5The sample script provided in

Figure 4 does not fully demonstrate the role of column and table ordering in FOR JSONAUTO clause as only a single table was used.

Figure 6 shows the revised script with a join to another fictitiouscustomer 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] salesLEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust ON sales.[Customer] = cust.[Customer]FOR JSON AUTO

Figure 6图6The execution of the above script results into an output that is shown in

Figure 7. You will now notice that anotherchild array (with its own objects) labelled

cust appears in the output. The child array represents the informationretrieved from the customer dimension.上⾯脚本的执⾏将导致输出，

如图7所⽰。现在，您将注意到另⼀个标记为cust的⼦数组（具有其⾃⼰的对象）出现在输出中。⼦数组表⽰从客户维度检索的信息。Figure 7图7However, when we change the column order of the SELECT statement such that it begins with a column from thecustomer dimension as shown in

Figure 8, we get a different output than the one in

Figure 7 in that the child array isnow 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] salesLEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust ON sales.[Customer] = cust.[Customer]FOR JSON AUTO

Figure 8图8Figure 9图9Therefore, although using the AUTO mode is convenient, it often returns an inconsistent output whose ordering issubject to change based on the column ordering in the SELECT statement. To ensure that your JSON results areconsistent, 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 outputbased on the ordering of columns in your SELECT statement.当您不使⽤点语法使⽤它时，它的⼯作⽅式类似于AUTO模式，因为它将根据SELECT语句中列的顺序⽣成JSON输出。

SELECT

Figure 10图10However, as it can be seen in

Figure 11, the PATH mode doesn’t automatically group information from joined tablesinto child arrays. In fact, all columns from the two tables are shown in the same root level.但是，

如图11所⽰，PATH模式不会⾃动将联接表中的信息分组到⼦数组中。实际上，两个表中的所有列都显⽰在同⼀根级别。Figure 11图11To organise the JSON output into child arrays, you will have to use the dot syntax as shown in

Figure 12. The labelbefore 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 [ Nr],sales.[Transaction Date] AS [ction Date] ,sales.[Fruit] AS [],sales.[Quantity] AS [ty] ,sales.[Customer] AS [er],sales.[MOP] AS [] ,sales.[Account Number] AS [t Number],cust.[Name] AS [] ,cust.[DOB] AS [],cust.[Gender] AS []

FROM [selectSIFISOBlogs].[DIM].[FruitSales] salesLEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust ON sales.[Customer] = cust.[Customer]FOR JSON PATH

Figure 12图12The execution results of

Figure 12 are shown below.图12的执⾏结果如下所⽰。Figure 13图13You can still organise your JSON PATH output into child arrays by converting your script into a nested join as shownin

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] salesFOR JSON PATH

Figure 14图14Figure 15图15JSON⼦句的选项 (Options FOR JSON Clause)Both AUTO and PATH modes allow you to specify additional options such as

ROOT,

INCLUDE_NULL_VALUES andWITHOUT_ARRAY_和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

选项⽤于将标签分配给顶层阵列。

图16显⽰了ROOT选项的应⽤。

SELECT

FROM [selectSIFISOBlogs].[DIM].[FruitSales] salesLEFT JOIN [selectSIFISOBlogs].[DIM].[Customer] cust ON sales.[Customer] = cust.[Customer]FOR JSON PATH, ROOT ('TOP_LEVEL')

Figure 16图16As it can be seen in

Figure 17, our main array is now titled

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

INCLUDE_NULL_VALUESThe default behaviour when using FOR JSON clause is that NULL values will not be included in your JSON can be overridden by specifying the

INCLUDE_NULL_VALUES option. To illustrate this point I have added adummy fruit transaction in my SELECT statement as shown in

Figure 18. You will notice that the last four valuesin 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] salesUNIONSELECT 12,20990101,'Dummy Fruit',NULL,NULL,NULL,NULLFOR JSON AUTO

Figure 18图18The output of this statement is shown in

Figure 19 in which the last 4 values that were NULL are not included inthe JSON output.该语句的输出如图19所⽰，其中JSON输出中不包括最后4个NULL值。Figure 19图19To override this behaviour, you just need to include

INCLUDE_NULL_VALUES option as shown in

Figure 20.要覆盖此⾏为，只需包含INCLUDE_NULL_VALUES选项，

如图20所⽰。 SELECT

Figure 20图20As it can now be seen in

Figure 21, after including the

INCLUDE_NULL_VALUES option, our JSON output includesNULL values.现在如图21所⽰，在包含INCLUDE_NULL_VALUES选项之后，我们的JSON输出包括NULL值。Figure 21图213. WITHOUT_ARRAY_WRAPPERWITHOUT_ARRAY_WRAPPERBy default every FOR JSON clause returns JSON data wrapped around square brackets – also known as, anarray. There are instances whereby you want the square brackets excluded from the output because you maywant to concatenate two or more JSON data.

Figure 22 shows the application of theWITHOUT_ARRAY_WRAPPER option.默认情况下，每个FOR JSON⼦句都返回包装在⽅括号中的JSON数据-也称为数组。在某些情况下，您希望将⽅括号从输出中排除，因为您可能希望连接两个或多个JSON数据。

图22显⽰了WITHOUT_ARRAY_WRAPPER选项的应⽤。

SELECT

Figure 22图22The results of the above scripts are shown below.以上脚本的结果如下所⽰。Figure 23图23Finally, you would have noticed in

Figure 22 that I used a TOP clause to limit my selection into a single row. This isbecause when you use the

WITHOUT_ARRAY_WRAPPER option against a dataset that has more than one row, you willrun into JSON validation error (shown in below) caused by the missing square brackets. Thus, be careful in the way youuse 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)参考 (Reference)1.

发布者：admin，转转请注明出处：http://www.yc00.com/web/1689249190a225731.html