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

Question

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

loop through a series of rows
read the results into variables
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?

The SQL you posted won't even compile. Please post the whole schema of testtable. It seems like maybe your example is not 100% 1-1 to what the real code is. My guess is that you're trying to update testtable, setting testRow to the json of the other fields in that same row? — squillman
– squillman, Commented Oct 26, 2020 at 17:11
sorry, those were meant to be two different tables, i've updated the post to reflect that — icefalcn
– icefalcn, Commented Oct 26, 2020 at 17:18

squillman · Accepted Answer · 2020-10-26 18:34:11Z

8

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.

edited Oct 26, 2020 at 18:34

answered Oct 26, 2020 at 16:58

squillman

13.7k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

icefalcn Over a year ago

also, for clarity, you should probably remove testRow from @sourceTable

Ishimwe Aubain Consolateur · Accepted Answer · 2021-12-07 14:17:49Z

7

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

answered Dec 7, 2021 at 14:17

Ishimwe Aubain Consolateur

1,08712 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related