Open Json a Json field in SQL Server to table

Question

I have a table in SQL Server where a row contains a Json column - something like this:

ResponseText	RequestId
{"LosUnqCod":0,"LosMidId":23}	96173722
{"LosUnqCod":1,"LosMidId":5}	96173721

I want to have a table in this shape:

LosUnqCod	LosMidId	RequestId
0	23	96173722
1	5	96173721

how to open this json?

Answer 1

You don't have to use OPENJSON if you only have one JSON object (not an array) per SQL row, you can use JSON_VALUE instead:

SELECT        
    JSON_VALUE(a.responsetext, '$.LosUnqCod') LosUnqCod,
    JSON_VALUE(a.responsetext, '$.LosMidId') LosMidId,
    a.RequestId
FROM [dbo].[a];