title: Converting JSON to table in SQL Server 2016
body: <p>I'm working on a Web project where the client application communicates with the DB via JSONs.</p>
  
  <p>The initial implementation took place with SQL Server 2012 (NO JSON support and hence we implemented a Stored Function that handled the parsing) and now we are moving to 2016 (YES JSON support).</p>
  
  <p>So far, we are reducing processing time by a significant factor (in some cases, over 200 times faster!).</p>
  
  <p>There are some interactions that contain arrays that need to be converted into tables. To achieve that, the <code>OPENJSON</code> function does <strong>ALMOST</strong> what we need.</p>
  
  <p>In some of these (array-based) cases, records within the arrays have one or more fields that are also OBJECTS (in this particular case, also arrays), for instance:</p>
  
  <pre><code>    [{
          "Formal_Round_Method": "Floor",
          "Public_Round_Method": "Closest",
          "Formal_Precision": "3",
          "Public_Precision": "3",
          "Formal_Significant_Digits": "3",
          "Public_Significant_Digits": "3",
          "General_Comment": [{
              "Timestamp": "2018-07-16 09:19",
              "From": "1",
              "Type": "Routine_Report",
              "Body": "[To + Media + What]: Comment 1",
              "$$hashKey": "object:1848"
          }, {
              "Timestamp": "2018-07-16 09:19",
              "From": "1",
              "Type": "User_Comment",
              "Body": "[]: Comment 2",
              "$$hashKey": "object:1857"
          }, {
              "Timestamp": "2018-07-16 09:19",
              "From": "1",
              "Type": "Routine_Report",
              "Body": "[To + Media + What]: Comment 3",
              "$$hashKey": "object:1862"
          }]
      }, {
          "Formal_Round_Method": "Floor",
          "Public_Round_Method": "Closest",
          "Formal_Precision": "3",
          "Public_Precision": "3",
          "Formal_Significant_Digits": "3",
          "Public_Significant_Digits": "3",
          "General_Comment": []
  
      }]
  </code></pre>
  
  <p>Here, <code>General_Comment</code> is also an array.</p>
  
  <p>When running the command:</p>
  
  <pre><code>SELECT *
    FROM OPENJSON(@_l_Table_Data)
    WITH (    Formal_Round_Method                 NVARCHAR(16)    '$.Formal_Round_Method'               ,
              Public_Round_Method                 NVARCHAR(16)    '$.Public_Round_Method'               ,
              Formal_Precision                    INT             '$.Formal_Precision'                  ,
              Public_Precision                    INT             '$.Public_Precision'                  ,
              Formal_Significant_Digits           INT             '$.Formal_Significant_Digits'         ,
              Public_Significant_Digits           INT             '$.Public_Significant_Digits'         ,
              General_Comment                     NVARCHAR(4000)  '$.General_Comment'                   
          ) ;
  </code></pre>
  
  <p>[<code>@_l_Table_Data</code> is a variable holding the JSON string]</p>
  
  <p>we are getting the column <code>General_Comment = NULL</code> even though the is data in there (at least in the first element of the array).</p>
  
  <p>I guess that I should be using a different syntax for those columns that may contain <strong>OBJECTS</strong> and not <strong>SIMPLE VALUES</strong>, but I have no idea what that syntax should be.</p>