How to create a SQL nested queries to get a JSON nested format

Question

I have a sample data table in SQL below

Sales_Order	PO_number	Booking_number	Container_number	Seal_number	ETD1	ETA1	Country
17493993	4338_A	MXO0583354	CAIU9661214	H4131037	30/06/2022	04/08/2022	Jordan
17493996	4338_A	MXO0583354	CMAU4496275	H4131039	30/06/2022	04/08/2022	Jordan
17699594	4355	190VC0059204	MSMU8090753	FJ14673712	02/07/2022	11/08/2022	Jordan
17699593	4355	190VC0059204	MSDU7050180	FJ14673630	02/07/2022	11/08/2022	Jordan
17699591	4355	190VC0059204	CAIU7594190	FJ14673612	02/07/2022	11/08/2022	Jordan
18193374	Order_for_September_2	190VC0059041	MSMU8133238	FJ14673638	06/07/2022	19/08/2022	Latvia

I want to get the output in the required nested Json format as below

[{
    "Sales_Order": 17493993,
    "PO_number": "4338_A",
    "Booking_number": "MXO0583354",
    "Container_number": "CAIU9661214",
    "Seal_number": "H4131037",
    "ETD1": "30/06/2022",
    "ETA1": "04/08/2022",
    "Country": "Jordan",
    "Sub_Rows": [{
        "Sales_Order": 17493996,
        "PO_number": "4338_A",
        "Booking_number": "MXO0583354",
        "Container_number": "CMAU4496275",
        "Seal_number": "H4131039",
        "ETD1": "30/06/2022",
        "ETA1": "04/08/2022",
        "Country": "Jordan",
                }]
    },{
    "Sales_Order": 17699594,
    "PO_number": "4335",
    "Booking_number": "190VC0059204",
    "Container_number": "MSMU8090753",
    "Seal_number": "FJ14673712",
    "ETD1": "02/07/2022",
    "ETA1": "11/08/2022",
    "Country": "Jordan",
    "Sub_Rows": [{
        "Sales_Order": 17699593,
        "PO_number": "4335",
        "Booking_number": "190VC0059204",
        "Container_number": "MSDU7050180",
        "Seal_number": "FJ14673630",
        "ETD1": "02/07/2022",
        "ETA1": "11/08/2022",
        "Country": "Jordan",
                }],
    "Sub_Rows": [{
        "Sales_Order": 17699591,
        "PO_number": "4335",
        "Booking_number": "190VC0059204",
        "Container_number": "CAIU7594190",
        "Seal_number": "FJ14673612",
        "ETD1": "02/07/2022",
        "ETA1": "11/08/2022",
        "Country": "Jordan",
                }],
    },{
    "Sales_Order": 18193374,
    "PO_number": "Order_for_September_2",
    "Booking_number": "190VC0059041",
    "Container_number": "MSMU8133238",
    "Seal_number": "FJ14673638",
    "ETD1": "06/07/2022",
    "ETA1": "19/08/2022",
    "Country": "Latvia",
    }
]

Here, I need to apply a condition : if the booking number has more than 1 container number, 1st booking number will be on the root and the following booking numbers will be on the sub_rows of the root.

I am trying to groupby in SQL however not successful to achieve the required output? Any help will be appreciated?

My queryso far

SELECT 
   *, 
   Sub_Rows = ( Select * FROM [DMS_Backend].[dbo].[DMS_DEV] as V where V.Booking_number = H.Booking_number for JSON Path ) 
FROM [DMS_Backend].[dbo].[DMS_DEV] as H 
group By H.Sales_Order, H.PO_number, H.Booking_number, H.Container_number, H.Seal_number, H.ETA1, H.ETD1, H.Country for JSON Path

Answer 1

Consider using a ROW_NUMBER() for running counts of booking number. Then, including in outer query values equal to 1 and subquery values after 1:

WITH H AS (
    SELECT *, 
           ROW_NUMBER() OVER(
               PARTITION BY Booking_number
               ORDER BY Container_number
           ) AS rn
    FROM [DMS_Backend].[dbo].[DMS_DEV]
)

SELECT 
   Sales_Order, PO_number, Booking_number, Container_number, 
   Seal_number, ETD1, ETA1, Country,
   Sub_Rows = ( 
      SELECT Sales_Order, PO_number, Booking_number, Container_number, 
             Seal_number, ETD1, ETA1, Country 
      FROM H AS V 
      WHERE V.Booking_number = H_PLUS.Booking_number 
        AND V.rn > 1
      FOR JSON PATH
   ) 
FROM H
WHERE rn = 1
GROUP BY Sales_Order, PO_number, Booking_number, Container_number, 
         Seal_number, ETD1, ETA1, Country
ORDER BY Sales_Order, PO_number, Booking_number, Container_number, 
         Seal_number, ETD1, ETA1, Country
FOR JSON PATH

Output

[
  {
    "Sales_Order": 17493993,
    "PO_number": "4338_A",
    "Booking_number": "MXO0583354",
    "Container_number": "CAIU9661214",
    "Seal_number": "H4131037",
    "ETD1": "30/06/2022",
    "ETA1": "04/08/2022",
    "Country": "Jordan",
    "Sub_Rows": [
      {
        "Sales_Order": 17493996,
        "PO_number": "4338_A",
        "Booking_number": "MXO0583354",
        "Container_number": "CMAU4496275",
        "Seal_number": "H4131039",
        "ETD1": "30/06/2022",
        "ETA1": "04/08/2022",
        "Country": "Jordan"
      }
    ]
  },
  {
    "Sales_Order": 17699591,
    "PO_number": "4355",
    "Booking_number": "190VC0059204",
    "Container_number": "CAIU7594190",
    "Seal_number": "FJ14673612",
    "ETD1": "02/07/2022",
    "ETA1": "11/08/2022",
    "Country": "Jordan",
    "Sub_Rows": [
      {
        "Sales_Order": 17699593,
        "PO_number": "4355",
        "Booking_number": "190VC0059204",
        "Container_number": "MSDU7050180",
        "Seal_number": "FJ14673630",
        "ETD1": "02/07/2022",
        "ETA1": "11/08/2022",
        "Country": "Jordan"
      },
      {
        "Sales_Order": 17699594,
        "PO_number": "4355",
        "Booking_number": "190VC0059204",
        "Container_number": "MSMU8090753",
        "Seal_number": "FJ14673712",
        "ETD1": "02/07/2022",
        "ETA1": "11/08/2022",
        "Country": "Jordan"
      }
    ]
  },
  {
    "Sales_Order": 18193374,
    "PO_number": "Order_for_September_2",
    "Booking_number": "190VC0059041",
    "Container_number": "MSMU8133238",
    "Seal_number": "FJ14673638",
    "ETD1": "06/07/2022",
    "ETA1": "19/08/2022",
    "Country": "Latvia"
  }
]

Online Demo