Read Nested Json through sql query

Question

Currently I have this piece of code

DECLARE @json NVARCHAR(MAX) 
SET @json = 
N'[
   {
     "objOrg": {
       "EmpIds": [
         {
           "Id": 101
         },
         {
           "Id": 102
         },
         {
           "Id": 103
         }
       ]
     }
   }
 ]'

How can I return EmpId values pivoted such as

Id1	Id2	Id3
101	102	103

Answer 1

You can use OPENJSON() along with ROW_NUMBER() window function such as

DECLARE 
    @json   AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
        
SET @json = 
N'[
   {
     "objOrg": {
       "EmpIds": [
         {
           "Id": 101
         },
         {
           "Id": 102
         },
         {
           "Id": 103
         }
       ]
     }
   }
 ]';
 
 SELECT j.*, ROW_NUMBER() OVER (ORDER BY j.Id) AS rn
   INTO t_json
     FROM OPENJSON(@json)
              WITH (
                    JS NVARCHAR(MAX) '$.objOrg.EmpIds' AS JSON 
                  ) AS j0
    CROSS APPLY OPENJSON (j0.JS) 
                WITH (
                      Id   INT '$.Id' 
                    ) AS j; 
                    
SET @query = CONCAT('SELECT',
              STUFF(
                   (SELECT CONCAT(', MAX(CASE WHEN rn=' , CAST(rn AS VARCHAR) , ' THEN Id END) AS Id', CAST(rn AS VARCHAR))
                      FROM t_json
                     ORDER BY rn 
                       FOR XML PATH(''), type).value('.', 'NVARCHAR(MAX)'),
                   1,1,''
                  ),' FROM t_json');

EXECUTE(@query)  

Demo