如何获得这个json输出？

siv3szwd 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(279)

我需要从SQLServer2016创建以下json。

{
    "recipientList": [
        {
            "name": "1",
            "recipientType": "User"
        },
        {
            "name": "2",
            "recipientType": "User"
        }
    ],
    "from": "Admin",
    "creationUtcDate": "2015-04-30T12:30:18.701Z",
    "content": "Test Message"
}

我尝试了使用json auto、json path和root的sql查询，如下所示，但没有一个得到所需的输出。

DECLARE @recipientList TABLE ([name] varchar(50), [recipientType] VARCHAR(50), [From] VARCHAR(500), [creationUtcDate] DATETIME, [content] VARCHAR(8000))
INSERT INTO @recipientList
SELECT '1', 'User', 'Admin', GETUTCDATE(), 'Test Message'
union
SELECT '2', 'User', 'Admin', GETUTCDATE(), 'Test Message'

SELECT r.[name], r.[recipientType], r.[From], r.creationUtcDate, r.content
FROM @recipientList r
FOR JSON PATH, ROOT ('recipientList')

当前结果：

{
    "recipientList": [
        {
            "name": "1",
            "recipientType": "User",
            "From": "Admin",
            "creationUtcDate": "2020-05-26T01:16:18.690",
            "content": "Test Message"
        },
        {
            "name": "2",
            "recipientType": "User",
            "From": "Admin",
            "creationUtcDate": "2020-05-26T01:16:18.690",
            "content": "Test Message"
        }
    ]
}

任何帮助都是值得赞赏的。。

sql JSON sql-server sql-server-2016

来源：https://stackoverflow.com/questions/62011135/how-do-i-get-this-json-output

1条答案

按热度按时间

bnlyeluc1#

您可以尝试此查询：

select distinct
  (select [name], recipientType from test for json path) recipientList,
  [from],
  creationUtcDate,
  content
from test t
for json path;

例子

create table test (
  [name] varchar(50),
  [recipientType] VARCHAR(50),
  [From] VARCHAR(500),
  [creationUtcDate] DATETIME,
  [content] VARCHAR(8000)
);

INSERT INTO test values 
('1', 'User', 'Admin', GETUTCDATE(), 'Test Message'),
('2', 'User', 'Admin', GETUTCDATE(), 'Test Message');

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=88bc66da55ff918b6550dd738aecb244
结果

[
  {
    "recipientList": [
      {
        "name": "1",
        "recipientType": "User"
      },
      {
        "name": "2",
        "recipientType": "User"
      }
    ],
    "from": "Admin",
    "creationUtcDate": "2020-05-26T01:48:26.447",
    "content": "Test Message"
  }
]

坦白地说，我不知道怎么做，直到我看到下面的例子：
https://www.sqlshack.com/convert-sql-server-results-json/
https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

赞(0）回复(0）举报 2021-07-26

我来回答

如何获得这个json输出？

1条答案

相关问题

热门标签

最新问答