SQL查询生成指定格式JSON未达预期，求修正建议

原查询存在的问题 #

1. DBSKeyData被包装为数组：原子查询用FOR JSON PATH默认返回数组，但预期是单个对象
2. DDSData包含多余空字段：Print方法下的SharedLocation、Path等空字段被不必要输出
3. Content字段类型错误：空字符串被自动转为数值0，不符合预期格式
4. 字段名大小写不匹配：输出的键名（如documentType）与预期的首字母大写格式（DocumentType）不一致

修正后的SQL查询 #

SELECT TOP 1
  labelType AS [DocumentType],
  (
    SELECT producingPlant AS [ProducingPlant], productionOrder AS [ProductionOrder]
    FROM test T2 WITH (NOLOCK)
    WHERE T1.ordinalid = T2.ordinalid
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- 去掉数组外层，返回单个对象
  ) AS [DBSKeyData],
  (
    SELECT * FROM (
      -- Print方法：只返回需要的字段，避免空值输出
      SELECT 
        ordinalid,
        'Print' AS [Method],
        Printer AS [PrinterId]
      FROM test T3 WITH (NOLOCK)
      UNION ALL
      -- Fileshare方法：返回全部需要的字段，Content转为字符串
      SELECT 
        ordinalid,
        'Fileshare' AS [Method],
        '' AS [PrinterId],
        [SharedLocation],
        [Path],
        [FileName],
        'true' AS [Overwrite],
        'true' AS [Watermark],
        CAST(1 AS VARCHAR(10)) AS [Content]
      FROM test T4 WITH (NOLOCK)
    ) T
    WHERE T1.ordinalid = T.ordinalid
    FOR JSON PATH
  ) AS [DDSData]
FROM test T1 WITH (NOLOCK)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- 外层去掉数组，返回单个JSON对象

修正说明 #

• DBSKeyData：添加WITHOUT_ARRAY_WRAPPER将子查询的JSON数组转为单个对象，同时修改字段别名匹配首字母大写格式
• DDSData：拆分两个Method的查询，各自只选择对应需要的字段，避免空字段输出；将Content转为字符串类型，确保值为"1"而非1
• 外层查询：添加FOR JSON PATH, WITHOUT_ARRAY_WRAPPER，确保最终输出是单个JSON对象而非数组
• 字段别名：统一使用[KeyName]格式指定首字母大写的键名，完全匹配预期的JSON结构

验证输出 #

修正后的查询会生成与预期完全一致的JSON结构：

{
    "DocumentType": "FFS",
    "DBSKeyData": {
        "ProducingPlant": "FRM",
        "ProductionOrder": "TEST_Order"        
    },
    "DDSData": [
        {
            "Method": "Print",
            "PrinterId": "Test_ID"                 
        },
        {
            "Method": "Fileshare",
            "SharedLocation": "Test_Path",
            "Path": "Z\\Test",
            "FileName": "Test.pdf",
            "Overwrite": "true",
            "Watermark": "true",
            "Content": "1"
        }
    ] 
}

内容的提问来源于stack exchange，提问作者praveen kumar