Restore SQL table row data back to JOSN format as new column on the fly

Create by @Bing

Suppose we have below table that stored the API response of each transactions.

UID ResponseUUID Message StatusCode FlowName OrganizationUUID FlowUUID
1 753e0c72-a58c-422c-b526-c1547eb5239d success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
2 d69382c0-e9aa-49cd-b4b6-b3045413cead success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
3 0c279958-794a-4bf2-ab1c-884a1141c220 success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
4 7502f4fb-c87e-4e89-a799-cc96b91bb69d success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
5 a8076262-19cb-4fa8-ba4d-eb3c6be90e67 success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
6 1abf6ab8-8c89-48ca-80bc-e842bd24691f success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
7 463c73a0-6fed-44a7-8617-c31a1a810c02 success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
8 929fedc7-4221-41bc-abd3-a232d63b5e29 success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
9 f8202eb1-a4d3-4eae-93d2-e652ffe34094 success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca
10 ef5857a2-6b60-4a0e-82c7-e7e904cbe424 success 200 flow_53434de4-797b-4206-9be7-a3125e1db0ca_VMI_Outbound_ASN 1a70d22e-303c-4b75-a2c5-2d66c1d68220 53434de4-797b-4206-9be7-a3125e1db0ca

If we would like restore back each rows’ data from table to JSON format that append to the tail column of the table as below:

You might wonder why not just insert JSON data right away when inserted the data into table then. But things gonna not be easy if we don’t have the control permission/flow sometimes. Or the table was not belong to the scope of the development phase.

We could considerate to pull the table data aside the database and built JSON data format even using application/scripting approach. But can we did it just on the fly? Hmm, we could just modify our store procedure for processing and appending the JSON data back to the select statements as shown below:

-- Usp_Read_Response_Result.sql 
-- .....
   select
   [R].[ResponseUUID]
      ,[R].[Message]
      ,[R].[StatusCode]
      ,[R].[FlowName]
      ,[R].[OrganizationUUID]
      ,[R].[FlowUUID]
   , [J].[Data] -- The data column store back the JSON format data
    from 
 [dbo].[Response_Result]  as [R]
 inner join
 (
 select JSON_VALUE([value], '$.ResponseUUID') as [ResponseUUID]
        , [value] as [Data]
  from
 openjson(
 (
   select 
      [ResponseUUID]
      , [Message]
      , [StatusCode]
      , [FlowName]
      , [OrganizationUUID]
      , [FlowUUID]
   from [dbo].[Response_Result] for json auto))
 ) as [J]
 on [J].ResponseUUID = [R].ResponseUUID

The key ideal here was we format our whole table using ‘for json auto’ and declare ‘$.ResponseUUID’ property as join key.

Reference

• https://learn.microsoft.com/en-US/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver16
• https://learn.microsoft.com/en-US/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16