title: Parse Nested JSON into SQL Table
body: <p>I am trying to get a JSON file parsed into a usable format so I can insert it into a SQL table.</p>
  <p>The JSON file I have is heavily nested (and I can't get the vendor to change it at this point), and uses the same name at different levels.</p>
  <p>I have used the following code, to start off, but it is the multi sections and potentially multiple accounts etc that has me stumped. I know I will probably need to iterate through somehow, but just not sure where to begin.</p>
  <pre><code>DECLARE @JSON VARCHAR(MAX)
  
  SELECT @JSON = BulkColumn
  FROM OPENROWSET 
  (BULK 'C:\Users\joshu\Downloads\Cashflow.JSON', SINGLE_CLOB) 
  AS j
  
  If (ISJSON(@JSON)=1)
  Select * FROM OPENJSON (@JSON,'$.data')
  with
  (
  [id] nvarchar(50),
  [title] nvarchar(50),
  [sections] nvarchar(max) as json
  ) data_Structure
  
  cross apply openjson(data_structure.sections, '$')
  
  with (
  [income] nvarchar(max) as json
  ) data2
  --Income is one type, there should be a loop here
  
  cross apply openjson(data2.income, '$')
  </code></pre>
  <p>Which is getting my down the tree. The last data points that I want to collect are based on this bit of code</p>
  <pre><code>DECLARE @JSON VARCHAR(MAX)
   
  SELECT @JSON = BulkColumn
  FROM OPENROWSET 
  (BULK 'C:\Users\joshu\Downloads\Cashflow.JSON', SINGLE_CLOB) 
  AS j
   
  If (ISJSON(@JSON)=1)
  Select * FROM OPENJSON (@JSON,'$.data.sections.income.sections.tracker_1193.sections.tracker_1193_income.rows.&quot;b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d&quot;.data.&quot;2017-06&quot;') 
  with
  (
  [date] nvarchar(50),
  [value] decimal(18,2))
  </code></pre>
  <p>A sample of the JSON is here</p>
  <pre><code>        &quot;data&quot;: {
          &quot;id&quot;: &quot;cashflow&quot;,
          &quot;title&quot;: &quot;Cashflow Report&quot;,
          &quot;sections&quot;: {
              &quot;income&quot;: {
                  &quot;id&quot;: &quot;income&quot;,
                  &quot;title&quot;: &quot;Income&quot;,
                  &quot;sections&quot;: {
                      &quot;tracker_1193&quot;: {
                          &quot;id&quot;: &quot;tracker_1193&quot;,
                          &quot;title&quot;: &quot;xxxxxxx&quot;,
                          &quot;sections&quot;: {
                              &quot;tracker_1193_income&quot;: {
                                  &quot;id&quot;: &quot;tracker_1193_income&quot;,
                                  &quot;title&quot;: &quot;Income&quot;,
                                  &quot;sections&quot;: null,
                                  &quot;rows&quot;: {
                                      &quot;b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d&quot;: {
                                          &quot;account_id&quot;: &quot;b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d&quot;,
                                          &quot;account_name&quot;: &quot;Bobby Calf Sales&quot;,
                                          &quot;data&quot;: {
                                              &quot;2017-06&quot;: {
                                                  &quot;date&quot;: &quot;2017-06&quot;,
                                                  &quot;value&quot;: 0
                                              },
                                              &quot;2017-09&quot;: {
                                                  &quot;date&quot;: &quot;2017-09&quot;,
                                                  &quot;value&quot;: 4801.36
                                              },
                                              &quot;2017-12&quot;: {
                                                  &quot;date&quot;: &quot;2017-12&quot;,
                                                  &quot;value&quot;: 1997.33
  </code></pre>
  <p>Now the fun part.</p>
  <ul>
  <li>The income section is the most complicated;</li>
  <li>The First &quot;section&quot; in the JSON data is one of 13.</li>
  <li>The Second &quot;section&quot; is variable, as in there could be 1, or 12.</li>
  <li>The Third &quot;section is fixed to 3 (income, costs, gross-profit)</li>
  </ul>
  <p>The rest is more straight forward</p>
  <pre><code>{
      &quot;data&quot;: {
          &quot;id&quot;: &quot;cashflow&quot;,
          &quot;title&quot;: &quot;Cashflow Report&quot;,
          &quot;sections&quot;: {
  
  //other data ahead of this
                        &quot;operating_expenses&quot;: {
                  &quot;id&quot;: &quot;operating_expenses&quot;,
                  &quot;title&quot;: &quot;Operating Expenses&quot;,
                  &quot;sections&quot;: {
                      &quot;operating_expenses_animal_health_animal&quot;: {
                          &quot;id&quot;: &quot;operating_expenses_animal_health_animal&quot;,
                          &quot;title&quot;: &quot;Animal Health&quot;,
                          &quot;sections&quot;: null,
                          &quot;rows&quot;: {
                              &quot;0de82545-be93-4fb5-9d20-fa076af48e40&quot;: {
                                  &quot;account_id&quot;: &quot;0de82545-be93-4fb5-9d20-fa076af48e40&quot;,
                                  &quot;account_name&quot;: &quot;Animal Health - Minerals&quot;,
                                  &quot;data&quot;: {
                                      &quot;2019-07&quot;: {
                                          &quot;date&quot;: &quot;2019-07&quot;,
                                          &quot;value&quot;: 5827.93
                                      }
                                  }
                              },
                              &quot;9ba329a6-f77e-4779-9d79-28dd20465b9c&quot;: {
                                  &quot;account_id&quot;: &quot;9ba329a6-f77e-4779-9d79-28dd20465b9c&quot;,
                                  &quot;account_name&quot;: &quot;Animal Health - Other&quot;,
                                  &quot;data&quot;: {
                                      &quot;2019-07&quot;: {
                                          &quot;date&quot;: &quot;2019-07&quot;,
                                          &quot;value&quot;: 663.73
                                      }
                                  }
                              },
                              &quot;4f406965-3355-4968-a5ba-519d9706f329&quot;: {
                                  &quot;account_id&quot;: &quot;4f406965-3355-4968-a5ba-519d9706f329&quot;,
                                  &quot;account_name&quot;: &quot;Animal Health - Treatments&quot;,
                                  &quot;data&quot;: {
                                      &quot;2019-07&quot;: {
                                          &quot;date&quot;: &quot;2019-07&quot;,
                                          &quot;value&quot;: 8670.1
                                      }
                                  }
                              },
                              &quot;79c8ab89-22a2-4c5c-b591-0a3d95a4a95b&quot;: {
                                  &quot;account_id&quot;: &quot;79c8ab89-22a2-4c5c-b591-0a3d95a4a95b&quot;,
                                  &quot;account_name&quot;: &quot;Animal Health - Vet&quot;,
                                  &quot;data&quot;: {
                                      &quot;2019-07&quot;: {
                                          &quot;date&quot;: &quot;2019-07&quot;,
                                          &quot;value&quot;: 7645.18
                                      }
                                  }
                              }
                          },
                          &quot;totals&quot;: {
                              &quot;2019-07&quot;: {
                                  &quot;date&quot;: &quot;2019-07&quot;,
                                  &quot;value&quot;: 22806.94
                              }
                          }
                      },
                      
  </code></pre>
  <p>Because of the nature of this data, I haven't sorted my SQL table structure yet, but I am imagining it to be something along the lines of the below:</p>
  <p><a href="https://i.stack.imgur.com/NT30A.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NT30A.png" alt="enter image description here" /></a></p>
  <p>That's about it. I am stuck, need some help/guidance so anything you can do to assist is greatly appreciated</p>