How to insert object of array into JSON format?

Question

If this json output looks like this

{     
    "Bank_Name":"This is bank name",             
    "ACC_Name":"Tummy",             
    "ACC_No":"1122XXXX115",             
    "Date_Active":"Jan 31 2019  2:16PM",             
    "Date_Expired":"Nov 17 2020  1:14PM",       
    "Bank_Status":"Expired",                    
    
    "email_Notif":[           
            {"Verification":[{"User_Email":"[email protected]","Send_DateTime":"2020-11-03T13:30:59.7036152"},            
                            {"User_Email":"[email protected]","Send_DateTime":"2020-11-03T13:31:02.1563596"}]
            },{"Verified":[{"DateTime": "2020-11-03T13:31:02.1563596"}]
            }, 
            { "Updating":[{"User_Email":"[email protected]","Send_DateTime":"2020-11-03T13:30:59.7036152"},            
                            {"User_Email":"[email protected]","Send_DateTime":"2020-11-03T13:31:02.1563596"}]
            }               
        ],                   
        
    "rejection_Statuses":[                    
            {"Verification":"Nov 3 2020 01:31:02 PM"}               ,     
            {"Verified":"Nov 7 2020 01:12:03 PM"}       ,     
            {"Updating":"Nov 17 2020 01:18:03 PM"}       ,     
            {"Re_run":"Nov 27 2020 05:18:03 PM"}          
        ]
}

Questions:

How do I use "JSON_Modifiy" in SQL Server to insert email_Notif (as object of array) ? if JSON input looks like this:

 {"Bank_Name": "BPD SULAWESI SELATAN",
 "ACC_Name": "Tutik",
 "ACC_No": "1122000115",
 "Date_Active": "Jan 31 2019  2:16PM",
 "Date_Expired": "Nov 17 2020  1:14PM",
 "Bank_Status": "Expired",    
 "rejection_Statuses":[                    
 {"Verification":"Nov 3 2020 01:31:02 PM"} ,     
 {"Verified":"Nov 7 2020 01:12:03 PM"} ,     
 {"Updating":"Nov 17 2020 01:18:03 PM"} ,     
 {"Re_run":"Nov 27 2020 05:18:03 PM"}]
 }

How to get value from "email_Notify" as JSON format in SQL Server query by using select statement ? (Verification, Verified and Updating)

Zhorov · Accepted Answer · 2020-11-17 18:21:45Z

1

If I understand correctly, one way to achieve this is:

JSON:

DECLARE 
   @email_Verfication nvarchar(max) = N'{
      "Verification":[
         {"User_Email":"[email protected]", "Send_DateTime":"2020-11-03T13:30:59.7036152"},
         {"User_Email":"[email protected]", "Send_DateTime":"2020-11-03T13:31:02.1563596"}
      ]
   }', 
   @email_Verified nvarchar(max) = N'{
      "Verified":[
         {"DateTime":"2020-11-03T13:31:02.1563596"}
      ]
   }',
   @email_Updating nvarchar(max) = N'{"Updating":[
         {"User_Email":"[email protected]", "Send_DateTime":"2020-11-03T13:30:59.7036152"},
         {"User_Email":"[email protected]", "Send_DateTime":"2020-11-03T13:31:02.1563596"}
      ]
   }',
   @detail NVARCHAR (MAX) = N'{  
      "Bank_Name":"BPD SULAWESI SELATAN",          
      "ACC_Name":"Tutik",          
      "ACC_No":"1122000115",          
      "Date_Active":"Jan 31 2019  2:16PM",          
      "Date_Expired":"-",    
      "Bank_Status":"Active",                            
      "rejection_Statuses":[               
          {"Verification":"Nov 3 2020 01:31:02 PM"},
          {"Verified":"Nov 7 2020 01:12:03 PM"},
          {"Updating":"Nov 17 2020 01:18:03 PM"},
          {"Re_run":"Nov 27 2020 05:18:03 PM"}      
       ]       
   }'

Modify JSON:

SET @detail = JSON_MODIFY (@detail, 'append $.email_Notif', JSON_QUERY(@email_Verfication, '$'))
SET @detail = JSON_MODIFY (@detail, 'append $.email_Notif', JSON_QUERY(@email_Verified, '$'))
SET @detail = JSON_MODIFY (@detail, 'append $.email_Notif', JSON_QUERY(@email_Updating, '$'))

Parse JSON:

SELECT j2.[key], j2.[value]
FROM OPENJSON(@json, '$.email_Notif') j1
CROSS APPLY OPENJSON(j1.[value], '$') j2

edited Nov 17, 2020 at 18:21

answered Nov 17, 2020 at 11:24

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daleman Over a year ago

I works nice, but if they were input one by one from verification, verified and updating, why does it just replace each others ? Not appended in sequence.

Daleman Over a year ago

I put some code lines above in this link for simulation: dbfiddle.uk/…

Zhorov Over a year ago

@Daleman The answer is updated. One possible approach is to append each JSON fragment to the existing JSON using JSON_MODIFY() and append path modifier.

Daleman Over a year ago

I got final answer in this link: dbfiddle.uk/… and easy readable in jsonlint.com. Thank you @Zhorov !!!

Collectives™ on Stack Overflow

How to insert object of array into JSON format?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related