如何为NULL子值保留空的JSON父对象-SQL Server for JSON PATH

Question

我在问并回答自己的问题，但我想看看是否有其他人对如何做到这一点有更好的想法。

我有一些JSON，我正在发送给第三方API。我创建了一个虚假的简化表示我正在尝试做什么。存在具有其子元素的primary_selection和具有其子元件的secondary_selection。如果辅助选择不存在，则子元素都为NULL。根据API，secondary_selection是必需的，但可以为空。他们还希望排除NULL元素。

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

上面的SQL生成以下内容：

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

但我想要的是：

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

Answer 1

另一个可能的选项是下面的语句。您需要使用FOR JSON PATH生成每个一级JSON对象。

请注意，您需要一个JSON_QUERY()调用来防止特殊字符的转义。文档中对此进行了解释：JSON_QUERY返回一个有效的JSON片段。因此，FOR JSON不会对JSON_QUERY返回值中的特殊字符进行转义。如果使用FOR JSON返回结果，并且包含的数据已经是JSON格式(在列中或作为表达式的结果(，请使用不带路径参数的JSON_QUERY包装JSON数据。

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

结果：

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

}
}

Answer 2

所以，这是我的解决方案，但想看看是否有其他人知道如何做到这一点。

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

DECLARE @EMPTY_OBJECT NVARCHAR(2) = '{}'
SELECT @JSON_WITHOUT_NULLS = JSON_MODIFY(@JSON_WITHOUT_NULLS,'$.secondary_selection',JSON_QUERY(@EMPTY_OBJECT))
SELECT @JSON_WITHOUT_NULLS