SQL Server中实现PostgreSQL jsonb_strip_nulls等效功能的方法

要实现忽略null值字段、仅保留存在有效字段的JSON拼接效果，可通过以下几种方法实现：

方法一：从表列动态生成过滤后的JSON #

如果需要从表的多个列生成JSON，且要忽略值为NULL的列，可以先将列转换为键值对行，过滤掉NULL值后再聚合为JSON：

SELECT 
  JSON_QUERY('{' + STRING_AGG(
    CASE 
      WHEN col_value IS NOT NULL THEN 
        '"' + col_name + '":' + 
        -- 处理嵌套JSON或字符串类型的值，避免转义错误
        CASE WHEN ISJSON(col_value) = 1 THEN col_value ELSE QUOTENAME(col_value, '"') END
      ELSE NULL
    END, ','
  ) + '}') AS stripped_json
FROM (
  -- 替换为你的表字段和查询条件
  VALUES 
    ('employee', (SELECT TOP 1 JSON_QUERY(employee) FROM your_table WHERE id = 1)),
    ('product2', (SELECT TOP 1 JSON_QUERY(product2) FROM your_table WHERE id = 1)),
    ('product1', (SELECT TOP 1 JSON_QUERY(product1) FROM your_table WHERE id = 1))
) AS key_values(col_name, col_value)
WHERE col_value IS NOT NULL;

说明： #

• 通过VALUES构造每个字段的键名和对应值（JSON_QUERY用于保留嵌套JSON的结构）
• 过滤掉值为NULL的行，避免无效字段被加入JSON
• 用STRING_AGG拼接成合法的JSON键值对字符串，再包裹为JSON对象
• QUOTENAME自动处理字符串类型的值，添加双引号并转义内部特殊字符

方法二：处理现有JSON字符串，移除null字段 #

如果已经有一个JSON字符串或JSON列，需要移除其中值为NULL的字段，可以用OPENJSON解析后过滤，再重新聚合：

DECLARE @original_json NVARCHAR(MAX) = '{"employee": {"id": 101}, "product2": null, "product1": {"name": "Laptop"}, "status": "active"}';

SELECT JSON_QUERY('{' + STRING_AGG(
  '"' + [key] + '":' + 
    CASE WHEN ISJSON([value]) = 1 THEN [value] ELSE QUOTENAME([value], '"') END, ','
) + '}') AS stripped_json
FROM OPENJSON(@original_json)
WHERE [value] IS NOT NULL;

执行结果： #

{"employee": {"id": 101}, "product1": {"name": "Laptop"}, "status": "active"}

方法三：针对已知字段的简化写法（字段少的场景） #

如果字段数量固定且较少，可以用JSON_MODIFY结合CASE判断，仅在值非NULL时添加字段：

SELECT 
  CASE 
    WHEN employee IS NOT NULL AND product2 IS NOT NULL THEN 
      JSON_MODIFY(JSON_MODIFY('{}', '$.employee', employee), '$.product2', product2)
    WHEN employee IS NOT NULL THEN JSON_MODIFY('{}', '$.employee', employee)
    WHEN product2 IS NOT NULL THEN JSON_MODIFY('{}', '$.product2', product2)
    ELSE '{}'
  END AS stripped_json
FROM your_table
WHERE id = 1;

这种写法适合字段数量少的场景，字段较多时推荐使用前两种动态聚合的方法。

内容的提问来源于stack exchange，提问作者Valeriy K.