How do I get this JSON output?

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"
}

​x
 
{    "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')

 
DECLARE @recipientList TABLE ([name] varchar(50), [recipientType] VARCHAR(50), [From] VARCHAR(500), [creationUtcDate] DATETIME, [content] VARCHAR(8000))INSERT INTO @recipientListSELECT '1', 'User', 'Admin', GETUTCDATE(), 'Test Message'unionSELECT '2', 'User', 'Admin', GETUTCDATE(), 'Test Message'​​SELECT r.[name], r.[recipientType], r.[From], r.creationUtcDate, r.contentFROM @recipientList rFOR 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"
        }
    ]
}

 
{    "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..

Answer

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;

 
select distinct  (select [name], recipientType from test for json path) recipientList,  [from],  creationUtcDate,  contentfrom test tfor 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');

 
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"
  }
]

 
[  {    "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:

Advertisement

Answer