OpenJson store processed object in original form-CodePudding

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)

CodePudding user response：

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.