Reputation: 41
I need to create the following JSON from SQL server 2016.
{
"recipientList": [
{
"name": "1",
"recipientType": "User"
},
{
"name": "2",
"recipientType": "User"
}
],
"from": "Admin",
"creationUtcDate": "2015-04-30T12:30:18.701Z",
"content": "Test Message"
}
I tried SQL queries using JSON AUTO, JSON PATH and ROOT as shown below but none of them gets me the desired output.
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')
Current result:
{
"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"
}
]
}
Any help is highly appreciable..
Upvotes: 3
Views: 81
Reputation: 37129
You can try this query:
select distinct
(select [name], recipientType from test for json path) recipientList,
[from],
creationUtcDate,
content
from test t
for json path;
Example
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
Results
[
{
"recipientList": [
{
"name": "1",
"recipientType": "User"
},
{
"name": "2",
"recipientType": "User"
}
],
"from": "Admin",
"creationUtcDate": "2020-05-26T01:48:26.447",
"content": "Test Message"
}
]
Quite frankly, I didn't know how to do it until I saw examples below:
Upvotes: 3