OPENJSON in SQL Server using with statement dynamically

Question

I have a requirement for selecting json column stored as text and parse it in a result set. For that i have written a stored procedure which is working fine.

ALTER PROCEDURE dbo.sp_SerializeJsonFromTable
AS

Declare     @json NVARCHAR(MAX)
Declare     @Id int

Select *
Into   #Temp
From   TTAuditLog

CREATE TABLE #Temp1
(
   Date_Time   varchar(50),  
   Exch        varchar(100), 
   Alias varchar(100),
   Broker varchar(100),
   Company varchar(100),
   Trd_Status  varchar(50) , 
   Trd_Message varchar(max),
   Action varchar(20),
   B_S varchar(10),
   O_C varchar(10),
   C_P varchar(10),
   Qty Float(50),
   Product varchar(50),
   Contract varchar(50),
   Strike varchar(50),
   Price Float(50),
   Order_Type varchar(20),
   TIF varchar(20),
   Modifier varchar(20),
   Trigger_Price Float(50),
   TPrice_Type varchar(20),
   TPrice_Qty Float(50),
   Ticks_Away varchar(10),
   Advanced varchar(50),
   Disc_Qty Float(50),
   Variance_pct Float(10),
   Duration varchar(20),
   Interval varchar(20),
   Retries varchar(10),
   Leftover_Act varchar(20),
   Exec_Qty Float(50),
   Work_Qty Float(50),
   Cxl_Qty Float(50),
   Legged_Qty Float(50),
   Pending_Qty Float(50),
   Undisclosed_Qty Float(50),
   Acc_Number varchar(50),
   Risk_Account varchar(50),
   Exch_Mbr varchar(20),
   Exch_Grp varchar(20),
   Exch_Trd varchar(20),
   Username varchar(50),
   Exch_Cred varchar(50),
   Trd_Mbr varchar(20),
   Trd_Grp varchar(20),
   Trd_ID varchar(20),
   Acct varchar(20),
   Give_Up varchar(20),
   Cntr_Party varchar(50),
   Exch_Time Time,
   Exch_Date DateTime,
   Time_Sent Time,
   Src varchar(20),
   Time_Proc varchar(20),
   P_A varchar(10),
   Owner varchar(20),
   Order_Num varchar(20),
   TT_Order_Key varchar(20),
   Rel_Key varchar(20),
   Parent_Key varchar(20),
  Link_Type varchar(20),
   SE_Server varchar(50),
   IP_Address varchar(20),
   FFT2 varchar(10),
   FFT3 varchar(10),
   FFT4 varchar(10),
   FFT5 varchar(10),
   FFT6 varchar(10),
   UserTag varchar(20),
   OrderTag varchar(20),
   Dir_Elec_Access varchar(20),
   Trading_Capacity varchar(20),
   Liq_Prov varchar(20),
   Cmdty_Der_Ind varchar(10),
   Inv_Decision varchar(20),
   Exec_Decision varchar(20),
   Client varchar(20),
   Start_Time Time,
   Start_Date DateTime,
   End_Time Time,
   End_Date DateTime,
   End_Behavior varchar(20),
   TransID varchar(20),
   Session_ID varchar(10),
   Mon_Username varchar(20),
   Callback_Rec varchar(10),
   SeriesKey varchar(20),
   Exch_Order_ID varchar(20),
   Destination varchar(20),
   FlowDel_Unit varchar(20),
   Time_Rec varchar(20),
   Order_Src_Hist varchar(20),
   Last_Ord_Src varchar(20) 
)


While (Select Count(*) From #Temp) > 0
Begin

   Select Top 1 @Id = Id, @json = JsonValue From #Temp

   --PRINT @json

   --SET @json = 'N' + @json

   --Declare @sql varchar(max)


   INSERT INTO #Temp1 SELECT * FROM  
   OPENJSON (@json)  
   WITH (
               Date_Time   varchar(50) '$."Time"',  
                          Exch   varchar(50) '$."Exch"', 
                          Alias varchar(100) '$."Alias"',
                          Broker varchar(100) '$."Broker"',
                          Company varchar(100) '$."Company"',
                          Trd_Status  varchar(50) '$."Status"', 
                          Trd_Message varchar(200) '$."Msg"',
                          Action varchar(20) '$."Action"',
                          B_S varchar(10) '$."B_S"',
                          O_C varchar(10) '$."O_C"',
                          C_P varchar(10) '$."C_P"',
                          Qty Float(50) '$."Qty"',
                          Product varchar(50) '$."Product"',
                          Contract varchar(50) '$."Contract"',
                          Strike varchar(50) '$."Strike"',
                          Price Float(50) '$."Price"',
                          Order_Type varchar(20) '$."OrderType"',
                          TIF varchar(20) '$."TIF"',
                          Modifier varchar(20) '$."Modifier"',
                          Trigger_Price Float(50) '$."TriggerPrice"',
                          TPrice_Type varchar(20) '$."TPriceType"',
                          TPrice_Qty Float(50) '$."TPriceQty"',
                          Ticks_Away varchar(10) '$."TicksAway"',
                          Advanced varchar(50) '$."Advanced"',
                          Disc_Qty Float(50) '$."DiscQty"',
                          Variance_pct Float(10) '$."Variance"',
                          Duration varchar(20) '$."Duration"',
                          Interval varchar(20) '$."Interval"',
                          Retries varchar(10) '$."Retries"',
                          Leftover_Act varchar(20) '$."LeftoverAction"',
                          Exec_Qty Float(50) '$."ExecQty"',
                          Work_Qty Float(50) '$."WorkQty"',
                          Cxl_Qty Float(50) '$." CxlQty"',
                          Legged_Qty Float(50) '$."LeggedQty"',
                          Pending_Qty Float(50) '$."PendingQty"',
                          Undisclosed_Qty Float(50) '$."UndisclosedQty"',
                          Acc_Number varchar(50) '$."AccountNumber"',
                          Risk_Account varchar(50) '$."RiskAccount"',
                          Exch_Mbr varchar(20) '$."ExchMbr"',
                          Exch_Grp varchar(20) '$."ExchGrp"',
                          Exch_Trd varchar(20) '$."ExchTrd"',
                          Username varchar(50) '$."Username"',
                          Exch_Cred varchar(50) '$."ExchCred"',
                          Trd_Mbr varchar(20) '$."TrdMbr"',
                          Trd_Grp varchar(20) '$."TrdGrp"',
                          Trd_ID varchar(20) '$."TrdID"',
                          Acct varchar(20) '$."Acct"',
                          Give_Up varchar(20) '$."GiveUp"',
                          Cntr_Party varchar(50) '$."CntrParty"',
                          Exch_Time Time '$."ExchTime"',
                          Exch_Date DateTime '$."ExchDate"',
                          Time_Sent Time '$."TimeSent"',
                          Src varchar(20) '$."Source"',
                          Time_Proc varchar(20) '$."TimeProcessed"',
                          P_A varchar(10) '$."P_A"',
                          Owner varchar(20) '$."Owner"',
                          Order_Num varchar(20) '$."OrderNumber"',
                          TT_Order_Key varchar(20) '$."TTOrderKey"',
                          Rel_Key varchar(20) '$."RelatedKey"',
                          Parent_Key varchar(20) '$."ParentKey"',
                          Link_Type varchar(20) '$."LinkType"',
                          SE_Server varchar(50) '$."SEServer"',
                         IP_Address varchar(20) '$."IPAddress"',
                          FFT2 varchar(10) '$."FFT2"',
                          FFT3 varchar(10) '$."FFT3"',
                          FFT4 varchar(10) '$."FFT4"',
                          FFT5 varchar(10) '$."FFT5"',
                          FFT6 varchar(10) '$."FFT6"',
                          UserTag varchar(20) '$."UserTag"',
                          OrderTag varchar(20) '$."OrderTag"',
                          Dir_Elec_Access varchar(20) '$."DirectElectronicAccess"',
                          Trading_Capacity varchar(20) '$."TradingCapacity"',
                          Liq_Prov varchar(20) '$."LiquidityProvision"',
                          Cmdty_Der_Ind varchar(10) '$."CommodityDerivativeIndicator"',
                          Inv_Decision varchar(20) '$."InvestmentDecision"',
                          Exec_Decision varchar(20) '$."ExecutionDecision"',
                          Client varchar(20) '$."Client"',
                          Start_Time Time '$."StartTime"',
                          Start_Date DateTime '$."StartDate"',
                          End_Time Time '$."EndTime"',
                          End_Date DateTime '$."EndDate"',
                          End_Behavior varchar(20) '$."EndBehavior"',
                          TransID varchar(20) '$."TransID"',
                          Session_ID varchar(10) '$."SessionID"',
                          Mon_Username varchar(20) '$."MonitoringUsername"',
                          Callback_Rec varchar(10) '$."CallbackReceived"',
                          SeriesKey varchar(20) '$."SeriesKey"',
                          Exch_Order_ID varchar(20) '$."ExchangeOrderID"',
                          Destination varchar(20) '$."Destination"',
                          FlowDel_Unit varchar(20) '$."FlowDeliveryUnit"',
                          Time_Rec varchar(20) '$."TimeReceived"',
                          Order_Src_Hist varchar(20) '$."OrderSourceHistory"',
                          Last_Ord_Src varchar(20) '$."LastOrderSource"'  
        )

Delete #Temp Where Id = @Id
End

SELECT * From #Temp1 order by Date_Time

Now I have a requirement wherein I can assign the field names inside with statement dynamically, rather than writing them specifically each one.

Please help.

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Feb 7, 2018 at 6:14

Razvan Socol · Accepted Answer · 2018-02-13 20:30:23Z

As long as your JSON data contains only similar rows with simple data types (no objects, no sub-arrays), you could construct the Dynamic SQL to create the table and insert the values in it, like this:

DECLARE @json NVARCHAR(MAX)

SET @json='[
    { "name":"John", "age":31, "city":"New York" },
    { "name":"Mary", "age":29, "city":"New York" }
]'

DECLARE @FirstRow NVARCHAR(MAX)=(SELECT TOP 1 Value FROM OPENJSON(@json))

DECLARE @Columns TABLE (
    Position INT IDENTITY PRIMARY KEY,
    ColumnName sysname NOT NULL UNIQUE,
    JSONDataType INT NOT NULL,
    SQLDataType VARCHAR(30) NOT NULL
)

INSERT INTO @Columns (ColumnName, JSONDataType, SQLDataType)
SELECT [Key], Type, 
    CASE Type 
        WHEN 1 THEN 'nvarchar(1000)'
        WHEN 2 THEN 'float'
        WHEN 3 THEN 'bit'
    END
FROM OPENJSON(@FirstRow) 

DECLARE @SQL NVARCHAR(MAX)

SET @SQL='('+(
    SELECT CHAR(13)+CHAR(10)+CHAR(9)+ColumnName+' '+c.SQLDataType
        +CASE WHEN c.Position<COUNT(*) OVER () THEN ',' ELSE '' END
    FROM @Columns c
    ORDER BY c.Position
    FOR XML PATH(''), TYPE
).value('.','nvarchar(max)')
+CHAR(13)+CHAR(10)+')'

SET @SQL='CREATE TABLE #Temp '+@SQL+CHAR(13)+CHAR(10)
+'INSERT INTO #Temp SELECT * FROM OPENJSON(@json) WITH'+@SQL
+CHAR(13)+CHAR(10)+'SELECT * FROM #Temp'

PRINT @SQL
EXEC sp_executesql @SQL,N'@json NVARCHAR(MAX)', @json

codeMonkey · Accepted Answer · 2020-09-16 23:05:05Z

For SQL 2017 and above:

DECLARE @cols nvarchar(max),
        @query nvarchar(max)
        
SELECT @cols = STRING_AGG([Key], ',')
FROM (
    SELECT jc.[Key]
    FROM ..YourTableWithAJsonColumn yt
    CROSS APPLY OPENJSON(yt.YourJsonColumn) jc
    GROUP BY jc.[Key]
) j

SET @query = N'SELECT SomeNonJsonColumn, ' + @cols + N'
                FROM
                (
                    SELECT yt.SomeNonJsonColumn,
                           jc.[Key],
                           jc.[Value]
                    FROM ..YourTableWithAJsonColumn yt
                    CROSS APPLY OPENJSON(yt.YourJsonColumn) jc
                ) x
                PIVOT
                (
                    MAX([Value])
                    FOR [Key] in (' + @cols + N'
                ) p'
                
EXEC sp_executesql @query

Collectives™ on Stack Overflow

OPENJSON in SQL Server using with statement dynamically

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related