How to convert DataTable to JSON using ConvertTo-json in Powershell v3

Question

I have a DataTable $dt with same data, I would like to pipe the data to JSON using the cmdlet ConvertTo-JSON in Powershell v3

$ds.Tables["mytable"] | ConvertTo-Json

The result is all the properties of the DataTable are returned, but I only need the records in the data table.

I am wondering if there is a way to do this without looking through each column/ row and adding then into a custom object..etc.

This is what I get when I run the above line;

[
    {
        "RowError":  "",
       "RowState":  2,
        "Table":  {
                  "CaseSensitive":  false,
                  "IsInitialized":  true,
                  "RemotingFormat":  0,
                  "ChildRelations":  "",
                  "Columns":  "Id Name IsActive",
                  "Constraints":  "",
                  "DataSet":  "System.Data.DataSet",
                  "DefaultView":  "System.Data.DataRowView System.Data.DataRowView",
                  "DisplayExpression":  "",
                  "ExtendedProperties":  "System.D.......

Thanks

Yasir

http://www.sqlist.co.uk

Answer 1

After playing with a sample datatable a bit, I ended up with this:

($ds.Tables["mytable"] | select $ds.Tables["mytable"].Columns.ColumnName ) | ConvertTo-Json