CODEWITHSUNDEEP
sqljsonsql-serversql-server-2016
Khalid
Khalid

Reputation: 41

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

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

Answers (1)

zedfoxus
zedfoxus

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

Related Questions