SQLtable to JSON with specific format

Question

I have a sql table which needs to be converted into JSON, Basic SELECT * FROM ##T FOR JSON AUTO; does not work here because JSON format is specific. The output should look like this:

[
      {
        "property": "firstname",
        "value": "Codey"
      },
      {
        "property": "lastname",
        "value": "Huang"
      }].

And the another problem is the script needs to be dynamic. It need to work even when a new column will be added to the table.

I found this script on the internet:

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

But the output of this query is:

[{column1:1,column2:row 1}, {column1:2,column2:row 2}, {column1:3,column2:row 3}]

which is not in the required format. I would be glad if someone helped me to handle this problem. Thanks in advance.

Answer 1

You don't need a temp table here, or XML. You can do the whole thing inside the dynamic SQL.

• First create the full JSON object of a single row, inside an APPLY.
• Shred that object using OPENJSON.
• Construct another JSON object containing each column's name and value.
• Reaggregate those objects using STRING_AGG.
• Aggregate the final result using STRING_AGG again, appending [] to make it a valid array.

CREATE OR ALTER PROCEDURE dbo.GetJSON
  @ObjectName sysname,
  @registries_per_request smallint = null
AS

IF OBJECT_ID('dbo.' + QUOTENAME(@ObjectName)) IS NULL
BEGIN
    SELECT Json = '';
    RETURN;
END;

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

DECLARE @SQL NVARCHAR(MAX) = N'
SELECT
  Json = ''['' + STRING_AGG(t.Json, '','') + '']''
FROM (
    SELECT ' + @Top + '
      j2.json
    FROM dbo.' + QUOTENAME(@ObjectName) + ' AS t
    CROSS APPLY (
        SELECT t.*
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS j1(Json)
    CROSS APPLY (
        SELECT
          Json = STRING_AGG(j3.Json, '','')
        FROM OPENJSON(j1.Json) AS j2
        CROSS APPLY (
          SELECT
            property = j2.[key],
            j2.value
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ) AS j3(Json)
    ) AS j2(Json)
) t;
'

EXEC sp_executesql @SQL,
  N'@registries_per_request smallint',
  @registries_per_request = @registries_per_request;

SQL Fiddle

The final result will contain all rows munged together. I doubt this is what you really want, but it fits what you have asked. You should be able to modify this to suit a different requirement.

Note also the use of sysname for the table name, and the use of QUOTENAME to correctly quote it, and the way to pass in a parameter properly to sp_executesql.