Convert Sql output to JSON format

Amit Trivedi 40 Reputation points
2023-10-03T18:19:46.89+00:00

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.

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
    2023-10-03T20:02:48.1266667+00:00

    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
    2023-10-04T05:36:39.66+00:00
    0 comments No comments