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?

[Edit] the question and show what you have tried already. Explain why/where it failed. Be specific (error message, unexpected result, etc.). — sticky bit, Jun 13 '21 at 03:15

score 5 · Answer 1 · answered Jun 13 '21 at 03:47

Hi every body I found my answer and want to share with anyone who has the same issue as I does

  SELECT        
    
        reqTbl.LosUnqCod ,
        a.RequestId
    
   FROM   [dbo].[a]  CROSS APPLY      
                     OPENJSON( dbo.a.responsetext) 
                     WITH (
                        LosUnqCod nvarchar(50)
                        ) AS reqTbl

score 3 · Accepted Answer · answered Jun 13 '21 at 09:07

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];

Open Json a Json field in SQL Server to table

2 Answers2