OpenJson store processed object in original form

Question

DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT *
FROM OPENJSON ( @JSON)  
WITH (   
            OrderNumber   VARCHAR(200)  ,  
            OrderDate     VARCHAR(200)      ,  
            AccountNumber NVARCHAR(MAX)  ,  
            ItemPrice NVARCHAR(MAX)            ,  
            ItemQuantity NVARCHAR(MAX)     ,
            OriginalObject NVARCHAR(MAX)  AS JSOn       
 )

I want to store processed object i.e each json object in its original form in OriginalObject table column.

I have done quite bit searching but did not find any solution.

I want to store original json object so that no need to convert it row to json as its increases performance problem.i get orignal data using above column.

version of sql server:Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64)

score 2 · Accepted Answer · answered Jun 23 '22 at 11:16

You can use OPENJSON without a schema to break out the array into single objects, then APPLY again the OPENJSON function to get the full schema.

DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]';

SELECT
  o.*,
  array.value AS OriginalObject
FROM OPENJSON(@JSON) array
CROSS APPLY OPENJSON(array.value)
  WITH (   
    OrderNumber   varchar(200),  
    OrderDate     datetime,
    AccountNumber varchar(50),
    ItemPrice     decimal(18,9),  
    ItemQuantity  int
 ) o;

db<>fiddle

I suggest you choose your data types more carefully.

Thanks @Charlieface. for you help. this is demo json because of confidentiality i cannot share original data. i have managed to merge that with you solution. thanks a lot for your help. — VivekL, Jun 23 '22 at 11:28

OpenJson store processed object in original form

1 Answers1