Open Json a Json field in SQL Server to table

user_11194827 0
Viewed 585

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?

open-json primary-key tsql sql-server json
2 Answers

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
user_111948270

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