SQL Server Retrieving and using the output from a "FOR JSON" query in a variable

ktca8awb  于 9个月前  发布在  其他
关注(0)|答案(3)|浏览(110)

I'm aware of the option to output a query formatted as JSON, like the following example from the from MSDN page:

SELECT name, surname  
FROM emp  
FOR JSON AUTO

There are a lot of samples on how to use the resulting json from apps but my question is, how can I store the resulting json in a varchar variable, let's say to store in another table?

avwztpqn

avwztpqn1#

DECLARE @Json nvarchar(MAX) = (
    SELECT name, surname  
    FROM emp  
    FOR JSON AUTO
);

Dan Guzman replied in the MSDN Forum with this neat solution, which corresponds also to @FDavidov's suggestion in his last comment

aurhwmvo

aurhwmvo2#

A JSON is, in fact, a character string. What makes this character string to be a JSON is the combination of two things:

  1. You refer to it as a JSON (using the correct functions within your environment),
  2. It contains the correct delimiters at the correct locations to comply with the rules of a JSON.

So, if you want to store a JSON in a variable, just assign to the variable the source string and, whenever you want to act on your variable, just remember it is a JSON.

jv2fixgn

jv2fixgn3#

I was looking for a similar thing, but how to use the JSON PATH output data from SQL Server in an application. In this case, in Node.JS (although this concept would be adaptable to other languages). Here's a sample of what I came up with:

var {recordset} = await new sql.Request(primary.dbPool)
.input('primaryID', sql.UniqueIdentifier, primaryID)
.query(`SELECT (
    SELECT MainTable.*,
        (
            SELECT SubTable.* 
            FROM dbo.SubTable AS SubTable WHERE MainTable.Main_RecordID = SubTable.SubTable_Main_RecordID 
            FOR JSON PATH
        ) AS [subItems]
    FROM dbo.MainTable AS MainTable
    WHERE primaryID = @primaryID
    FOR JSON PATH, ROOT('mainItems')
) AS jsonResult;`)
let mainItems = JSON.parse(recordset[0].jsonResult).mainItems

console.log(mainItems)

This avoids it being returned as a long string in a UUID field like:

And is instead something you can use in the application.

相关问题