Grouping and arranging data using FOR JSON PATH and INCLUDE_NULL_VALUES

0 votes

2 answers

167 views

sql-server t-sql sql-server-2017 json

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](https://stackoverflow.com/help/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 | disable | dt_stamp | prod_id | prod_var_id | user_id | |----------|-------------|----------|-------------|---------|----------|----------|-------------|----------| | ORDER-2025-04-08-31BD887341FA | NULL | 1 | STAT-REF-1001 | 0 | 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 | 0 | 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 | 0 | 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 | 0 | 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 | 0 | 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 | 0 | 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.

Asked by DevQt (165 rep)

Apr 12, 2025, 02:31 AM
Last activity: Apr 16, 2025, 02:05 AM

Grouping and arranging data using FOR JSON PATH and INCLUDE_NULL_VALUES

Related Questions