title: OPENJSON with dynamic JSON key value pair
body: <p>we are having a JSON document, where week numbers are dynamic keys. We want to load them to a relational table.</p>
  
  <p>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.</p>
  
  <p>Is there a way in TSQL to dynamically map the key value pairs as a relational table ?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The result set we got is:</p>
  
  <pre><code>+----+---------------+------------+-------+
  | 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 |
  +----+---------------+------------+-------+
  </code></pre>