SQL Server表转json [英] SQL Server table to json

问题描述

我希望提取表的某些列(Col1和2)并以JSON格式放置，并在每个节点中编写一些硬编码的JSON，就像这样.

I am looking to pull some columns (Col1 and 2) of a table and put in JSON format and also write some hardcoded JSON in each node, like this.

{"col1":"xxxx"，"col2":"xxxx"，"hardcodedString":"xxxx"， "hardcodedString":"xxxx"，"hardcodedString":"xxxx"， "hardcodedString":"xxxx"，"hardcodedString":"xxxx"}，

{ "col1":"xxxx", "col2":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx"},

我找到了以下git脚本，它创建了一个应该生成JSON的SP，但是当我按要求执行时，我得到成功完成命令"

I found the following git script, it creates a SP that should generate JSON but when i executed as required i get 'Commands Completed Succesfully'

有什么想法可以输出输出，或者是否确实有更好的方法来实现JSON?

Any ideas where the output is going or indeed if a better way to acheive my JSON?

create procedure [dbo].[GetJSON] (
    @schema_name varchar(50),
    @table_name varchar(50),
    @registries_per_request smallint = null
)
as
begin
    if ( ( select count(*) from information_schema.tables where table_schema = @schema_name and table_name = @table_name ) > 0 )
    begin
        declare @json varchar(max),
            @line varchar(max),
            @columns varchar(max),
            @sql nvarchar(max),
            @columnNavigator varchar(50),
            @counter tinyint,
            @size varchar(10)

        if (@registries_per_request is null)
        begin
            set @size = ''
        end
        else
        begin
            set @size = 'top ' + convert(varchar, @registries_per_request)
        end
        set @columns = '{'

        declare schemaCursor cursor for
            select column_name
            from information_schema.columns
            where table_schema = @schema_name
            and table_name = @table_name
        open schemaCursor

        fetch next from schemaCursor into @columnNavigator

        select @counter = count(*)
        from information_schema.columns
        where table_schema = @schema_name
        and table_name = @table_name

        while @@fetch_status = 0
        begin
            set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + '''''''
            set @counter = @counter - 1
            if ( 0 != @counter )
            begin
                set @columns = @columns + ','
            end

            fetch next from schemaCursor into @columnNavigator
        end 

        set @columns =  @columns + '}'

        close schemaCursor
        deallocate schemaCursor

        set @json = '['

        set @sql = 'select ' + @size + '''' + @columns + ''' as json into tmpJsonTable from [' + @schema_name + '].[' + @table_name + ']'
        exec sp_sqlexec @sql

        select @counter = count(*) from tmpJsonTable

        declare tmpCur cursor for
            select * from tmpJsonTable
        open tmpCur

        fetch next from tmpCur into @line

        while @@fetch_status = 0
        begin
            set @counter = @counter - 1
            set @json = @json + @line
            if ( 0 != @counter )
            begin
                set @json = @json + ','
            end

            fetch next from tmpCur into @line
        end

        set @json = @json + ']'

        close tmpCur
        deallocate tmpCur
        drop table tmpJsonTable

        select @json as json
    end
end

推荐答案

我真的不建议这样做，在应用程序层中有很多更好的方法可以做到这一点，但是以下内容避免了循环，并且不那么冗长比您目前的方法:

I wouldn't really advise it, there are much better ways of doing this in the application layer, but the following avoids loops, and is a lot less verbose than your current method:

CREATE PROCEDURE dbo.GetJSON @ObjectName VARCHAR(255), @registries_per_request smallint = null
AS
BEGIN
    IF OBJECT_ID(@ObjectName) IS NULL
        BEGIN
            SELECT Json = '';
            RETURN
        END;

    DECLARE @Top NVARCHAR(20) = CASE WHEN @registries_per_request IS NOT NULL 
                                    THEN 'TOP (' + CAST(@registries_per_request AS NVARCHAR) + ') ' 
                                    ELSE '' 
                                END;

    DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + @Top + '* INTO ##T ' + 
                                'FROM ' + @ObjectName;

    EXECUTE SP_EXECUTESQL @SQL;

    DECLARE @X NVARCHAR(MAX) = '[' + (SELECT * FROM ##T FOR XML PATH('')) + ']';


    SELECT  @X = REPLACE(@X, '<' + Name + '>', 
                    CASE WHEN ROW_NUMBER() OVER(ORDER BY Column_ID) = 1 THEN '{'
                         ELSE '' END + Name + ':'),
            @X = REPLACE(@X, '</' + Name + '>', ','),
            @X = REPLACE(@X, ',{', '}, {'),
            @X = REPLACE(@X, ',]', '}]')
    FROM    sys.columns
    WHERE   [Object_ID] = OBJECT_ID(@ObjectName)
    ORDER BY Column_ID;

    DROP TABLE ##T;

    SELECT  Json = @X;

END

我将您的两部分对象名称(@schema和@table)更改为仅接受完整的对象名称.

N.B. I've changed your two part object name (@schema and @table) to just accept the full object name.

SQL小提琴示例

Example on SQL Fiddle

想法是基本上使用SQL-Server中的XML扩展将表转换为XML，然后只需将开始标记替换为{ColumnName:，将结束标记替换为,.然后，它需要再进行两次替换，才能停止在每行的最后一列中添加右括号，并从JSON字符串中删除最后一个,.

The idea is to basically use the XML extension within SQL-Server to turn the table into XML, then just replace the start tags with {ColumnName: and the end tags with ,. It then requires two more replaces to stop add the closing bracket to the last column of each row, and the remove the final , from the JSON string.

这篇关于SQL Server表转json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！