How to create JSON from variables in SQL SERVER using FOR JSON AUTO

user_4802879 0
Viewed 5576

I'm trying to write a query in SQL Server 2016 where I:

  1. loop through a series of rows
  2. read the results into variables
  3. create JSON objects from those variables to store in my database

This is a basic example of the code I have so far

DECLARE 
    @test1 int,
    @test2 varchar(255)
DECLARE db_update_cursor CURSOR FOR
    SELECT test1, test2
    FROM sourceTable
OPEN db_update_cursor 
FETCH NEXT FROM db_update_cursor INTO @test1, @test2
WHILE @@FETCH_STATUS = 0  
    BEGIN 
    INSERT INTO destinationTable(testRow)
    VALUES (SELECT @test1, @test2 FOR JSON AUTO)
FETCH NEXT FROM db_update_cursor INTO @test1, @test2
END

CLOSE db_update_cursor  
DEALLOCATE db_update_cursor

From this, I am hoping to get a Json object that looks something like this:

[
  {
    "test1": 5,
    "test2": "something"
  }
]

When I try to run this, I am getting the error: "FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name."

What is the best way to implement this workflow?

sql-server sql json
2 Answers

SQL Server can't generate JSON with FOR JSON AUTO unless it has a schema to build the object from. The SELECT in your INSERT statement is just selecting a couple of variable values when trying to generate the JSON. In the scope of the cursor, SQL Server doesn't have any context in which to generate the JSON object(s).

You don't need a cursor for this. See if this will get you to what you want.

DECLARE @sourceTable TABLE (
    test1 int,
    test2 varchar(255)
);

DECLARE @destinationTable TABLE (testRow NVARCHAR(MAX));

INSERT @sourceTable  (test1, test2)
VALUES (5, 'something'),
       (6, 'something else');

WITH r AS (
    SELECT test1, test2, ROW_NUMBER() OVER (ORDER BY test1, test2) AS RowNum
    FROM @sourceTable 
)
INSERT @destinationTable (testRow)
SELECT (SELECT test1, test2 FROM r WHERE RowNum = r2.RowNum FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)
FROM r r2;

SELECT testRow FROM @destinationTable;

The results from this will be:

{"test1":5,"test2":"something"}
{"test1":6,"test2":"something else"}

If you want each row to be in an array, remove WITHOUT_ARRAY_WRAPPER.

The CTE generates an identifier for each row and then the outer query generates the JSON for just that single row. This operates as a single set operation, though, so will greatly outperform a cursor solution as the source data grows. Any time you find yourself using a cursor to iterate through a table to select or update data you should take a step back and look for a set based method instead.

user_758520

I might be late but this can help someone. Change the line

SELECT @test1, @test2 FOR JSON AUTO

To

SELECT @test1, @test2 FOR JSON PATH
user_61288630
Related