How to retain an empty JSON parent object for NULL child values - SQL Server FOR JSON PATH

I am asking and answering my own question but I would like to see if anyone else has any better idea of how to do this.

I have some JSON that I am sending to a 3rd party API. I have created a fake simplified representation of what I am trying to do. There is a "primary_selection" with its child elements and a "secondary_selection" with its child elements. If the secondary selection doesn't exist, the child elements are all NULL. According to the API the "secondary_selection" is required but can be empty. They also want NULL elements to be excluded.

DECLARE @JSON_WITHOUT_NULLS NVARCHAR(500)

SELECT @JSON_WITHOUT_NULLS = (SELECT '123456' [administrative_info.account_num],
'1' [administrative_info.user_id],
'whole wheat bread' [primary_selection.vehicle_for_sauce],
'avocado' [primary_selection.topping],
'mayo' [primary_selection.sauce_type],
NULL [secondary_selection.vehicle_for_mayo],
NULL [secondary_selection.topping],
NULL [secondary_selection.sauce_type]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

SELECT @JSON_WITHOUT_NULLS


The SQL above produces the following:

"administrative_info": {
"account_num": "123456",
"user_id": "1"
},
"primary_selection": {
"vehicle_for_sauce": "whole wheat bread",
"topping": "avocado",
"sauce_type": "mayo"
}


But what I want is:

"administrative_info": {
"account_num": "123456",
"user_id": "1"
},
"primary_selection": {
"vehicle_for_sauce": "whole wheat bread",
"topping": "avocado",
"sauce_type": "mayo"
},
"secondary_selection": {}

Continue reading...