SQL Server中将查询结果转换为Json格式脚本分享

1. 概述

在SQL Server中，我们通常使用SELECT语句来查询数据。但是，有时我们需要将查询结果转换为Json格式，并将其用于Web API或其他应用程序中。在本文中，我们将介绍如何使用SQL Server中的FOR JSON PATH语句将查询结果转换为Json格式。

2. FOR JSON PATH语句

FOR JSON PATH语句是SQL Server 2016及更高版本中的一个新功能，它允许我们将查询结果转换为Json格式。该语句允许我们将查询结果按照指定的路径结构格式化为Json格式。

2.1 FOR JSON PATH语句的基本用法

FOR JSON PATH语句的基本语法如下：

SELECT columns 
FROM table 
FOR JSON PATH

使用该语句，我们可以将表中的列作为Json键并将行作为Json值进行格式化。以下是一个示例：

SELECT FirstName, LastName, Email 
FROM Customers 
FOR JSON PATH

上述语句将返回一个Json数组，其中包含所有客户的信息。下面是一个示例：

[
  {
    "FirstName": "John",
    "LastName": "Doe",
    "Email": "john.doe@example.com"
  },
  {
    "FirstName": "Jane",
    "LastName": "Doe",
    "Email": "jane.doe@example.com"
  },
  {
    "FirstName": "Bob",
    "LastName": "Smith",
    "Email": "bob.smith@example.com"
  }
]

2.2 使用FOR JSON PATH语句生成嵌套的Json

除了生成一维的Json数组，FOR JSON PATH语句还可以生成嵌套的Json。下面是一个示例：

SELECT c.CustomerID, c.FirstName, c.LastName, j.Jobs 
FROM Customers c 
LEFT JOIN (
    SELECT CustomerID, Jobs = STUFF((
        SELECT ', ' + JobTitle FROM JobHistory 
        WHERE CustomerID = jh.CustomerID 
        ORDER BY StartYear 
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '')
    FROM JobHistory jh 
    GROUP BY CustomerID
) j ON c.CustomerID = j.CustomerID 
FOR JSON PATH;

上述SQL语句使用了子查询和SQL Server的XML PATH和FOR JSON PATH功能来生成嵌套的Json。该语句将返回以下Json：

[
  {
    "CustomerID": 1,
    "FirstName": "John",
    "LastName": "Doe",
    "Jobs": "Software Engineer, Project Manager"
  },
  {
    "CustomerID": 2,
    "FirstName": "Jane",
    "LastName": "Doe",
    "Jobs": "QA Tester, Software Engineer"
  }
]

2.3 带有条件的Json转换

除了基本的Json转换，我们还可以使用条件和分组语句来生成特定的Json。例如，下面的示例使用CASE语句来根据产品的数量生成相应的Json：

SELECT ProductName, 
       Quantity, 
       CASE WHEN Quantity < 50 THEN 'Low' 
            WHEN Quantity BETWEEN 50 AND 100 THEN 'Medium' 
            ELSE 'High' END AS QuantityRange 
FROM Products 
FOR JSON PATH

上述SQL语句使用了CASE语句来根据产品数量生成Json。返回的Json如下：

[
  {
    "ProductName": "Product A",
    "Quantity": 25,
    "QuantityRange": "Low"
  },
  {
    "ProductName": "Product B",
    "Quantity": 75,
    "QuantityRange": "Medium"
  },
  {
    "ProductName": "Product C",
    "Quantity": 150,
    "QuantityRange": "High"
  }
]

3. 将Json保存到文件中

在SQL Server中，我们还可以将生成的Json保存到文件中，以便我们在需要时使用。以下是一个示例：

DECLARE @json NVARCHAR(MAX)
SET @json = (
    SELECT FirstName, LastName, Email 
    FROM Customers 
    FOR JSON PATH
)
SELECT @json
SELECT @json AS JsonOutput
    INTO dbo.JsonTable

上述SQL语句使用SELECT语句和JSON_PATH功能将查询结果生成为Json，然后使用SELECT INTO语句将Json保存到数据库表中。

4. 结论

在本文中，我们学习了如何使用SQL Server中的FOR JSON PATH语句将查询结果转换为Json格式。我们还学习了如何生成一维和嵌套的Json以及如何使用条件语句生成特定的Json。我们还学习了如何将生成的Json保存到数据库表中以备将来使用。