Convert Sql output to JSON format

Question

I am trying to convert sql table data into a particular json format.

Sql Table:

CREATE TABLE SqlToJson

(IngestIdentifier varchar(100),

SenderIdentifier varchar(100),

RetrievalTimestamp varchar(100),

SourceIdentifier varchar(100),

Name varchar(100),

SizeInKb varchar(100),

Location varchar(100)

);

INSERT INTO SqlToJson VALUES ('abcvdf12346', 'Data Transport','2023-09-21', 'Internal Data','abcd','24','root/site');

INSERT INTO SqlToJson VALUES ('abcvdf12346', 'Data Transport','2023-09-21', 'Internal Data','pqqre','32','root/account');

INSERT INTO SqlToJson VALUES ('abcvdf12346', 'Data Transport','2023-09-21', 'Internal Data','hgtv','87','root/bills');

Expected JSON output:

{
  "IngestIdentifier": "abcvdf12346",
  "SenderIdentifier": "Data Transport",
  "RetrievalTimestamp": "2023-09-21",
  "SourceIdentifier": "Internal Data",
  "OutputFiles": [
    {
      {
        "Name": "abcd",
        "SizeInKb": 24,
        "Location": "root/site"
      },
      {
        "Name": "pqqre",
        "SizeInKb": 32,
        "Location": "root/account"
      }
    }
  ]
}

Please help me to write the sql query which would generate the desired output.

Answer 1

This seems to work:

select IngestIdentifier, SenderIdentifier, RetrievalTimestamp, SourceIdentifier,
    OutputFiles = (
        Select [Name], SizeInKb, Location from SqlToJson
        where IngestIdentifier = t.IngestIdentifier and SenderIdentifier = t.SenderIdentifier and RetrievalTimestamp = t.RetrievalTimestamp and SourceIdentifier = t.SourceIdentifier
        for json path
    )
from SqlToJson t
group by IngestIdentifier, SenderIdentifier, RetrievalTimestamp, SourceIdentifier
for json path

However, if NULL are allowed, then “=” can be replaced with “is not distinct from”.

Answer 2

See

Work with JSON data in SQL Server

JSON Functions (Transact-SQL)