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

CodePudding user response：

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

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

SELECT MAX(CASE WHEN [rn]=1 THEN ID END) AS Id1,
       MAX(CASE WHEN [rn]=2 THEN ID END) AS Id2,
       MAX(CASE WHEN [rn]=3 THEN ID END) AS Id3
  FROM
  (
   SELECT j.*, ROW_NUMBER() OVER (ORDER BY j.Id) AS rn
     FROM OPENJSON(@json)
              WITH (
                    JS NVARCHAR(MAX) '$.objOrg.EmpIds' AS JSON 
                  ) AS j0
    CROSS APPLY OPENJSON (j0.JS) 
                WITH (
                      Id   INT '$.Id' 
                    ) AS j ) AS jj

Demo

CodePudding user response：

OPENJSON without a schema will return the array index. Then pass the inner object to OPENJSON again to parse it out, and pivot the final result using PIVOT or MAX(CASE

DECLARE @json nvarchar(max) = 
N'[
   {
     "objOrg": {
       "EmpIds": [
         {
           "Id": 101
         },
         {
           "Id": 102
         },
         {
           "Id": 103
         }
       ]
     }
   }
 ]';

SELECT MAX(CASE WHEN arr.[key] = 0 THEN ID END) AS Id1,
       MAX(CASE WHEN arr.[key] = 1 THEN ID END) AS Id2,
       MAX(CASE WHEN arr.[key] = 2 THEN ID END) AS Id3
FROM OPENJSON(@json, '$[0].objOrg.EmpIds') arr
CROSS APPLY OPENJSON (arr.value) 
  WITH (
    Id int
  ) AS j;

-- alternatively

SELECT p.*
FROM (
    SELECT arr.[key]   1 AS [key], j.Id
    FROM OPENJSON(@json, '$[0].objOrg.EmpIds') arr
    CROSS APPLY OPENJSON (arr.value) 
      WITH (
        Id int
      ) AS j
) j
PIVOT (
    MAX(j.Id) FOR j.[key] IN
      ([1], [2], [3])
) p;

db<>fiddle