json - 对象中的ConvertTo-Json，数组作为属性值

Question

在Windows Powershell中，我有一个简单的Invoke-SqlCmd查询，该查询返回下表:

ID Text
-- ----
 1 FOO 
 2 BAR 
 3 SPAM

将其传递到 | ConvertTo-Json -Depth 1会产生以下结果:

[
    {
        "RowError":  "",
        "RowState":  1,
        "Table":  "",
        "ItemArray":  "1 FOO",
        "HasErrors":  false,
        "ID":  1,
        "Text":  "FOO"
    },
    {
        "RowError":  "",
        "RowState":  1,
        "Table":  "",
        "ItemArray":  "2 BAR",
        "HasErrors":  false,
        "ID":  2,
        "Text":  "BAR"
    },
    {
        "RowError":  "",
        "RowState":  1,
        "Table":  "",
        "ItemArray":  "3 SPAM",
        "HasErrors":  false,
        "ID":  3,
        "Text":  "SPAM"
    }
]

我期望的输出不是这个裸数组，而是一个 对象，它具有单个 属性，具有“Products”作为名称，该数组作为值。

此外，我希望我的数组条目是仅具有SQL表列作为属性的objecs。

也就是说，我想要的输出将是:

{
    "Products": [
        {
            "ID": 1,
            "Text": "FOO"
        },
        {
            "ID": 2,
            "Text": "BAR"
        },
        {
            "ID": 3,
            "Text": "SPAM"
        }
    ]
}

我该如何实现？

编辑:SQL Server Management Studio中的查询及其结果如下:

Answer 1

假设$queryResult包含Invoke-SqlCmd调用的结果:

[pscustomobject] @{ 
  Products = @($queryResult | Select-Object ID, Text)
} | ConvertTo-Json

Select-Object 创建[pscustomobject]实例，这些实例仅包含代表实际表列的属性，在这种情况下，这些列是硬编码的。

注意:动态确定这些列名称可提供更通用的解决方案-请参阅下一节。

array-subexpression operator，

，

确保将结果视为一个数组(以防您的查询碰巧只返回一行)。

@()是用于创建包装[pscustomobject] @{ ... }实例的语法糖，该实例的唯一属性[pscustomobject]包含仅列值对象的数组。

Products将产生的自定义对象转换回JSON。

尽管这里不是问题，但请注意默认情况下递归深度限制为2，因此必须显式使用ConvertTo-Json以防止数据丢失-请参阅this post。

动态确定列名:

在 Mathias R. Jessen的帮助下， Fry Simpson制定了此解决方案。

通过调用 -Depth 返回一个 Invoke-SqlCmd实例
通过 System.Data.Table， -OutputAs DataTables可用于提取查询的列名作为数组:

# Note the use of `-OutputAs DataTables`; `-As DataTables` should work too.
$queryResult = Invoke-SqlCmd -OutputAs DataTables -Query "SELECT * FROM Schema1.dbo.Table_1" -ServerInstance $instanceName -Database $databaseName  

[pscustomobject] @{ 
  Products = @($queryResult | Select-Object $queryResult.Columns.ColumnName) 
} | ConvertTo-Json 

默认情况下， .Column.ColumnNames返回单个 Invovke-SqlCmd实例的流。

请注意，即使 System.Data.DataRow包含 $queryResult实例，也如何通过管道隐式发送其行；换句话说: System.Data.DataTable与 $queryResult | ...相同，后者是PowerShell中内置的行为。