OPENJSON with dynamic JSON key value pair

Question

we are having a JSON document, where week numbers are dynamic keys. We want to load them to a relational table.

We are able to achieve relational resultset, if we hardcode the weeknumbers, as given below. But, it looks like a circuitous approach with hardcoded values. We want to make it dynamic.

Is there a way in TSQL to dynamically map the key value pairs as a relational table ?

DECLARE @json NVARCHAR(MAX) = N'[
  {
    "ID": "1",
    "Measure": "Current Sales",
    "2019Week12": "33",
    "2019Week13": "33",
    "2019Week14": "34"
  },
  {
    "ID": "2",
    "Measure": "Current Sales",
    "2019Week12": "",
    "2019Week13": "10",
    "2019Week14": "60"
  }]';

SELECT ID,Measure, WeekNumber, Sales
FROM
(   SELECT * FROM OPENJSON(@json)
    with 
    ( ID int '$.ID',
    Measure VARCHAR(30) '$.Measure',
    [2019Week12] INT '$."2019Week12"',
    [2019Week13] INT '$."2019Week13"',
    [2019Week14] INT '$."2019Week14"'
    )
) as p
UNPIVOT
(
Sales FOR WeekNumber IN ([2019Week12],[2019Week13],[2019Week14]) 
) as unpvt

The result set we got is:

+----+---------------+------------+-------+
| ID |    Measure    | WeekNumber | Sales |
+----+---------------+------------+-------+
|  1 | Current Sales | 2019Week12 |    33 |
|  1 | Current Sales | 2019Week13 |    33 |
|  1 | Current Sales | 2019Week14 |    34 |
|  2 | Current Sales | 2019Week12 |     0 |
|  2 | Current Sales | 2019Week13 |    10 |
|  2 | Current Sales | 2019Week14 |    60 |
+----+---------------+------------+-------+

Answer 1

You did not state the expected output. What I've got is: You want to get the same as above without the need to specify the names literally. I hope I got this correctly:

SELECT   JSON_VALUE(A.[value],'$.ID') AS ID
        ,JSON_VALUE(A.[value],'$.Measure') AS Measure
        ,B.[key] AS [varName]
        ,B.[value] AS [varValue]  
        ,ROW_NUMBER() OVER(PARTITION BY JSON_VALUE(A.[value],'$.ID') ORDER BY B.[key]) RowIndex
FROM OPENJSON(@json) A
CROSS APPLY OPENJSON(A.[value]) B
WHERE b.[key] NOT IN('ID','Measure');

The result

+----+---------------+------------+----------+----------+
| ID | Measure       | varName    | varValue | RowIndex |
+----+---------------+------------+----------+----------+
| 1  | Current Sales | 2019Week12 | 33       | 1        |
+----+---------------+------------+----------+----------+
| 1  | Current Sales | 2019Week13 | 33       | 2        |
+----+---------------+------------+----------+----------+
| 1  | Current Sales | 2019Week14 | 34       | 3        |
+----+---------------+------------+----------+----------+
| 2  | Current Sales | 2019Week12 |          | 1        |
+----+---------------+------------+----------+----------+
| 2  | Current Sales | 2019Week13 | 10       | 2        |
+----+---------------+------------+----------+----------+
| 2  | Current Sales | 2019Week14 | 60       | 3        |
+----+---------------+------------+----------+----------+

The idea in short:

• We use OPENJSON() to dive into your json string. This will return the two objects contained in a derived set A.
• Now we use OPENJSON() again passing in A.[value], which is the json object itself.
• This would return all contained items, but we surpress ID and Measurement within WHERE.
• The two sepcial columns, ID and Measurement we fetch directly from A.[value] using JSON_VALUE().

UPDATE

One enhancement might be this:

SELECT   C.ID
        ,C.varName AS [varName]
        ,TRY_CAST(LEFT(C.varName,4) AS INT) AS MeasureYear
        ,TRY_CAST(RIGHT(C.varName,2) AS INT) AS MeasureWeek
        ,C.varContent AS [varValue]  
        ,ROW_NUMBER() OVER(PARTITION BY C.ID ORDER BY C.varName) RowIndex
FROM OPENJSON(@json) A
CROSS APPLY OPENJSON(A.[value]) B
CROSS APPLY (SELECT JSON_VALUE(A.[value],'$.ID') AS ID
                   ,JSON_VALUE(A.[value],'$.Measure') AS Measure
                   ,B.[key] AS varName
                   ,B.[value] AS varContent) C
WHERE C.varName NOT IN('ID','Measure');

The idea: Adding one more APPLY allows to return the values as regular columns. This makes it easier to proceed with the values and makes this lot more readable.