json - 使用 FOR JSON 和 CTE 并将其存储在变量中

Question

如何从使用公用表表达式 (CTE) 的选择语句中使用 FOR JSON 在 T-SQL 中生成 JSON，然后将其存储在局部变量中？如果没有 CTE，它可能看起来像这样:

DECLARE @var NVARCHAR(MAX) = (
    SELECT  x.Val
    FROM (VALUES (1)) AS x(Val)
    FOR JSON AUTO
)
SELECT @var

但如果像下面这样使用 CTE，语法会怎样？

WITH y AS (
    SELECT  Val
    FROM (VALUES (1)) AS _(Val)
)
SELECT  x.Val, y.Val ValY
FROM (VALUES (1)) AS x(Val)
JOIN y ON y.Val = x.Val
FOR JSON AUTO

用 DECLARE @var NVARCHAR(MAX) = ( ... ) 包围是行不通的。

Answer 1

可能是这样的？

DECLARE @json NVARCHAR(MAX);

WITH y AS (
    SELECT  Val
    FROM (VALUES (1)) AS _(Val)
)
SELECT @json=
(
    SELECT  x.Val, y.Val ValY
    FROM (VALUES (1)) AS x(Val)
    JOIN y ON y.Val = x.Val
    FOR JSON AUTO
);

SELECT @json;

此外，您可以将 CTE 作为子查询而不是“WITH”。 CTE 只不过是“语法糖”，实际上可以避免重复的子查询并使您的 SQL 更易读。您可以将其用于相同的目的:

DECLARE @json NVARCHAR(MAX)=
(
    SELECT  x.Val, y.Val ValY
    FROM (VALUES (1)) AS x(Val)
    JOIN (
            SELECT  Val
            FROM (VALUES (1)) AS _(Val)
         ) y ON y.Val = x.Val
    FOR JSON AUTO
);

SELECT @json