时间戳

首页 > 解决方案 > SQL Server 的 JSON 输出格式

json - SQL Server 的 JSON 输出格式

问题描述

我昨天发布了一个关于这个的问题，解决方案主义者非常有帮助地回答了这个问题，但我现在有一个类似的后续问题。

我想从 JSON 中获取两组数据。

我有三个数据集，一个是标题，一个是一组客户记录，最后一个是错误对象。我想要尝试得到的结构是：Header Customers Error

但我得到的是：标题客户，错误客户，错误

现在在这个例子中，错误是空的，所以我很高兴错误被忽略，因为它是空的，或者作为一个空对象返回，但我想要一个，而不是每个客户一个。

这是我目前得到的：

select isjson('{"APIResult":[{"ID":200,
               "Status_Message":"Success",
               "Developer_Message":"Customers found",
           "User_Message":"Customers found",
           "Return_Code":0,
           "Customer":[{"ID":"A6B10FA0-85AD-422D-8A7E-07EEA541593E",
                        "First_Name":"Bbb",
                        "Paternal_Last_Name":"Bbbbb",
                        "Email_Address":"bbbb@example.com",
                        "Error":[{}]
                       },
                       {"ID":"91EE2FD6-2C40-4CFD-ABB7-2CDAE3624487",
                        "First_Name":"Aaaa",
                        "Paternal_Last_Name":"Aaaaa",
                        "Email_Address":"aaaaa@example.com",
                        "Error":[{}]
                       }]
           }]
}')

这就是我想要得到的：

select isjson('{"APIResult":[{"ID":200,
            "Status_Message":"Success",
            "Developer_Message":"Customers found",
            "User_Message":"Customers found",
            "Return_Code":0,
            "Customer":[{"ID":"A6B10FA0-85AD-422D-8A7E-07EEA541593E",
                         "First_Name":"Bbb",
                         "Paternal_Last_Name":"Bbbbb",
                         "Email_Address":"bbbb@example.com"
                        },
                        {"ID":"91EE2FD6-2C40-4CFD-ABB7-2CDAE3624487",
                         "First_Name":"Aaaa",
                         "Paternal_Last_Name":"Aaaaa",
                         "Email_Address":"aaaaa@example.com"
                        }],
            "Error":[{}]

           }]
}')

两者都是有效的 JSON（我这次已经测试过了，如您所见），但我不知道如何获得第二种格式。

这是我正在运行以获取输出的查询：

declare @returncode int = 0
       ,@VerboseMsg nvarchar(4000) = 'Customers found'
       ,@DisplayMsg nvarchar(4000) = 'Customers found'
CREATE TABLE #Customers (CustomerID UNIQUEIDENTIFIER DEFAULT(NEWID()), FirstName NVARCHAR(50), PaternalLastName NVARCHAR(50), EmailAddress NVARCHAR(250))
INSERT INTO #Customers (FirstName, PaternalLastName, EmailAddress)
VALUES ('Aaaa', 'Aaaa', 'aaaa@example.com'), ('Bbbb', 'Bbbb', 'bbbb@example.com')

SELECT st.APIStatus AS 'ID'
      ,st.StatusMessage AS 'Status_Message'
      ,@VerboseMsg AS 'Developer_Message'
      ,@DisplayMsg AS 'User_Message'
      ,st.ReturnCode AS 'Return_Code'
      ,[Customer].CustomerID AS 'ID'
      ,[Customer].FirstName AS 'First_Name'
      ,[Customer].PaternalLastName AS 'Paternal_Last_Name' 
      ,[Customer].EmailAddress AS 'Email_Address'
      ,[Error].ErrorCode AS 'Error_Code'
      ,[Error].ErrorMsg AS 'Error_Message'
FROM (SELECT 200 AS APIStatus, 'Success' AS StatusMessage, @ReturnCode AS ReturnCode) st
LEFT JOIN (SELECT CustomerID 
                 ,FirstName 
                 ,PaternalLastName 
                 ,EmailAddress
                 ,@ReturnCode AS ReturnCode
           FROM #Customers
          ) As [Customer]
ON st.ReturnCode = [Customer].ReturnCode
LEFT JOIN (SELECT @ReturnCode AS ErrorCode, @DisplayMsg AS ErrorMsg WHERE @returncode != 0) AS [Error]
ON [Error].ErrorCode = st.ReturnCode
FOR JSON AUTO, ROOT('APIResult')

是不是我能得到这个输出的唯一方法就是自己把东西拼接在一起？JSON 对我来说仍然很新，我正在努力掌握它的构成。

标签： jsonsql-server

解决方案

在此设置中，我将在返回代码之后放置错误代码，并将使用带有 JSON PATH 的子查询，如下所示：

SELECT st.APIStatus AS 'ID'
      ,st.StatusMessage AS 'Status_Message'
      ,@VerboseMsg AS 'Developer_Message'
      ,@DisplayMsg AS 'User_Message'
      ,st.ReturnCode AS 'Return_Code'

      , (SELECT @ReturnCode AS ErrorCode, @DisplayMsg AS ErrorMsg  FOR JSON PATH) AS [Error]

      ,[Customer].CustomerID AS 'ID'
      ,[Customer].FirstName AS 'First_Name'
      ,[Customer].PaternalLastName AS 'Paternal_Last_Name' 
      ,[Customer].EmailAddress AS 'Email_Address'
FROM (SELECT 200 AS APIStatus, 'Success' AS StatusMessage, @ReturnCode AS ReturnCode) st
LEFT JOIN (SELECT CustomerID 
                 ,FirstName 
                 ,PaternalLastName 
                 ,EmailAddress
                 ,@ReturnCode AS ReturnCode
           FROM #Customers
          ) As [Customer]
ON st.ReturnCode = [Customer].ReturnCode -- why?
FOR JSON AUTO, ROOT('APIResult')

请注意，如果您在 Error 中没有任何记录，它将被省略，但您可以在这种情况下使用 null，我还注意到您使用了两次 @ReturnCode，我不知道这是故意还是错误。

推荐阅读