SQL Sever JSon Output with variable nested levels-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

You can use dot-separated column names to create the desired nested levels. If you don't include the INCLUDE_NULL_VALUES option, the empty values won't be included.

SELECT prop.crm_company_id
, prop.crm_contact_id
, prop.expected_connection_date
, products.product_id AS 'products.product_id'
, products.quantity AS 'products.quantity',
, products.cost AS 'products.cost'
, boltons.bolton_id AS 'products.boltons.bolton_id'
, boltons.quantity AS 'products.boltons.quantity'
, boltons.cost AS 'products.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
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

From Microsoft docs

The FOR JSON PATH clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the PATH option creates nested objects.