SQL Sever JSon Output with variable nested levels

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.

The required output format is as follows :

{
  "crm_company_id": 165938,
  "crm_contact_id": 122908,
  "expected_connection_date": "2022-07-01",
  "products": [
    {
      "product_id": "6401",
      "quantity": "15",
      "cost": "243.55",
      "boltons": [
        {
          "bolton_id": "902",
          "quantity": "10",
          "cost": "10.00"
        },
        {
          "bolton_id": "903",
          "quantity": "5",
          "cost": "15.00"
        }
      ]
    },
    {
      "product_id": "6402",
      "quantity": "10",
      "cost": "43.99"
    }
  ]
}

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 :

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

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.

>Solution :

You can just use nested FOR JSON clauses.

As long as you leave out the INCLUDE_NULL_VALUES option, the missing values will be left out.

SELECT
  prop.crm_company_id
, prop.crm_contact_id
, prop.expected_connection_date
, p.products
FROM dbo.tblProposal prop
CROSS APPLY (
    SELECT
      products.product_id
    , products.quantity
    , products.cost
    , b.boltons
    FROM dbo.tblProduct products
    OUTER APPLY (
        SELECT
          boltons.bolton_id
        , boltons.quantity
        , boltons.cost
        FROM dbo.tblProduct boltons
        WHERE boltons.ProductID = products.ID
        FOR JSON PATH
    ) b(boltons)
    WHERE products.ProposalID = prop.ID
    FOR JSON PATH
) p(products)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;