json - 多个 SELECT 语句合并到一个 JSON 中

Question

我确信这个问题必须在某个地方找到答案，但在我的一生中，无论我如何改变搜索短语，我似乎都找不到任何东西。

我需要从两个完全独立的表中选择数据并将信息导出到JSON。在本例中，它们在每个表中都是 1 条记录。

如果我一次只选择 1 条并导出到 JSON，则它们是 1 条记录，但是当我在 SQL 中连接两条记录然后导出到 JSON 时，它们是 1 条记录数组。

只需 1 条记录 SQL 输入:

DECLARE @Json nvarchar(max) = 
(   
    SELECT 'Data1' AS [Data1], 'Data2' AS [Data2]

    FOR JSON PATH
    , INCLUDE_NULL_VALUES
    , WITHOUT_ARRAY_WRAPPER
);

SELECT @Json;
GO

只有 1 条记录 JSON 输出(注意没有数组):

{
  "Data1": "Data1",
  "Data2": "Data2"
}

2条记录SQL输入:

DECLARE @Json nvarchar(max) = 
(   
    SELECT
    (
        SELECT 'Data1' AS [Data1], 'Data2' AS [Data2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
    ) AS [Part1]
    ,
    (
        SELECT 'Text1' AS [Text1], 'Text2' AS [Text2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
    ) AS [Part2]

    FOR JSON PATH
    , WITHOUT_ARRAY_WRAPPER
);

SELECT @Json;
GO

2 条记录 JSON 输出(注意包含数组):

{
  "Part1": [
    {
      "Data1": "Data1",
      "Data2": "Data2"
    }
  ],
  "Part2": [
    {
      "Text1": "Text1",
      "Text2": "Text2"
    }
  ]
}

我“认为”WITHOUT_ARRAY_WRAPPER 是要添加的正确属性，它将解决此问题，但一旦添加该属性，我就会将整个记录作为字符串获取:

{
  "Part1": "{\"Data1\":\"Data1\",\"Data2\":\"Data2\"}",
  "Part2": "{\"Text1\":\"Text1\",\"Text2\":\"Text2\"}"
}

我知道我可以使用一些文本操作方法来使其工作，但我希望有一个干净的 SQL > JSON 语句。

我目前正在使用 SQL Server 2016，但如果需要，我可以获取 2017 或 2019 服务器。不确定后面的 SQL 是否可以更好地处理这个问题，或者是否只是我的查询需要优化。

编辑:我想要的输出是:

{
  "Part1": {
      "Data1": "Data1",
      "Data2": "Data2"
    },
  "Part2": {
      "Text1": "Text1",
      "Text2": "Text2"
    }
}

Answer 1

根据FOR JSON PATH. how to not use escape characters接受的答案MSDN 上的 SQL Server 论坛:

FOR JSON will escape any text unless if it is generated as JSON result by some JSON function/query. In your example, FOR JSON cannot know do you really want raw JSON or you are just sending some free text that looks like JSON.

Properly defined JSON is generated with FOR JSON (unless if it has WITHOUT_ARRAY_WRAPPER option) or JSON_QUERY. If you wrap your JSON literal with JSON_QUERY it will not be escaped.

这个答案让我尝试以下代码:

DECLARE @Json nvarchar(max) = 
(
    SELECT
     JSON_QUERY((
        SELECT 'Data1' AS [Data1], 'Data2' AS [Data2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
        , WITHOUT_ARRAY_WRAPPER
    )) AS [Part1]
    ,
    JSON_QUERY((
        SELECT 'Text1' AS [Text1], 'Text2' AS [Text2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
        , WITHOUT_ARRAY_WRAPPER
    )) AS [Part2]

    FOR JSON PATH
    , WITHOUT_ARRAY_WRAPPER
);

SELECT @Json;

事实证明 - 这就像一个魅力。结果:

{
    "Part1": {
        "Data1": "Data1",
        "Data2": "Data2"
    },
    "Part2": {
        "Text1": "Text1",
        "Text2": "Text2"
    }
}

DB<>Fiddle

---

更新看看我在 official documentation 里发现了什么:

To avoid automatic escaping, provide newValue by using the JSON_QUERY function. JSON_MODIFY knows that the value returned by JSON_MODIFY is properly formatted JSON, so it doesn't escape the value.