如何通过PowerShell正确保存含子查询的T-SQL JSON结果？

我使用SQL Server 2016/2019编写了包含嵌套子查询的T-SQL语句，需通过PowerShell将查询结果保存为JSON文件。无嵌套子查询时（使用FOR JSON PATH）可正常工作，但带嵌套子查询时，子查询结果被保存为字符串，且缺失外层的"List"结构。

期望的JSON输出格式 #

{
    "List": [{
            "MainId": 1,
            "Name": "A",
            "sub": [{
                    "SubId": 1,
                    "Description": "a",
                    "detail": [{
                            "DetailId": 1,
                            "Details": "detail1"
                        }
                    ]
                }, {
                    "SubId": 2,
                    "Description": "aa"
                }
            ]
        }, {
            "MainId": 2,
            "Name": "B",
            "sub": [{
                    "SubId": 3,
                    "Description": "b",
                    "detail": [{
                            "DetailId": 2,
                            "Details": "detail2"
                        }, {
                            "DetailId": 3,
                            "Details": "detail3"
                        }
                    ]
                }
            ]
        }
    ]
}

当前使用的PowerShell脚本 #

# Define your SQL query
$sqlQuery = @"
DECLARE @MainList TABLE (id INT, name NVARCHAR(50))
DECLARE @SubList TABLE (id INT, mainId INT, description NVARCHAR(50))
DECLARE @DetailList TABLE (id INT, subId INT, details NVARCHAR(50))
INSERT INTO @Mainlist VALUES (1, 'A'), (2, 'B')
INSERT INTO @SubList VALUES (1, 1, 'a'), (2, 1, 'aa'), (3, 2, 'b')
INSERT INTO @DetailList VALUES (1, 1, 'detail1'), (2, 3, 'detail2'), (3, 3, 'detail3')
SELECT
    m.id AS MainId,
    m.name AS Name,
    (SELECT
        s.id AS SubId,
        s.description AS Description,
        (SELECT
            d.id AS DetailId,
            d.details AS Details
        FROM
            @DetailList d
        WHERE
            d.subId = s.id
        FOR JSON PATH
        ) AS detail
    FROM @SubList s WHERE s.mainId = m.id FOR JSON PATH
    ) AS sub
FROM @MainList AS m
"@
# Execute the SQL query using Invoke-Sqlcmd
$result = Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourServerName" -Database "YourDatabaseName" -Username "YourUsername" -Password "YourPassword" 
# Convert the result to JSON
$jsonResult = $result | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json  -Depth 3  
# Define the path to save the JSON file
$jsonFilePath = "C:\OutputFile.json" 
# Save the JSON result to a file
$jsonResult | Out-File -FilePath $jsonFilePath -Encoding UTF8

实际生成的JSON文件 #

[
    {
        "MainId":  1,
        "Name":  "A",
        "sub":  "[{\"SubId\":1,\"Description\":\"a\",\"detail\":[{\"DetailId\":1,\"Details\":\"detail1\"}]},{\"SubId\":2,\"Description\":\"aa\"}]"
    },
    {
        "MainId":  2,
        "Name":  "B",
        "sub":  "[{\"SubId\":3,\"Description\":\"b\",\"detail\":[{\"DetailId\":2,\"Details\":\"detail2\"},{\"DetailId\":3,\"Details\":\"detail3\"}]}]"
    }
]

解决方案 #

方案一：修改T-SQL语句直接生成目标JSON #

让SQL Server直接输出完整的嵌套JSON结构，避免PowerShell二次转换时的字符串化问题。核心是在子查询的FOR JSON PATH后添加AS JSON标记，告诉SQL Server该字段是JSON对象而非字符串；同时在外层查询添加FOR JSON PATH, ROOT('List')生成带外层"List"的结构。

修改后的T-SQL代码：

DECLARE @MainList TABLE (id INT, name NVARCHAR(50))
DECLARE @SubList TABLE (id INT, mainId INT, description NVARCHAR(50))
DECLARE @DetailList TABLE (id INT, subId INT, details NVARCHAR(50))
INSERT INTO @Mainlist VALUES (1, 'A'), (2, 'B')
INSERT INTO @SubList VALUES (1, 1, 'a'), (2, 1, 'aa'), (3, 2, 'b')
INSERT INTO @DetailList VALUES (1, 1, 'detail1'), (2, 3, 'detail2'), (3, 3, 'detail3')

SELECT
    m.id AS MainId,
    m.name AS Name,
    (SELECT
        s.id AS SubId,
        s.description AS Description,
        (SELECT
            d.id AS DetailId,
            d.details AS Details
        FROM @DetailList d
        WHERE d.subId = s.id
        FOR JSON PATH
        ) AS detail
    FROM @SubList s 
    WHERE s.mainId = m.id 
    FOR JSON PATH
    ) AS sub
FROM @MainList AS m
FOR JSON PATH, ROOT('List')

对应的PowerShell脚本简化为直接获取SQL输出的JSON并保存：

$sqlQuery = @"
-- 上面修改后的T-SQL代码
"@

# 执行查询并获取JSON结果（添加 -OutputAs SingleValue 确保返回完整JSON字符串）
$result = Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourServerName" -Database "YourDatabaseName" -Username "YourUsername" -Password "YourPassword" -OutputAs SingleValue

$jsonFilePath = "C:\OutputFile.json"
$result | Out-File -FilePath $jsonFilePath -Encoding UTF8

方案二：修改PowerShell脚本处理字符串化的JSON #

如果无法修改T-SQL，可在PowerShell中解析每个sub字段的字符串为JSON对象，再重新构造包含外层"List"的JSON结构。

修改后的PowerShell脚本：

$sqlQuery = @"
-- 原有的T-SQL代码
"@

$result = Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourServerName" -Database "YourDatabaseName" -Username "YourUsername" -Password "YourPassword" 

# 处理每个对象的sub字段，将字符串解析为JSON数组
$processedResult = $result | ForEach-Object {
    [PSCustomObject]@{
        MainId = $_.MainId
        Name = $_.Name
        sub = $_.sub | ConvertFrom-Json
    }
}

# 构造外层List结构并转换为JSON
$jsonResult = [PSCustomObject]@{
    List = $processedResult
} | ConvertTo-Json -Depth 10

$jsonFilePath = "C:\OutputFile.json"
$jsonResult | Out-File -FilePath $jsonFilePath -Encoding UTF8

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