CODEWITHSUNDEEP
sqljsonsql-servert-sql
Ivan
Ivan

Reputation: 61

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

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}');

Upvotes: 0

Views: 109

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 2

marc_s
marc_s

Reputation: 755421

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.

Upvotes: 4

Related Questions