How to make a table from a table of json rows in SQL Server-CodePudding

I have table like:

value
{"Date":"2022-10-31","Delta":5,"Comment":null}
{"Date":"2022-11-01","Delta":5,"Comment":null}

How can I get a table like:

Date	Delta	Comment
2022-10-31	5	null
2022-11-01	5	null

Data:

DECLARE @r TABLE (
   value VARCHAR(255)
)
INSERT INTO @r VALUES
     (N'{"Date":"2022-10-31","Delta":5,"Comment":null}'),
     (N'{"Date":"2022-11-01","Delta":5,"Comment":null}');

CodePudding user response：

Try something like this:

SELECT j.* 
FROM @r
CROSS APPLY OPENJSON(value)
            WITH 
            (
                Date DATE,
                Delta INT,
                Comment VARCHAR(50)
            ) j;

Should give you the desired output.

CodePudding user response：

Just another option demonstrating the use of JSON_VALUE()

Select Date   =JSON_VALUE(value,'$.Date')   -- could wrap in a try_convert(date,...)
      ,Delta  =JSON_VALUE(value,'$.Delta')  -- could wrap in a try_convert(int,...)
      ,Comment=JSON_VALUE(value,'$.Comment')
 From  @r