从变量中的“FOR JSON”查询中检索和使用输出

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

我知道可以输出一个JSON格式的查询，如来自MSDN页面的以下示例:

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?

关于如何使用来自应用程序的结果json，有很多示例，但我的问题是，如何将结果json存储在varchar变量中，比方说存储在另一个表中?

2 个解决方案

#1

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

丹·古兹曼用这个简洁的解决方案在MSDN论坛上回答，这也符合@FDavidov在他最后的评论中的建议。

#2

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

JSON实际上是一个字符串。使这个字符串成为JSON的原因是两个因素的组合:

You refer to it as a JSON (using the correct functions within your environment),
您将其称为JSON(在您的环境中使用正确的函数)，
It contains the correct delimiters at the correct locations to comply with the rules of a JSON.
它在正确的位置包含正确的分隔符，以符合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.

所以，如果你想在变量中存储JSON，只需将源字符串赋给变量，无论何时你想对变量进行操作，只要记住它是JSON。

#1

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