ConvertTo-Json in object with array as property value

In Windows Powershell I have a simple Invoke-SqlCmd query which returns the following table:

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

Piping it to | ConvertTo-Json -Depth 1 yields the following result:

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

My desired output would be not this bare array, but an object having a single property having "Products" as name and the array as value.

Besides, I'd like my array entries being objecs having just the columns of the SQL table as properties.

That is, my desired output would be:

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

How can I achieve it?

---

EDIT: The query and its result from SQL Server Management Studio are the following:

Assuming $queryResult contains the result of your Invoke-SqlCmd call:

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

• Select-Object creates [pscustomobject] instances that contain the properties representing actual table columns only, hard-coded in this case.

  • Note: Determining these column names dynamically makes for a more generic solution - see next section.

• @(), the array-subexpression operator, ensures that the result is treated as an array (in case your query happens to return just one row).

• [pscustomobject] @{ ... } is syntactic sugar for creating the wrapper [pscustomobject] instance, whose only property, Products, contains the array of column-values-only objects.

• ConvertTo-Json converts the resulting custom object back to JSON.

  • While not an issue here, do note that recursion depth is limited to 2 by default, necessitating explicit use of -Depth to prevent data loss - see this post.

---

Determining the column names dynamically:

^{Mathias R. Jessen, assisted by Fry Simpson, worked out this solution.}

By making the Invoke-SqlCmd call return a System.Data.Table instance
via -OutputAs DataTables, .Column.ColumnNames can be used to extract the query's column names as an array:

# 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 

By default, Invovke-SqlCmd returns a stream of individual System.Data.DataRow instances.

Note how using even when $queryResult contains a System.Data.DataTable instance, its rows are implicitly sent through the pipeline; in other words: $queryResult | ... is the same as $queryResult.Rows | ..., which is behavior built into PowerShell.