主页 / dba / 问题 / 198838
Accepted
Zach Smith
Asked: 2018-02-27 08:47:50 +0800 CST

SQL Server 2012 中“FOR JSON”的替代方案？

  • 772

在 SQL Server 2016 中，我可以按照文档Format Query Results as JSON with FOR JSON (SQL Server)JSON中的描述输出查询结果

SELECT * 
FROM x 
FOR JSON PATH

但我发现这在 SQL Server 2012 中不受支持。

JSONSQL Server 2012中是否有将表格数据转换为数据的内置方法？

为了使这种交叉兼容，我希望能够for json path在 SQL Server 2012 中使用。

sql-server sql-server-2012

4 个回答

  1. 不，JSON 支持是 SQL Server 2016 中的新增功能。

    如果要将数据转换为 JSON，您有以下几种选择：

    • 像获取任何其他查询结果一样获取行，并在应用层（如 C#、Java 等）中对其进行转换
    • 在存储过程或函数中构建 T-SQL 逻辑以自己将其转换为 JSON（但请记住，您将无法像 2016 年一样使用“FOR JSON PATH”）
    • 构建 CLR 逻辑来做到这一点（同样，不会与“FOR JSON PATH”兼容）
    • 6
  2. Best Answer

    在 SQL Server 2012 中，您可以使用FOR XML. 就我而言，这是一个可行的替代方案JSON，因为我只是将字符串传递给浏览器。

    • 4

  3. 社区维基回答：

    您可以使用它，但更改您的JSON. 这会将所有结果存储为一项：

    $server = 'Test'
$mainquery = sqlcmd -E -S $server -Q "set nocount on; select name,database_id,compatibility_level from sys.databases" -m 10 -h-1 
$mainquery | convertto-json #-Depth 3

    或者，您可以构建自己的函数，如Phil Factor通过 TSQL 从 SQL Server 查询中生成 JSON 文档中所述。

    • 1

  4. 从 SQL Server 2012 构建 JSON 数据的工作解决方案

        DECLARE @TableName NVARCHAR(50)
    DECLARE @TempTableColumns TABLE (
        [Object_Id] NVARCHAR(100),
        [TableName] NVARCHAR(100),
        [ColumnName] NVARCHAR(100),
        [ColumnSequence] INT,
        [Type] NVARCHAR(100)
    )

    INSERT INTO @TempTableColumns ([Object_Id],[TableName],[ColumnName],[ColumnSequence],[Type])
    SELECT 
        [T].[OBJECT_ID] [Object_Id]
        , [T].[Name]
        , [C].[Name] [ColumnName]
        , [C].[column_id] AS [ColumnSequence]
        , UPPER(CASE 
            WHEN 
                UPPER([ST].[Name]) = 'VARCHAR' OR UPPER([ST].[Name]) = 'NVARCHAR' THEN [ST].[Name] + '(' +  CASE WHEN CONVERT(VARCHAR,[C].[Max_Length]) = -1 THEN 'MAX' ELSE CONVERT(VARCHAR,[C].[Max_Length]) END + ')' 
            ELSE [ST].[Name] END) [Type]
        FROM SYS.COLUMNS [C] 
        INNER JOIN SYS.TYPES [ST] ON [C].[System_Type_Id] = [St].[System_Type_Id]
        INNER JOIN SYS.TABLES [T] ON [C].[OBJECT_ID] = [T].[OBJECT_ID] 
        WHERE [ST].[Name] <> 'sysname'
    ORDER BY [T].[Name], [C].[column_id]
    
    SELECT 
        [T1].[TableName], 
        REPLACE('SELECT ROW_NUMBER() OVER (ORDER BY ' + (SELECT T3.ColumnName FROM @TempTableColumns T3 WHERE T3.TableName = T1.TableName AND T3.ColumnSequence = 1) + ' ASC) SequenceNumber, ''{' + 
        STUFF(( 
            SELECT '"' + T2.ColumnName + '": '' + CASE WHEN ' + T2.ColumnName + ' IS NULL THEN ''NULL'' ELSE ''"'' + CONVERT(NVARCHAR(MAX), ' + T2.ColumnName + ') + ''"'' END  + '','
                FROM @TempTableColumns AS T2 WHERE T1.TableName = T2.TableName
                FOR XML PATH('')
        ),1, 0,'')  + '}'' DataResults FROM ['+ T1.TableName +'] WITH (NOLOCK)', ',}','}') AS [Query]
    
    FROM @TempTableColumns [T1]
    WHERE T1.TableName >= @TableName OR @TableName = ''
    GROUP BY [T1].[TableName]

    查询的输出将返回 Select 语句，如下所示：

    SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) SequenceNumber, '{"ID": ' + CASE WHEN ID IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ID) + '"' END  + ',"DatabaseName": ' + CASE WHEN DatabaseName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), DatabaseName) + '"' END  + ',"SchemaName": ' + CASE WHEN SchemaName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), SchemaName) + '"' END  + ',"ObjectName": ' + CASE WHEN ObjectName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ObjectName) + '"' END  + ',"ObjectType": ' + CASE WHEN ObjectType IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ObjectType) + '"' END  + ',"IndexName": ' + CASE WHEN IndexName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), IndexName) + '"' END  + ',"IndexType": ' + CASE WHEN IndexType IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), IndexType) + '"' END  + ',"StatisticsName": ' + CASE WHEN StatisticsName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), StatisticsName) + '"' END  + ',"PartitionNumber": ' + CASE WHEN PartitionNumber IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), PartitionNumber) + '"' END  + ',"ExtendedInfo": ' + CASE WHEN ExtendedInfo IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ExtendedInfo) + '"' END  + ',"Command": ' + CASE WHEN Command IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), Command) + '"' END  + ',"CommandType": ' + CASE WHEN CommandType IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), CommandType) + '"' END  + ',"StartTime": ' + CASE WHEN StartTime IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), StartTime) + '"' END  + ',"EndTime": ' + CASE WHEN EndTime IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), EndTime) + '"' END  + ',"ErrorNumber": ' + CASE WHEN ErrorNumber IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ErrorNumber) + '"' END  + ',"ErrorMessage": ' + CASE WHEN ErrorMessage IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ErrorMessage) + '"' END  + '}' DataResults FROM [CommandLog] WITH (NOLOCK)

    当您运行从上面返回的单个查询时。会给你如下输出：

    
{"ID": "79143","DatabaseName": "msdb","SchemaName": NULL,"ObjectName": NULL,"ObjectType": NULL,"IndexName": NULL,"IndexType": NULL,"StatisticsName": NULL,"PartitionNumber": NULL,"ExtendedInfo": NULL,"Command": "DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'G:\Backup\AWS19WVSQL072\msdb\FULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)","CommandType": "xp_create_subdir","StartTime": "Jul 18 2020  1:00AM","EndTime": "Jul 18 2020  1:00AM","ErrorNumber": "0","ErrorMessage": NULL}

{"ID": "79144","DatabaseName": "msdb","SchemaName": NULL,"ObjectName": NULL,"ObjectType": NULL,"IndexName": NULL,"IndexType": NULL,"StatisticsName": NULL,"PartitionNumber": NULL,"ExtendedInfo": NULL,"Command": "BACKUP DATABASE [msdb] TO DISK = N'G:\Backup\AWS19WVSQL072\msdb\FULL\AWS19WVSQL072_msdb_FULL_20200718_010056.bak' WITH CHECKSUM, NO_COMPRESSION","CommandType": "BACKUP_DATABASE","StartTime": "Jul 18 2020  1:00AM","EndTime": "Jul 18 2020  1:01AM","ErrorNumber": "0","ErrorMessage": NULL}
    • -1

相关问题