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"
    }
]

Моим желаемым результатом был бы не этот пустой массив, а объект, имеющий единственное свойство, имеющее "Продукты" в качестве имени и массив в качестве значения.

Кроме того, я бы хотел, чтобы записи моего массива были объектами, имеющими в качестве свойств только столбцы таблицы SQL.

То есть мой желаемый результат был бы таким:

{
    "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] экземпляры, которые содержат свойства, представляющие только фактические столбцы таблицы, в данном случае жестко запрограммированные.

  • Примечание. Динамическое определение этих имен столбцов дает более общее решение - см. Следующий раздел.

• @(), оператор подвыражения массива, обеспечивает обработку результата как массива (в случае, если ваш запрос возвращает только одну строку).

• [pscustomobject] @{ ... } синтаксический сахар для создания оболочки [pscustomobject] экземпляр, единственное свойство которого, Products, содержит массив объектов, содержащих только значения столбцов.

• ConvertTo-Json преобразует полученный настраиваемый объект обратно в JSON.

  • Хотя это не проблема, обратите внимание, что глубина рекурсии по умолчанию ограничена до 2, что требует явного использования-Depthчтобы предотвратить потерю данных - см. этот пост.

---

Динамическое определение имен столбцов:

^{Mathias R. Jessen при помощи Fry Simpson разработал это решение.}

Сделав Invoke-SqlCmd вызов вернуть System.Data.Tableэкземпляр
через-OutputAs DataTables, .Column.ColumnNames может использоваться для извлечения имен столбцов запроса в виде массива:

# 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 

По умолчанию, Invovke-SqlCmd возвращает поток отдельных System.Data.DataRow экземпляры.

Обратите внимание, как использовать, даже когда $queryResult содержит System.Data.DataTableнапример, его строки неявно отправляются через конвейер; другими словами:$queryResult | ... такой же как $queryResult.Rows | ..., который является поведением, встроенным в PowerShell.