title: SQL Sever JSon Output with variable nested levels
body: <p>I am trying to output some Json from SQL server where the result needs to be able to deal with 2 x nested levels, and where we won't always have the second nested level.</p>
  <p>The required output format is as follows :</p>
  <pre><code>{
    &quot;crm_company_id&quot;: 165938,
    &quot;crm_contact_id&quot;: 122908,
    &quot;expected_connection_date&quot;: &quot;2022-07-01&quot;,
    &quot;products&quot;: [
      {
        &quot;product_id&quot;: &quot;6401&quot;,
        &quot;quantity&quot;: &quot;15&quot;,
        &quot;cost&quot;: &quot;243.55&quot;,
        &quot;boltons&quot;: [
          {
            &quot;bolton_id&quot;: &quot;902&quot;,
            &quot;quantity&quot;: &quot;10&quot;,
            &quot;cost&quot;: &quot;10.00&quot;
          },
          {
            &quot;bolton_id&quot;: &quot;903&quot;,
            &quot;quantity&quot;: &quot;5&quot;,
            &quot;cost&quot;: &quot;15.00&quot;
          }
        ]
      },
      {
        &quot;product_id&quot;: &quot;6402&quot;,
        &quot;quantity&quot;: &quot;10&quot;,
        &quot;cost&quot;: &quot;43.99&quot;
      }
    ]
  }
  </code></pre>
  <p>So we can have any number of products from 1 to N and within each product any number of bolton from 0 to N. I have a header table, and then a child table with a reference to itself to store the boltons against the products My SQL for the selection is :</p>
  <pre><code>SELECT prop.crm_company_id
  , prop.crm_contact_id
  , prop.expected_connection_date
  , products.product_id
  , products.quantity
  , products.cost
  , boltons.bolton_id
  , boltons.quantity
  , boltons.cost
  FROM dbo.tblProposal prop
  INNER JOIN dbo.tblProduct products
      ON products.ProposalID = prop.ID
      AND products.ProductID = 0
  LEFT JOIN dbo.tblProduct boltons
      ON boltons.ProductID = products.ID
  
  </code></pre>
  <p>I can't seem to get the JSon to either not contain an empty array for the boltons for the product that has none if I use JSON AUTO, or if I use JSON PATH and put explicit array/column names it repeats the header. I can't quite get to the desired output.</p>