Export to JSON format

I have the following 2 tables. Actually I need to sync all table related and non related table to another database. Am using SQL Server 2017.

 CREATE TABLE EmailAddresses (
        ID int,
        EmailAddress varchar(255),
         StartDate datetime,
        EndDate datetime,   
    );   

 CREATE TABLE Cards (
        ID int,
        CardNumber varchar(255),
        ValidFrom datetime, 
        ValidTo datetime,  
        CVC  varchar(255),   
    );

I need all the records of 2 table in single json file ( format is as below) .

{ 
    "Sync By" : "XYZ", 
    "Sync Date" : "2021-10-16",
    "EmailAddresses" : [
        {
            "EmailAddress" : "Aisha@AEL.com", 
            "StartDate" : "2015-11-12", 
            "EndDate" : "2019-06-21"
        }, 
        {
            "EmailAddress" : "Elsie@Livulook.co.wm", 
            "StartDate" : "2014-02-09", 
            "EndDate" : "2015-12-23"
        }, 
        {
            "EmailAddress" : "Harriet23@Zaha.com", 
            "StartDate" : "2013-07-26", 
            "EndDate" : "2018-04-16"
        }
    ], 
    "Cards" : [
        {
            "CardNumber" : "340365242116610", 
            "ValidFrom" : "2015-07-28", 
            "ValidTo" : "2021-10-16", 
            "CVC" : "205"
        }

    ]
}

Solution

You could achieve that with the for json.

https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017

These two will produce two json arrays which if combined together will give you the desired format.

These two properties: Sync By and Sync Date do not exist in database hence they need to be adjusted manually at the export time.

 select 'XYZ' as 'Sync By',
 select '2021-10-16' as 'Sync Date',

 (select ID, EmailAddress, StartDate, EndDate,   
         from table EmailAddresses
         for json path) EmailAddresses,

 (select Id, CardNumber, ValidFrom, ValidTo, CVC
         from table Cards
         for json path) Cards

  for json path