将SQLServer结果转换为JSON
In my article, , we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes
phpjson格式化输出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
在我的⽂章《 ，我们介绍了可⽤于将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：
A breakdown of supported data types is shown in Table1
表1显⽰了⽀持的数据类型的细分
SQL Se r v e r D ata T yp e JSON D ata T yp e
char, nchar, varchar, nvarchar, date, datetime, datetime2, time, datetimeoffset,
string
uniqueidentifier, money
int, bigint, float, decimal, numeric number
Bit Boolean
varbinary, binary, image, timestamp, rowversion BASE64-encoded string
SQL Se r v e r数据类型JSON数据类型
char，nchar，varchar，nvarchar，⽇期，datetime，datetime2，时
串
间，datetimeoffset，uniqueidentifier，货币
int，bigint，float，⼗进制，数字数
位布尔型
varbinary，binary，image，timestamp，rowversion 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 the purposes of this discussion, I will be using . As can be seen in Figure 3, the JSON output from Figure 2 is now properly formatted.
因此，建议您在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 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 scri
pt 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的对象。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。