Grouping and arranging data using FOR JSON PATH and INCLUDE_NULL_VALUES

Question 1

I'm having an issue with my T-SQL script. I am thinking about and working on how to produce the expected output from it.

Here's my T-SQL script:

DECLARE @user_id VARCHAR(50) = 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')
SELECT
 orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
 oa_prod.nested_data AS nested_prod,
 cnb.amount_to_pay
FROM
 [testDB].[dbo].[Orders] AS orders
RIGHT OUTER JOIN
 [testDB].[dbo].[Payment] AS cnb
ON
 orders.order_id = cnb.order_id
LEFT OUTER JOIN
 [testDB].[dbo].[StatusRef] AS stat_ref
ON
 orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
 SELECT
 orders.batch_code,
 orders.order_id,
 prod.prod_name,
 pv_kn.key_name,
 pv_kv.value,
 CASE WHEN
 orders.prod_id IS NOT NULL
 THEN
 prod.disc_price
 WHEN
 orders.prod_var_id IS NOT NULL
 THEN
 prod_var.disc_price
 END AS disc_price,
 orders.quantity
 FROM
 [testDB].[dbo].[Product] AS prod
 RIGHT OUTER JOIN
 [testDB].[dbo].[SubProduct] AS prod_var
 ON
 prod.prod_id = prod_var.prod_id
 LEFT OUTER JOIN
 [testDB].[dbo].[SubProductVarKeyValue] AS pv_kv
 ON
 prod_var.prod_var_id = pv_kv.prod_var_id
 LEFT OUTER JOIN
 [testDB].[dbo].[SubProductVarKeyNames] AS pv_kn
 ON
 pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
 WHERE 
 prod.prod_id = orders.prod_id
 OR prod_var.prod_var_id = orders.prod_var_id
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
 orders.disable = 0
 AND cnb.disable = 0
 AND orders.user_id = @user_id
 AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
 orders.dt_stamp DESC,
 orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

Here's my minimal, reproducible example using table variables:

DECLARE @StatusRef TABLE(
 stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
 com_usd_wrds NVARCHAR(100) NOT NULL
);
DECLARE @Product TABLE(
 prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
 prod_name VARCHAR(200) NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL
);
DECLARE @SubProduct TABLE(
 prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL,
 prod_id VARCHAR(50) NOT NULL
);
DECLARE @Orders TABLE(
 order_id VARCHAR(50) PRIMARY KEY NOT NULL,
 batch_code VARCHAR(50) NULL,
 quantity INT NOT NULL,
 stat_ref_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL,
 dt_stamp DATETIME NOT NULL,
 prod_id VARCHAR(50) NULL,
 prod_var_id VARCHAR(50) NULL,
 user_id VARCHAR(50) NOT NULL
);
DECLARE @Payment TABLE(
 amount_to_pay DECIMAL(14, 2) NOT NULL,
 order_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL
);
DECLARE @SubProductVarKeyValue TABLE(
 value VARCHAR(100) NOT NULL,
 prod_var_id VARCHAR(50) NOT NULL,
 pv_key_name_id VARCHAR(50) NOT NULL
);
DECLARE @SubProductVarKeyNames TABLE(
 pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
 key_name VARCHAR(100) NOT NULL
);
INSERT INTO @StatusRef
VALUES
(
 'STAT-REF-1001', -- stat_ref_id
 'PENDING' -- com_usd_wrds
),
(
 'STAT-REF-1002', -- stat_ref_id
 'APPROVED' -- com_usd_wrds
),
(
 'STAT-REF-1003', -- stat_ref_id
 'PROCESSING' -- com_usd_wrds
);
INSERT INTO @Product
VALUES
(
 'PROD-ID-1001', -- prod_id
 'iPhone', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1002', -- prod_id
 'Samsung', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1003', -- prod_id
 'Nokia', -- prod_name
 75, -- stock
 33150.00 -- disc_price
),
(
 'PROD-ID-1004', -- prod_id
 'Google', -- prod_name
 100, -- stock
 53509.00 -- disc_price
),
(
 'PROD-ID-1005', -- prod_id
 'Sony', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1006', -- prod_id
 'Lenovo', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
);
INSERT INTO @SubProduct
VALUES
(
 'PROD-VAR-ID-1', -- prod_var_id
 25, -- stock
 45809.00, -- disc_price
 'PROD-ID-1001' -- prod_id
),
(
 'PROD-VAR-ID-2', -- prod_var_id
 50, -- stock
 40209.00, -- disc_price
 'PROD-ID-1002' -- prod_id
),
(
 'PROD-VAR-ID-3', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1003' -- prod_id
),
(
 'PROD-VAR-ID-4', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1004' -- prod_id
),
(
 'PROD-VAR-ID-5', -- prod_var_id
 125, -- stock
 25809.00, -- disc_price
 'PROD-ID-1005' -- prod_id
),
(
 'PROD-VAR-ID-6', -- prod_var_id
 150, -- stock
 49100.00, -- disc_price
 'PROD-ID-1006' -- prod_id
);
INSERT INTO @SubProductVarKeyValue
VALUES
(
 'new', -- value
 'PROD-VAR-ID-1', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-2', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-5', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-6', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
)
INSERT INTO @SubProductVarKeyNames
VALUES
(
 'PVKN-ID-1', -- pv_key_name_id
 'Condition' -- key_name
)
INSERT INTO @Orders
(
 order_id,
 batch_code,
 quantity,
 stat_ref_id,
 dt_stamp,
 prod_id,
 prod_var_id,
 user_id
)
VALUES
(
 'ORDER-2025年04月11日-B71D0E2F5D8C', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1003', -- stat_ref_id
 '2025-04-14 10:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-1', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-D95EB033CA40', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-2', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-7F04EFA2BB60', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 'PROD-ID-1003', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-3F03EAA47686', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 'PROD-ID-1004', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-F4A89E2C4A30', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-5', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月08日-31BD887341FA', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-11 07:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-6', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);
INSERT INTO @Payment
(
 amount_to_pay,
 order_id
)
VALUES
(
 45809.00, -- amount_to_pay
 'ORDER-2025年04月11日-B71D0E2F5D8C' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-D95EB033CA40' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-7F04EFA2BB60' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-3F03EAA47686' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-F4A89E2C4A30' -- order_id
),
(
 49100.00, -- amount_to_pay
 'ORDER-2025年04月08日-31BD887341FA' -- order_id
);

SELECT * FROM @StatusRef

OUTPUT:

stat_ref_id	com_usd_wrds
STAT-REF-1001	PENDING
STAT-REF-1002	APPROVED
STAT-REF-1003	PROCESSING

SELECT * FROM @Product

OUTPUT:

prod_id	prod_name	stock	disc_price
PROD-ID-1001	iPhone	0	0.00
PROD-ID-1002	Samsung	0	0.00
PROD-ID-1003	Nokia	75	33150.00
PROD-ID-1004	Google	100	53509.00
PROD-ID-1005	Sony	0	0.00
PROD-ID-1006	Lenovo	0	0.00

SELECT * FROM @SubProduct

OUTPUT:

prod_var_id	stock	disc_price	prod_id
PROD-VAR-ID-1	25	45809.00	PROD-ID-1001
PROD-VAR-ID-2	50	40209.00	PROD-ID-1002
PROD-VAR-ID-3	0	0.00	PROD-ID-1003
PROD-VAR-ID-4	0	0.00	PROD-ID-1004
PROD-VAR-ID-5	125	25809.00	PROD-ID-1005
PROD-VAR-ID-6	150	49100.00	PROD-ID-1006

SELECT * FROM @Orders ORDER BY dt_stamp

OUTPUT:

order_id	batch_code	quantity	stat_ref_id	dt_stamp	prod_id	prod_var_id	user_id
ORDER-2025年04月08日-31BD887341FA	NULL	1	STAT-REF-1001	2025年04月11日 07:17:20.963	NULL	PROD-VAR-ID-6	UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL
ORDER-2025年04月10日-3F03EAA47686	BGUID-2025年04月10日-20239FD2059F	1	STAT-REF-1002	2025年04月12日 08:17:20.963	PROD-ID-1004	NULL	UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL
ORDER-2025年04月10日-F4A89E2C4A30	BGUID-2025年04月10日-20239FD2059F	1	STAT-REF-1002	2025年04月12日 08:17:20.963	NULL	PROD-VAR-ID-5	UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL
ORDER-2025年04月11日-7F04EFA2BB60	BGUID-2025年04月11日-6D81B58FAE94	2	STAT-REF-1001	2025年04月13日 09:17:20.963	PROD-ID-1003	NULL	UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL
ORDER-2025年04月11日-D95EB033CA40	BGUID-2025年04月11日-6D81B58FAE94	2	STAT-REF-1001	2025年04月13日 09:17:20.963	NULL	PROD-VAR-ID-2	UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL
ORDER-2025年04月11日-B71D0E2F5D8C	NULL	1	STAT-REF-1003	2025年04月14日 10:17:20.963	NULL	PROD-VAR-ID-1	UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL

SELECT * FROM @Payment

OUTPUT:

amount_to_pay	order_id	disable
45809.00	ORDER-2025年04月11日-B71D0E2F5D8C	0
146718.00	ORDER-2025年04月11日-D95EB033CA40	0
146718.00	ORDER-2025年04月11日-7F04EFA2BB60	0
79318.00	ORDER-2025年04月10日-3F03EAA47686	0
79318.00	ORDER-2025年04月10日-F4A89E2C4A30	0
45809.00	ORDER-2025年04月08日-31BD887341FA	0

SELECT * FROM @SubProductVarKeyValue

OUTPUT:

value	prod_var_id	pv_key_name_id
new	PROD-VAR-ID-1	PVKN-ID-1
new	PROD-VAR-ID-2	PVKN-ID-1
new	PROD-VAR-ID-5	PVKN-ID-1
new	PROD-VAR-ID-6	PVKN-ID-1

SELECT * FROM @SubProductVarKeyNames

OUTPUT:

pv_key_name_id	key_name
PVKN-ID-1	Condition

Here's the modified example of the script that's similar to the first one provided:

DECLARE @user_id VARCHAR(50) = 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')
SELECT
 orders.batch_code, -- FOR DEMONSTRATION PURPOSES ONLY
 oa_prod.nested_data AS nested_prod,
 cnb.amount_to_pay
FROM
 @Orders AS orders
RIGHT OUTER JOIN
 @Payment AS cnb
ON
 orders.order_id = cnb.order_id
LEFT OUTER JOIN
 @StatusRef AS stat_ref
ON
 orders.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY( -- OR OUTER APPLY
 SELECT
 orders.batch_code,
 orders.order_id,
 prod.prod_name,
 pv_kn.key_name,
 pv_kv.value,
 CASE WHEN
 orders.prod_id IS NOT NULL
 THEN
 prod.disc_price
 WHEN
 orders.prod_var_id IS NOT NULL
 THEN
 prod_var.disc_price
 END AS disc_price,
 orders.quantity
 FROM
 @Product AS prod
 RIGHT OUTER JOIN
 @SubProduct AS prod_var
 ON
 prod.prod_id = prod_var.prod_id
 LEFT OUTER JOIN
 @SubProductVarKeyValue AS pv_kv
 ON
 prod_var.prod_var_id = pv_kv.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyNames AS pv_kn
 ON
 pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
 WHERE 
 prod.prod_id = orders.prod_id
 OR prod_var.prod_var_id = orders.prod_var_id
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
) AS oa_prod(nested_data) -- it's a syntax analyzer lint conflict ONLY
WHERE
 orders.disable = 0
 AND cnb.disable = 0
 AND orders.user_id = @user_id
 AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
 orders.dt_stamp DESC
 --orders.batch_code ASC -- To prevent batch_code from being separated
FOR JSON PATH,
INCLUDE_NULL_VALUES

Unfortunately, when I beautify the JSON output, this is what it generates:

[
 {
 "batch_code": null,
 "nested_prod": [
 {
 "batch_code": null,
 "order_id": "ORDER-2025年04月11日-B71D0E2F5D8C",
 "prod_name": "iPhone",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 45809,
 "quantity": 1
 }
 ],
 "amount_to_pay": 45809
 },
 {
 "batch_code": "BGUID-2025年04月11日-6D81B58FAE94",
 "nested_prod": [
 {
 "batch_code": "BGUID-2025年04月11日-6D81B58FAE94",
 "order_id": "ORDER-2025年04月11日-D95EB033CA40",
 "prod_name": "Samsung",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 40209,
 "quantity": 2
 }
 ],
 "amount_to_pay": 146718
 },
 {
 "batch_code": "BGUID-2025年04月11日-6D81B58FAE94",
 "nested_prod": [
 {
 "batch_code": "BGUID-2025年04月11日-6D81B58FAE94",
 "order_id": "ORDER-2025年04月11日-7F04EFA2BB60",
 "prod_name": "Nokia",
 "key_name": null,
 "value": null,
 "disc_price": 33150,
 "quantity": 2
 }
 ],
 "amount_to_pay": 146718
 },
 {
 "batch_code": "BGUID-2025年04月10日-20239FD2059F",
 "nested_prod": [
 {
 "batch_code": "BGUID-2025年04月10日-20239FD2059F",
 "order_id": "ORDER-2025年04月10日-3F03EAA47686",
 "prod_name": "Google",
 "key_name": null,
 "value": null,
 "disc_price": 53509,
 "quantity": 1
 }
 ],
 "amount_to_pay": 79318
 },
 {
 "batch_code": "BGUID-2025年04月10日-20239FD2059F",
 "nested_prod": [
 {
 "batch_code": "BGUID-2025年04月10日-20239FD2059F",
 "order_id": "ORDER-2025年04月10日-F4A89E2C4A30",
 "prod_name": "Sony",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 25809,
 "quantity": 1
 }
 ],
 "amount_to_pay": 79318
 },
 {
 "batch_code": null,
 "nested_prod": [
 {
 "batch_code": null,
 "order_id": "ORDER-2025年04月08日-31BD887341FA",
 "prod_name": "Lenovo",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 49100,
 "quantity": 1
 }
 ],
 "amount_to_pay": 49100
 }
]

However, I need the output to look something like this:

[
 {
 "batch_code": null,
 "nested_prod": [
 {
 "batch_code": null,
 "order_id": "ORDER-2025年04月11日-B71D0E2F5D8C",
 "prod_name": "iPhone",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 45809,
 "quantity": 1
 }
 ],
 "amount_to_pay": 45809
 },
 {
 "batch_code": "BGUID-2025年04月11日-6D81B58FAE94",
 "nested_prod": [
 {
 "batch_code": "BGUID-2025年04月11日-6D81B58FAE94",
 "order_id": "ORDER-2025年04月11日-D95EB033CA40",
 "prod_name": "Samsung",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 40209,
 "quantity": 2
 },
 {
 "batch_code": "BGUID-2025年04月11日-6D81B58FAE94",
 "order_id": "ORDER-2025年04月11日-7F04EFA2BB60",
 "prod_name": "Nokia",
 "key_name": null,
 "value": null,
 "disc_price": 33150,
 "quantity": 2
 }
 ],
 "amount_to_pay": 146718
 },
 {
 "batch_code": "BGUID-2025年04月10日-20239FD2059F",
 "nested_prod": [
 {
 "batch_code": "BGUID-2025年04月10日-20239FD2059F",
 "order_id": "ORDER-2025年04月10日-3F03EAA47686",
 "prod_name": "Google",
 "key_name": null,
 "value": null,
 "disc_price": 53509,
 "quantity": 1
 },
 {
 "batch_code": "BGUID-2025年04月10日-20239FD2059F",
 "order_id": "ORDER-2025年04月10日-F4A89E2C4A30",
 "prod_name": "Sony",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 25809,
 "quantity": 1
 }
 ],
 "amount_to_pay": 79318
 },
 {
 "batch_code": null,
 "nested_prod": [
 {
 "batch_code": null,
 "order_id": "ORDER-2025年04月08日-31BD887341FA",
 "prod_name": "Lenovo",
 "key_name": "Condition",
 "value": "new",
 "disc_price": 49100,
 "quantity": 1
 }
 ],
 "amount_to_pay": 49100
 }
]

Note: The amount_to_pay was pre-computed in the client app. So, it should become a single instance when duplicate batch_codes are grouped.

User orders can cause variations in the desirable JSON output shown above.

Is anyone familiar with the issue I'm dealing with?

As much as possible, I prefer not to implement the Common Table Expression (CTE) approach.

Question 2

Could you supply a script which creating the tables are used and inserting sample data?

Question 3

Okay, I'll provide that shortly.

Question 4

@RodgerKong, I updated my question just now.

Question 5

The way you try to build a JSON is correct, nest a FOR JSON PATH subquery or joined table in the SELECT statement will output the nested JSON text you want. But there are some incorrect relationships in your data sample, this cause a unexpect result.

In my understanding:

The most outside level of your output should be the BATCH object, which is aggregated from the the table @orders that have the same batch_code. I'm assuming that orders with the same batch_code value also have the same stat_ref_id, disable, user_id, and dt_stamp values. So that I can include these columns in one dataset, I call it batches.
The BATCH object also has a property amount_to_pay, observe the sample data, it should be the value of sum(disc_price * quantity) of the orders with same value of batch_code. In your query, this value comes from the table @Payment, but in the table, column amount_to_pay is related to the @orders by order_id, it will repeating with @orders, so we need to add a new relationship to the dataset batched.
The most importaint is object BATCH includes an array which contains object ORDER, it look like each ORDER has only one PRODUCT/SUBPRODUCT object, this must be a nested array in JSON. To impliment this, the CROSS APPLY must filter orders by batch_code.

If my understanding is correct, issues in your SQL script are:

Your main query is based on table @orders, it has 6 validated rows, the top level of JSON result has to be a array contain 6 objects.
The result you expect has 4 top level objects, If you aggregate the @orders by batch_code directly, NULL values make you cannot pass it into subquery and filter correct orders/products. We have to find a valid key column during aggregating to represent batch_id.
The column amount_to_pay is come from table @Payment, but the only column can make relationship is order_id, if the main query JOIN with order_id will extend the rows either. We must add a key that can JOIN with the new batch_id in main query.
The subquery of CROSS APPLY should filter the table @orders by batch_code, since the NULL value make this impossible, we need to add a batch_id in this @orders table as filter key too.

Of all the previous points, add a batch_id column in the @orders table is key.

I think no batch number means only has one order, then I can treat the order_id as batch_code when the batch_code is null. So I building a CTE p_orders before the query, using IIF(batch_code IS NULL) to create a new column batch_id as key.
Then I changing the main query to a nested query which SELECT DISTINCT batch_id and some columns as the dataset batches.
I changing the dataset cnb to add a batch_id by JOIN the @Payment and the table p_orders.
At last, I adding a JOIN p_tables in the subquery of CROSS APPLY to get columns value which must show in JSON, then filtering inner rows by the value of batch_id of the main query batches.

Code is here:

DECLARE @StatusRef TABLE(
 stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
 com_usd_wrds NVARCHAR(100) NOT NULL
);
DECLARE @Product TABLE(
 prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
 prod_name VARCHAR(200) NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL
);
DECLARE @SubProduct TABLE(
 prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL,
 prod_id VARCHAR(50) NOT NULL
);
DECLARE @Orders TABLE(
 order_id VARCHAR(50) PRIMARY KEY NOT NULL,
 batch_code VARCHAR(50) NULL,
 quantity INT NOT NULL,
 stat_ref_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL,
 dt_stamp DATETIME NOT NULL,
 prod_id VARCHAR(50) NULL,
 prod_var_id VARCHAR(50) NULL,
 user_id VARCHAR(50) NOT NULL
);
DECLARE @Payment TABLE(
 amount_to_pay DECIMAL(14, 2) NOT NULL,
 order_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL
);
DECLARE @SubProductVarKeyValue TABLE(
 value VARCHAR(100) NOT NULL,
 prod_var_id VARCHAR(50) NOT NULL,
 pv_key_name_id VARCHAR(50) NOT NULL
);
DECLARE @SubProductVarKeyNames TABLE(
 pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
 key_name VARCHAR(100) NOT NULL
);
INSERT INTO @StatusRef
VALUES
(
 'STAT-REF-1001', -- stat_ref_id
 'PENDING' -- com_usd_wrds
),
(
 'STAT-REF-1002', -- stat_ref_id
 'APPROVED' -- com_usd_wrds
),
(
 'STAT-REF-1003', -- stat_ref_id
 'PROCESSING' -- com_usd_wrds
);
INSERT INTO @Product
VALUES
(
 'PROD-ID-1001', -- prod_id
 'iPhone', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1002', -- prod_id
 'Samsung', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1003', -- prod_id
 'Nokia', -- prod_name
 75, -- stock
 33150.00 -- disc_price
),
(
 'PROD-ID-1004', -- prod_id
 'Google', -- prod_name
 100, -- stock
 53509.00 -- disc_price
),
(
 'PROD-ID-1005', -- prod_id
 'Sony', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1006', -- prod_id
 'Lenovo', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
);
INSERT INTO @SubProduct
VALUES
(
 'PROD-VAR-ID-1', -- prod_var_id
 25, -- stock
 45809.00, -- disc_price
 'PROD-ID-1001' -- prod_id
),
(
 'PROD-VAR-ID-2', -- prod_var_id
 50, -- stock
 40209.00, -- disc_price
 'PROD-ID-1002' -- prod_id
),
(
 'PROD-VAR-ID-3', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1003' -- prod_id
),
(
 'PROD-VAR-ID-4', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1004' -- prod_id
),
(
 'PROD-VAR-ID-5', -- prod_var_id
 125, -- stock
 25809.00, -- disc_price
 'PROD-ID-1005' -- prod_id
),
(
 'PROD-VAR-ID-6', -- prod_var_id
 150, -- stock
 49100.00, -- disc_price
 'PROD-ID-1006' -- prod_id
);
INSERT INTO @SubProductVarKeyValue
VALUES
(
 'new', -- value
 'PROD-VAR-ID-1', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-2', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-5', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-6', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
)
INSERT INTO @SubProductVarKeyNames
VALUES
(
 'PVKN-ID-1', -- pv_key_name_id
 'Condition' -- key_name
)
INSERT INTO @Orders
(
 order_id,
 batch_code,
 quantity,
 stat_ref_id,
 dt_stamp,
 prod_id,
 prod_var_id,
 user_id
)
VALUES
(
 'ORDER-2025年04月11日-B71D0E2F5D8C', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1003', -- stat_ref_id
 '2025-04-14 10:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-1', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-D95EB033CA40', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-2', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-7F04EFA2BB60', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 'PROD-ID-1003', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-3F03EAA47686', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 'PROD-ID-1004', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-F4A89E2C4A30', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-5', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月08日-31BD887341FA', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-11 07:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-6', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);
INSERT INTO @Payment
(
 amount_to_pay,
 order_id
)
VALUES
(
 45809.00, -- amount_to_pay
 'ORDER-2025年04月11日-B71D0E2F5D8C' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-D95EB033CA40' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-7F04EFA2BB60' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-3F03EAA47686' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-F4A89E2C4A30' -- order_id
),
(
 49100.00, -- amount_to_pay
 'ORDER-2025年04月08日-31BD887341FA' -- order_id
);
DECLARE @user_id VARCHAR(50) = 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')
;
WITH p_orders
AS
(
 SELECT IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, o.* FROM @orders o
)
SELECT
 batchs.batch_code AS 'batch_code', -- FOR DEMONSTRATION PURPOSES ONLY
 oa_prod.nested_data AS 'nested_prod',
 cnb.amount_to_pay AS 'amount_to_pay'
FROM
(
 SELECT DISTINCT batch_id, batch_code, stat_ref_id, disable, user_id, dt_stamp FROM p_orders 
)batchs
RIGHT OUTER JOIN
(
 SELECT DISTINCT o.batch_id, p.amount_to_pay, p.disable FROM p_orders o
 INNER JOIN @Payment p
 ON o.order_id = p.order_id
)cnb
ON
 batchs.batch_id = cnb.batch_id
LEFT OUTER JOIN
 @StatusRef AS stat_ref
ON
 batchs.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY
(
 SELECT
 orders.batch_code,
 orders.order_id,
 prod.prod_name,
 pv_kn.key_name,
 pv_kv.value,
 CASE WHEN
 orders.prod_id IS NOT NULL
 THEN
 prod.disc_price
 WHEN
 orders.prod_var_id IS NOT NULL
 THEN
 prod_var.disc_price
 END AS disc_price,
 orders.quantity
 FROM
 @Product AS prod
 RIGHT OUTER JOIN
 @SubProduct AS prod_var
 ON
 prod.prod_id = prod_var.prod_id
 INNER JOIN p_orders orders
 ON prod.prod_id = orders.prod_id 
 OR prod_var.prod_var_id = orders.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyValue AS pv_kv
 ON
 prod_var.prod_var_id = pv_kv.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyNames AS pv_kn
 ON
 pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
 WHERE 
 orders.batch_id = batchs.batch_id
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
)AS oa_prod(nested_data)
WHERE
 batchs.disable = 0
 AND cnb.disable = 0
 AND batchs.user_id = @user_id
 AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
 batchs.dt_stamp DESC
FOR JSON PATH,
INCLUDE_NULL_VALUES

Code without CTE:

DECLARE @StatusRef TABLE(
 stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
 com_usd_wrds NVARCHAR(100) NOT NULL
);
DECLARE @Product TABLE(
 prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
 prod_name VARCHAR(200) NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL
);
DECLARE @SubProduct TABLE(
 prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL,
 prod_id VARCHAR(50) NOT NULL
);
DECLARE @Orders TABLE(
 order_id VARCHAR(50) PRIMARY KEY NOT NULL,
 batch_code VARCHAR(50) NULL,
 quantity INT NOT NULL,
 stat_ref_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL,
 dt_stamp DATETIME NOT NULL,
 prod_id VARCHAR(50) NULL,
 prod_var_id VARCHAR(50) NULL,
 user_id VARCHAR(50) NOT NULL
);
DECLARE @Payment TABLE(
 amount_to_pay DECIMAL(14, 2) NOT NULL,
 order_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL
);
DECLARE @SubProductVarKeyValue TABLE(
 value VARCHAR(100) NOT NULL,
 prod_var_id VARCHAR(50) NOT NULL,
 pv_key_name_id VARCHAR(50) NOT NULL
);
DECLARE @SubProductVarKeyNames TABLE(
 pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
 key_name VARCHAR(100) NOT NULL
);
INSERT INTO @StatusRef
VALUES
(
 'STAT-REF-1001', -- stat_ref_id
 'PENDING' -- com_usd_wrds
),
(
 'STAT-REF-1002', -- stat_ref_id
 'APPROVED' -- com_usd_wrds
),
(
 'STAT-REF-1003', -- stat_ref_id
 'PROCESSING' -- com_usd_wrds
);
INSERT INTO @Product
VALUES
(
 'PROD-ID-1001', -- prod_id
 'iPhone', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1002', -- prod_id
 'Samsung', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1003', -- prod_id
 'Nokia', -- prod_name
 75, -- stock
 33150.00 -- disc_price
),
(
 'PROD-ID-1004', -- prod_id
 'Google', -- prod_name
 100, -- stock
 53509.00 -- disc_price
),
(
 'PROD-ID-1005', -- prod_id
 'Sony', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1006', -- prod_id
 'Lenovo', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
);
INSERT INTO @SubProduct
VALUES
(
 'PROD-VAR-ID-1', -- prod_var_id
 25, -- stock
 45809.00, -- disc_price
 'PROD-ID-1001' -- prod_id
),
(
 'PROD-VAR-ID-2', -- prod_var_id
 50, -- stock
 40209.00, -- disc_price
 'PROD-ID-1002' -- prod_id
),
(
 'PROD-VAR-ID-3', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1003' -- prod_id
),
(
 'PROD-VAR-ID-4', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1004' -- prod_id
),
(
 'PROD-VAR-ID-5', -- prod_var_id
 125, -- stock
 25809.00, -- disc_price
 'PROD-ID-1005' -- prod_id
),
(
 'PROD-VAR-ID-6', -- prod_var_id
 150, -- stock
 49100.00, -- disc_price
 'PROD-ID-1006' -- prod_id
);
INSERT INTO @SubProductVarKeyValue
VALUES
(
 'new', -- value
 'PROD-VAR-ID-1', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-2', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-5', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-6', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
)
INSERT INTO @SubProductVarKeyNames
VALUES
(
 'PVKN-ID-1', -- pv_key_name_id
 'Condition' -- key_name
)
INSERT INTO @Orders
(
 order_id,
 batch_code,
 quantity,
 stat_ref_id,
 dt_stamp,
 prod_id,
 prod_var_id,
 user_id
)
VALUES
(
 'ORDER-2025年04月11日-B71D0E2F5D8C', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1003', -- stat_ref_id
 '2025-04-14 10:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-1', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-D95EB033CA40', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-2', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-7F04EFA2BB60', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 'PROD-ID-1003', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-3F03EAA47686', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 'PROD-ID-1004', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-F4A89E2C4A30', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-5', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月08日-31BD887341FA', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-11 07:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-6', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);
INSERT INTO @Payment
(
 amount_to_pay,
 order_id
)
VALUES
(
 45809.00, -- amount_to_pay
 'ORDER-2025年04月11日-B71D0E2F5D8C' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-D95EB033CA40' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-7F04EFA2BB60' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-3F03EAA47686' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-F4A89E2C4A30' -- order_id
),
(
 49100.00, -- amount_to_pay
 'ORDER-2025年04月08日-31BD887341FA' -- order_id
);
DECLARE @user_id VARCHAR(50) = 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')
SELECT
 batches.batch_code AS 'batch_code', -- FOR DEMONSTRATION PURPOSES ONLY
 oa_prod.nested_data AS 'nested_prod',
 cnb.amount_to_pay AS 'amount_to_pay'
FROM
(
 SELECT DISTINCT 
 IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, 
 batch_code, stat_ref_id, disable, user_id, dt_stamp FROM @orders o
)batches
RIGHT OUTER JOIN
(
 SELECT DISTINCT IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, 
 p.amount_to_pay, p.disable 
 FROM @orders o INNER JOIN @Payment p
 ON o.order_id = p.order_id
)cnb
ON
 batches.batch_id = cnb.batch_id
LEFT OUTER JOIN
 @StatusRef AS stat_ref
ON
 batches.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY
(
 SELECT
 orders.batch_code,
 orders.order_id,
 prod.prod_name,
 pv_kn.key_name,
 pv_kv.value,
 CASE WHEN
 orders.prod_id IS NOT NULL
 THEN
 prod.disc_price
 WHEN
 orders.prod_var_id IS NOT NULL
 THEN
 prod_var.disc_price
 END AS disc_price,
 orders.quantity
 FROM
 @Product AS prod
 RIGHT OUTER JOIN
 @SubProduct AS prod_var
 ON
 prod.prod_id = prod_var.prod_id
 INNER JOIN 
 (
 SELECT IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, o.* FROM @orders o
 )AS orders
 ON prod.prod_id = orders.prod_id 
 OR prod_var.prod_var_id = orders.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyValue AS pv_kv
 ON
 prod_var.prod_var_id = pv_kv.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyNames AS pv_kn
 ON
 pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
 WHERE 
 orders.batch_id = batches.batch_id
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
)AS oa_prod(nested_data)
WHERE
 batches.disable = 0
 AND cnb.disable = 0
 AND batches.user_id = @user_id
 AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
 batches.dt_stamp DESC
FOR JSON PATH,
INCLUDE_NULL_VALUES

Question 6

Is it possible to not integrate the CTE approach in your solution?

Question 7

Yes, it is. Just repeat the process that genarate batch_id when using @orders. CTE makes things more simple.

Question 8

My code is just a demo showing how to use subquery output nested JSON, which is not the best way in production environment.

Question 9

However, I am doing my best to avoid CTEs, if possible. Also, since you said yes (i.e., it is possible to not integrate the CTE approach), could you include it in your answer? I’d like to compare your answer with the other one provided here to see how they differ.

Question 10

Feeling good that the answer is helpful.

Question 11

I'll pass on the brain damage from grokking the entire schema. I think that this approach is going to group things the way that you want. I thought that JSON_QUERY() was supposed to not escape the quotes on the keys and values in the nested JSON but I still get them when I run this. I likely missed something there.

SELECT 
 o.batch_code
 , o.amount_to_pay
 , CASE 
 WHEN o.batch_code IS NULL 
 THEN JSON_QUERY((
 SELECT 
 oo.batch_code 
 , oo.order_id
 , p.prod_name
 , COALESCE(p.disc_price, sp.disc_price) as disc_price
 FROM @Orders as oo
 LEFT OUTER JOIN @Product as p
 ON oo.prod_id = p.prod_id
 LEFT OUTER JOIN @SubProduct as sp
 ON oo.prod_var_id = sp.prod_var_id
 WHERE o.order_id = oo.order_id
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
 ))
 ELSE JSON_QUERY((
 SELECT 
 oo.batch_code 
 , oo.order_id
 , p.prod_name
 , COALESCE(p.disc_price, sp.disc_price) as disc_price
 FROM @Orders as oo
 LEFT OUTER JOIN @Product as p
 ON oo.prod_id = p.prod_id
 LEFT OUTER JOIN @SubProduct as sp
 ON oo.prod_var_id = sp.prod_var_id
 WHERE o.batch_code = oo.batch_code
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
 )) 
 END as nested_prod
FROM (
 SELECT o.order_id, o.batch_code, p.amount_to_pay
 FROM @Orders as o
 LEFT OUTER JOIN @Payment as p
 ON o.order_id = p.order_id
 WHERE batch_code IS NULL
 UNION 
 SELECT NULL as order_id, batch_code, SUM(p.amount_to_pay) as amount_to_pay
 FROM @Orders as o
 LEFT OUTER JOIN @Payment as p
 ON o.order_id = p.order_id
 WHERE batch_code iS not null
 GROUP BY o.batch_code
) as o
FOR JSON PATH,
INCLUDE_NULL_VALUES

Question 12

Interestingly, the records that should be listed (have a relationship based on batch_code) are retrieved as text; is that correct? Unfortunately, the data that has been retrieved does not accurately provide the expected data mentioned in my question thread.

Question 13

You missed the "key_name" and "value" keys and its values. The "prod_name" shouldn't be null, except for "key_name" and "value". Still, your answer was great; you just need to refine it.

Question 14

One more thing, which I noticed in your query, for batch_code "BGUID-2025年04月11日-6D81B58FAE94" and "BGUID-2025年04月10日-20239FD2059F", you need to prevent "amount_to_pay" from summing up with other entries sharing the same "batch_code", as it represents the total of the corresponding list's "disc_price" (programmatically). Additionally, the "quantity" key and its value are missing.

Question 15

I had a feeling that I'd miss something important. The issue that I wanted to present was how to get the json to group the orders that are joined by batch, and list them individually when they are not in a batch.

Question 16

Could you please edit your answer to show how you would correct it to provide the expected output? I'd like to compare your approach to mine. Kindly disregard the backslash characters included in the output for now.

Rodger Kong Rodger Kong 4952 silver badges6 bronze badges · Accepted Answer · 2025-04-15 10:20:11Z

The way you try to build a JSON is correct, nest a FOR JSON PATH subquery or joined table in the SELECT statement will output the nested JSON text you want. But there are some incorrect relationships in your data sample, this cause a unexpect result.

In my understanding:

The most outside level of your output should be the BATCH object, which is aggregated from the the table @orders that have the same batch_code. I'm assuming that orders with the same batch_code value also have the same stat_ref_id, disable, user_id, and dt_stamp values. So that I can include these columns in one dataset, I call it batches.
The BATCH object also has a property amount_to_pay, observe the sample data, it should be the value of sum(disc_price * quantity) of the orders with same value of batch_code. In your query, this value comes from the table @Payment, but in the table, column amount_to_pay is related to the @orders by order_id, it will repeating with @orders, so we need to add a new relationship to the dataset batched.
The most importaint is object BATCH includes an array which contains object ORDER, it look like each ORDER has only one PRODUCT/SUBPRODUCT object, this must be a nested array in JSON. To impliment this, the CROSS APPLY must filter orders by batch_code.

If my understanding is correct, issues in your SQL script are:

Your main query is based on table @orders, it has 6 validated rows, the top level of JSON result has to be a array contain 6 objects.
The result you expect has 4 top level objects, If you aggregate the @orders by batch_code directly, NULL values make you cannot pass it into subquery and filter correct orders/products. We have to find a valid key column during aggregating to represent batch_id.
The column amount_to_pay is come from table @Payment, but the only column can make relationship is order_id, if the main query JOIN with order_id will extend the rows either. We must add a key that can JOIN with the new batch_id in main query.
The subquery of CROSS APPLY should filter the table @orders by batch_code, since the NULL value make this impossible, we need to add a batch_id in this @orders table as filter key too.

Of all the previous points, add a batch_id column in the @orders table is key.

I think no batch number means only has one order, then I can treat the order_id as batch_code when the batch_code is null. So I building a CTE p_orders before the query, using IIF(batch_code IS NULL) to create a new column batch_id as key.
Then I changing the main query to a nested query which SELECT DISTINCT batch_id and some columns as the dataset batches.
I changing the dataset cnb to add a batch_id by JOIN the @Payment and the table p_orders.
At last, I adding a JOIN p_tables in the subquery of CROSS APPLY to get columns value which must show in JSON, then filtering inner rows by the value of batch_id of the main query batches.

Code is here:

DECLARE @StatusRef TABLE(
 stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
 com_usd_wrds NVARCHAR(100) NOT NULL
);
DECLARE @Product TABLE(
 prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
 prod_name VARCHAR(200) NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL
);
DECLARE @SubProduct TABLE(
 prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL,
 prod_id VARCHAR(50) NOT NULL
);
DECLARE @Orders TABLE(
 order_id VARCHAR(50) PRIMARY KEY NOT NULL,
 batch_code VARCHAR(50) NULL,
 quantity INT NOT NULL,
 stat_ref_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL,
 dt_stamp DATETIME NOT NULL,
 prod_id VARCHAR(50) NULL,
 prod_var_id VARCHAR(50) NULL,
 user_id VARCHAR(50) NOT NULL
);
DECLARE @Payment TABLE(
 amount_to_pay DECIMAL(14, 2) NOT NULL,
 order_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL
);
DECLARE @SubProductVarKeyValue TABLE(
 value VARCHAR(100) NOT NULL,
 prod_var_id VARCHAR(50) NOT NULL,
 pv_key_name_id VARCHAR(50) NOT NULL
);
DECLARE @SubProductVarKeyNames TABLE(
 pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
 key_name VARCHAR(100) NOT NULL
);
INSERT INTO @StatusRef
VALUES
(
 'STAT-REF-1001', -- stat_ref_id
 'PENDING' -- com_usd_wrds
),
(
 'STAT-REF-1002', -- stat_ref_id
 'APPROVED' -- com_usd_wrds
),
(
 'STAT-REF-1003', -- stat_ref_id
 'PROCESSING' -- com_usd_wrds
);
INSERT INTO @Product
VALUES
(
 'PROD-ID-1001', -- prod_id
 'iPhone', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1002', -- prod_id
 'Samsung', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1003', -- prod_id
 'Nokia', -- prod_name
 75, -- stock
 33150.00 -- disc_price
),
(
 'PROD-ID-1004', -- prod_id
 'Google', -- prod_name
 100, -- stock
 53509.00 -- disc_price
),
(
 'PROD-ID-1005', -- prod_id
 'Sony', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1006', -- prod_id
 'Lenovo', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
);
INSERT INTO @SubProduct
VALUES
(
 'PROD-VAR-ID-1', -- prod_var_id
 25, -- stock
 45809.00, -- disc_price
 'PROD-ID-1001' -- prod_id
),
(
 'PROD-VAR-ID-2', -- prod_var_id
 50, -- stock
 40209.00, -- disc_price
 'PROD-ID-1002' -- prod_id
),
(
 'PROD-VAR-ID-3', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1003' -- prod_id
),
(
 'PROD-VAR-ID-4', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1004' -- prod_id
),
(
 'PROD-VAR-ID-5', -- prod_var_id
 125, -- stock
 25809.00, -- disc_price
 'PROD-ID-1005' -- prod_id
),
(
 'PROD-VAR-ID-6', -- prod_var_id
 150, -- stock
 49100.00, -- disc_price
 'PROD-ID-1006' -- prod_id
);
INSERT INTO @SubProductVarKeyValue
VALUES
(
 'new', -- value
 'PROD-VAR-ID-1', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-2', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-5', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-6', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
)
INSERT INTO @SubProductVarKeyNames
VALUES
(
 'PVKN-ID-1', -- pv_key_name_id
 'Condition' -- key_name
)
INSERT INTO @Orders
(
 order_id,
 batch_code,
 quantity,
 stat_ref_id,
 dt_stamp,
 prod_id,
 prod_var_id,
 user_id
)
VALUES
(
 'ORDER-2025年04月11日-B71D0E2F5D8C', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1003', -- stat_ref_id
 '2025-04-14 10:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-1', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-D95EB033CA40', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-2', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-7F04EFA2BB60', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 'PROD-ID-1003', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-3F03EAA47686', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 'PROD-ID-1004', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-F4A89E2C4A30', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-5', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月08日-31BD887341FA', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-11 07:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-6', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);
INSERT INTO @Payment
(
 amount_to_pay,
 order_id
)
VALUES
(
 45809.00, -- amount_to_pay
 'ORDER-2025年04月11日-B71D0E2F5D8C' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-D95EB033CA40' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-7F04EFA2BB60' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-3F03EAA47686' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-F4A89E2C4A30' -- order_id
),
(
 49100.00, -- amount_to_pay
 'ORDER-2025年04月08日-31BD887341FA' -- order_id
);
DECLARE @user_id VARCHAR(50) = 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')
;
WITH p_orders
AS
(
 SELECT IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, o.* FROM @orders o
)
SELECT
 batchs.batch_code AS 'batch_code', -- FOR DEMONSTRATION PURPOSES ONLY
 oa_prod.nested_data AS 'nested_prod',
 cnb.amount_to_pay AS 'amount_to_pay'
FROM
(
 SELECT DISTINCT batch_id, batch_code, stat_ref_id, disable, user_id, dt_stamp FROM p_orders 
)batchs
RIGHT OUTER JOIN
(
 SELECT DISTINCT o.batch_id, p.amount_to_pay, p.disable FROM p_orders o
 INNER JOIN @Payment p
 ON o.order_id = p.order_id
)cnb
ON
 batchs.batch_id = cnb.batch_id
LEFT OUTER JOIN
 @StatusRef AS stat_ref
ON
 batchs.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY
(
 SELECT
 orders.batch_code,
 orders.order_id,
 prod.prod_name,
 pv_kn.key_name,
 pv_kv.value,
 CASE WHEN
 orders.prod_id IS NOT NULL
 THEN
 prod.disc_price
 WHEN
 orders.prod_var_id IS NOT NULL
 THEN
 prod_var.disc_price
 END AS disc_price,
 orders.quantity
 FROM
 @Product AS prod
 RIGHT OUTER JOIN
 @SubProduct AS prod_var
 ON
 prod.prod_id = prod_var.prod_id
 INNER JOIN p_orders orders
 ON prod.prod_id = orders.prod_id 
 OR prod_var.prod_var_id = orders.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyValue AS pv_kv
 ON
 prod_var.prod_var_id = pv_kv.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyNames AS pv_kn
 ON
 pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
 WHERE 
 orders.batch_id = batchs.batch_id
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
)AS oa_prod(nested_data)
WHERE
 batchs.disable = 0
 AND cnb.disable = 0
 AND batchs.user_id = @user_id
 AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
 batchs.dt_stamp DESC
FOR JSON PATH,
INCLUDE_NULL_VALUES

Code without CTE:

DECLARE @StatusRef TABLE(
 stat_ref_id VARCHAR(50) PRIMARY KEY NOT NULL,
 com_usd_wrds NVARCHAR(100) NOT NULL
);
DECLARE @Product TABLE(
 prod_id VARCHAR(50) PRIMARY KEY NOT NULL,
 prod_name VARCHAR(200) NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL
);
DECLARE @SubProduct TABLE(
 prod_var_id VARCHAR(50) PRIMARY KEY NOT NULL,
 stock INT NOT NULL,
 disc_price DECIMAL(12, 2) NOT NULL,
 prod_id VARCHAR(50) NOT NULL
);
DECLARE @Orders TABLE(
 order_id VARCHAR(50) PRIMARY KEY NOT NULL,
 batch_code VARCHAR(50) NULL,
 quantity INT NOT NULL,
 stat_ref_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL,
 dt_stamp DATETIME NOT NULL,
 prod_id VARCHAR(50) NULL,
 prod_var_id VARCHAR(50) NULL,
 user_id VARCHAR(50) NOT NULL
);
DECLARE @Payment TABLE(
 amount_to_pay DECIMAL(14, 2) NOT NULL,
 order_id VARCHAR(50) NOT NULL,
 disable BIT DEFAULT (0) NOT NULL
);
DECLARE @SubProductVarKeyValue TABLE(
 value VARCHAR(100) NOT NULL,
 prod_var_id VARCHAR(50) NOT NULL,
 pv_key_name_id VARCHAR(50) NOT NULL
);
DECLARE @SubProductVarKeyNames TABLE(
 pv_key_name_id VARCHAR(50) PRIMARY KEY NOT NULL,
 key_name VARCHAR(100) NOT NULL
);
INSERT INTO @StatusRef
VALUES
(
 'STAT-REF-1001', -- stat_ref_id
 'PENDING' -- com_usd_wrds
),
(
 'STAT-REF-1002', -- stat_ref_id
 'APPROVED' -- com_usd_wrds
),
(
 'STAT-REF-1003', -- stat_ref_id
 'PROCESSING' -- com_usd_wrds
);
INSERT INTO @Product
VALUES
(
 'PROD-ID-1001', -- prod_id
 'iPhone', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1002', -- prod_id
 'Samsung', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1003', -- prod_id
 'Nokia', -- prod_name
 75, -- stock
 33150.00 -- disc_price
),
(
 'PROD-ID-1004', -- prod_id
 'Google', -- prod_name
 100, -- stock
 53509.00 -- disc_price
),
(
 'PROD-ID-1005', -- prod_id
 'Sony', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
),
(
 'PROD-ID-1006', -- prod_id
 'Lenovo', -- prod_name
 0, -- stock | dependent to @SubProduct
 0.00 -- disc_price | dependent to @SubProduct
);
INSERT INTO @SubProduct
VALUES
(
 'PROD-VAR-ID-1', -- prod_var_id
 25, -- stock
 45809.00, -- disc_price
 'PROD-ID-1001' -- prod_id
),
(
 'PROD-VAR-ID-2', -- prod_var_id
 50, -- stock
 40209.00, -- disc_price
 'PROD-ID-1002' -- prod_id
),
(
 'PROD-VAR-ID-3', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1003' -- prod_id
),
(
 'PROD-VAR-ID-4', -- prod_var_id
 0, -- stock | dependent to @Product
 0.00, -- disc_price | dependent to @Product
 'PROD-ID-1004' -- prod_id
),
(
 'PROD-VAR-ID-5', -- prod_var_id
 125, -- stock
 25809.00, -- disc_price
 'PROD-ID-1005' -- prod_id
),
(
 'PROD-VAR-ID-6', -- prod_var_id
 150, -- stock
 49100.00, -- disc_price
 'PROD-ID-1006' -- prod_id
);
INSERT INTO @SubProductVarKeyValue
VALUES
(
 'new', -- value
 'PROD-VAR-ID-1', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-2', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-5', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
),
(
 'new', -- value
 'PROD-VAR-ID-6', -- prod_var_id
 'PVKN-ID-1' -- pv_key_name_id
)
INSERT INTO @SubProductVarKeyNames
VALUES
(
 'PVKN-ID-1', -- pv_key_name_id
 'Condition' -- key_name
)
INSERT INTO @Orders
(
 order_id,
 batch_code,
 quantity,
 stat_ref_id,
 dt_stamp,
 prod_id,
 prod_var_id,
 user_id
)
VALUES
(
 'ORDER-2025年04月11日-B71D0E2F5D8C', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1003', -- stat_ref_id
 '2025-04-14 10:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-1', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-D95EB033CA40', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-2', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月11日-7F04EFA2BB60', -- order_id
 'BGUID-2025年04月11日-6D81B58FAE94', -- batch_code
 2, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-13 09:17:20.963', -- dt_stamp
 'PROD-ID-1003', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-3F03EAA47686', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 'PROD-ID-1004', -- prod_id
 NULL, -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月10日-F4A89E2C4A30', -- order_id
 'BGUID-2025年04月10日-20239FD2059F', -- batch_code
 1, -- quantity
 'STAT-REF-1002', -- stat_ref_id
 '2025-04-12 08:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-5', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
),
(
 'ORDER-2025年04月08日-31BD887341FA', -- order_id
 NULL, -- batch_code
 1, -- quantity
 'STAT-REF-1001', -- stat_ref_id
 '2025-04-11 07:17:20.963', -- dt_stamp
 NULL, -- prod_id
 'PROD-VAR-ID-6', -- prod_var_id
 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL' -- user_id
);
INSERT INTO @Payment
(
 amount_to_pay,
 order_id
)
VALUES
(
 45809.00, -- amount_to_pay
 'ORDER-2025年04月11日-B71D0E2F5D8C' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-D95EB033CA40' -- order_id
),
(
 146718.00, -- amount_to_pay
 'ORDER-2025年04月11日-7F04EFA2BB60' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-3F03EAA47686' -- order_id
),
(
 79318.00, -- amount_to_pay
 'ORDER-2025年04月10日-F4A89E2C4A30' -- order_id
),
(
 49100.00, -- amount_to_pay
 'ORDER-2025年04月08日-31BD887341FA' -- order_id
);
DECLARE @user_id VARCHAR(50) = 'UGUID-2025年01月27日-14-09-22-1967-ABCDEFGHIJKL'; -- FOR TESTING PURPOSES ONLY
DECLARE @status_t TABLE (status VARCHAR(50));
INSERT INTO @status_t VALUES ('PENDING'),('APPROVED'),('PROCESSING')
SELECT
 batches.batch_code AS 'batch_code', -- FOR DEMONSTRATION PURPOSES ONLY
 oa_prod.nested_data AS 'nested_prod',
 cnb.amount_to_pay AS 'amount_to_pay'
FROM
(
 SELECT DISTINCT 
 IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, 
 batch_code, stat_ref_id, disable, user_id, dt_stamp FROM @orders o
)batches
RIGHT OUTER JOIN
(
 SELECT DISTINCT IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, 
 p.amount_to_pay, p.disable 
 FROM @orders o INNER JOIN @Payment p
 ON o.order_id = p.order_id
)cnb
ON
 batches.batch_id = cnb.batch_id
LEFT OUTER JOIN
 @StatusRef AS stat_ref
ON
 batches.stat_ref_id = stat_ref.stat_ref_id
CROSS APPLY
(
 SELECT
 orders.batch_code,
 orders.order_id,
 prod.prod_name,
 pv_kn.key_name,
 pv_kv.value,
 CASE WHEN
 orders.prod_id IS NOT NULL
 THEN
 prod.disc_price
 WHEN
 orders.prod_var_id IS NOT NULL
 THEN
 prod_var.disc_price
 END AS disc_price,
 orders.quantity
 FROM
 @Product AS prod
 RIGHT OUTER JOIN
 @SubProduct AS prod_var
 ON
 prod.prod_id = prod_var.prod_id
 INNER JOIN 
 (
 SELECT IIF(o.batch_code IS NULL, o.order_id, o.batch_code) as batch_id, o.* FROM @orders o
 )AS orders
 ON prod.prod_id = orders.prod_id 
 OR prod_var.prod_var_id = orders.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyValue AS pv_kv
 ON
 prod_var.prod_var_id = pv_kv.prod_var_id
 LEFT OUTER JOIN
 @SubProductVarKeyNames AS pv_kn
 ON
 pv_kv.pv_key_name_id = pv_kn.pv_key_name_id
 WHERE 
 orders.batch_id = batches.batch_id
 FOR JSON PATH,
 INCLUDE_NULL_VALUES
)AS oa_prod(nested_data)
WHERE
 batches.disable = 0
 AND cnb.disable = 0
 AND batches.user_id = @user_id
 AND stat_ref.com_usd_wrds IN (SELECT status FROM @status_t)
ORDER BY
 batches.dt_stamp DESC
FOR JSON PATH,
INCLUDE_NULL_VALUES

Is it possible to not integrate the CTE approach in your solution?
Yes, it is. Just repeat the process that genarate batch_id when using @orders. CTE makes things more simple.
My code is just a demo showing how to use subquery output nested JSON, which is not the best way in production environment.
However, I am doing my best to avoid CTEs, if possible. Also, since you said yes (i.e., it is possible to not integrate the CTE approach), could you include it in your answer? I’d like to compare your answer with the other one provided here to see how they differ.

Stack Exchange Network

Grouping and arranging data using FOR JSON PATH and INCLUDE_NULL_VALUES

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

Grouping and arranging data using FOR JSON PATH and INCLUDE_NULL_VALUES

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions