将多个SELECT语句合并到一个JSON中 [英] Multiple SELECT statements into a single JSON

问题描述

我相信必须在某个地方回答此问题，但是对于我的一生，无论我如何更改搜索词组，我似乎都找不到任何东西.

我需要从两个完全独立的表中选择数据，然后将信息导出到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"
    }
}

解决方案

根据 DB<> Fiddle

---

更新 看看我在

2 record SQL Input:

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 record JSON Output (note the inclusion of arrays):

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

I "think" that WITHOUT_ARRAY_WRAPPER is the correct attribute to add which will resolve this but as soon as I add that, I get the entire record as a string:

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

I understand that there's text manipulation methods I can use to get this to work, but I'm hoping for a clean SQL > JSON statement.

I'm currently working on SQL Server 2016 but I can if necessary get a 2017 or 2019 server. Not sure if later SQL handles this better or if it's just my query that needs optimisation.

Edit: My desired output is:

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

解决方案

According to the accepted answer of FOR JSON PATH. how to not use escape characters on SQL Server's forum on MSDN:

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.

This answer got me to try the following code:

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;

As as it turns out - this is working like a charm. Results:

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

DB<>Fiddle

---

Update Look what I found buried in 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.

这篇关于将多个SELECT语句合并到一个JSON中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！