Dynamic Pivot in OPENJSON

Question

I have below JSON data.

{
"Id": "001",
"EMP_Id": 203,
"DEP_Id": 4126,
"CreatedOn": "2020-07-20T03:52:26.6918267-04:00",
"CreatedBy": "Test"
}

I need to convert it into the below format, but my JSON values will be dynamic, I will not know what columns in Key I will get it.

Below the sample code:

DECLARE @Json  NVARCHAR(200)
SET @Json = '{"Id":"001","EMP_Id":203,"DEP_Id":4126,"CreatedOn":"2020-07-20T03:52:26.6918267-04:00","CreatedBy":"Test"}'
SELECT [Key], [Value] FROM OPENJSON(@Json)

Kindly help me to achieve it.

Answer 1

You may try to dynamically build the explicit schema needed for the OPENJSON() call. You need to execute OPENJSON() with the default schema to get the key names and data types:

JSON:

DECLARE @Json nvarchar(200)
SET @Json = '{"Id":"001","EMP_Id":203,"DEP_Id":4126,"CreatedOn":"2020-07-20T03:52:26.6918267-04:00","CreatedBy":"Test"}'

Statement:

DECLARE @stm nvarchar(max)

-- Explict schema
SELECT @stm = STRING_AGG(CONCAT(
   QUOTENAME([Key]), 
   CASE 
      WHEN [type] = 0 THEN CONCAT(N' nvarchar(200) ''$."', [key], N'"''')
      WHEN [type] = 1 THEN CONCAT(N' nvarchar(200) ''$."', [key], N'"''')
      WHEN [type] = 2 THEN CONCAT(N' int ''$."', [key], N'"''')
      WHEN [type] = 3 THEN CONCAT(N' bit ''$."', [key], N'"''')
      WHEN [type] = 4 THEN CONCAT(N' nvarchar(max) ''$."' , [key], N'"'' AS JSON')
      WHEN [type] = 5 THEN CONCAT(N' nvarchar(max) ''$."', [key], N'"'' AS JSON') 
   END
), N',')
FROM OPENJSON(@Json)

-- Execute statement
SET @stm = CONCAT(N'SELECT * FROM OPENJSON(@json) WITH (', @stm, N')')
EXEC sp_executesql @stm, N'@json nvarchar(200)', @json

Result:

Id  EMP_Id  DEP_Id  CreatedOn                         CreatedBy
---------------------------------------------------------------
001 203     4126    2020-07-20T03:52:26.6918267-04:00 Test