Convert Sql output to JSON format

Amit Trivedi 40 Reputation points

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

Sql Table:


(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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
{count} votes

Accepted answer
  1. Viorel 112.9K Reputation points

    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”.

    3 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,021 Reputation points
    0 comments No comments