Powershell - Write JSON with column and row to MSSQL DB

Question

I have the json output in powershell as below type

IsPublic IsSerial Name                                     BaseType                                                                                                                                             
-------- -------- ----                                     --------                                                                                                                                             
True     True     String                                   System.Object

with content as below.

{
    "columns":  [
                    {
                        "name":  "@timestamp",
                        "type":  "datetime"
                    },
                    {
                        "name":  "first.hostname",
                        "type":  "text"
                    },
                    {
                        "name":  "username",
                        "type":  "text"
                    },
                    {
                        "name":  "payload",
                        "type":  "text"
                    },
                    {
                        "name":  "domain",
                        "type":  "text"
                    }
                ],
    "rows":  [
                 [
                     "2021-06-23T07:53:17.294Z",
                     "Name1",
                     "User1",
                     "Message",
                     "Domain"
                 ],
                 [
                    "2021-06-23T07:53:17.294Z",
                    "Name1",
                    "User1",
                    "Message",
                    "Domain"
                 ]
             ]
}

I need to be able to write this column and row mapping to a MSSQL DB. I am familiar with writing a datatable to DB in powershell. The part where i am struggling is to convert this content into a proper datatable.

Mathias R. Jessen · Accepted Answer · 2021-06-23 08:43:43Z

2

Create a [DataTable], then use the values from the columns array to define the columns, and the values from rows to populate the table:

# Convert JSON to a custom object
$tableDefinition = $json |ConvertFrom-Json

# Define type mappings
$typeMap = @{
  datetime = [datetime]
  text     = [string]
}

# Create a datatable to hold the data
$dataTable = [System.Data.DataTable]::new()

# Create column definitions from JSON
foreach($column in $tableDefinition.columns){
  [void]$dataTable.Columns.Add($column.name, $typeMap[$column.type])
}

# Populate table with rows
foreach($row in $tableDefinition.rows){
  [void]$dataTable.Rows.Add($row)
}

answered Jun 23, 2021 at 8:43

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Delphin Over a year ago

this worked and now i have the data in the DB. Awesome, thanks Mathias. Wish I can upvote it, but i do not have enough reputation :(

Mathias R. Jessen Over a year ago

@DelphinSA That's great! You can mark my answer "accepted" by clicking the checkmark on the left :-)

Collectives™ on Stack Overflow

Powershell - Write JSON with column and row to MSSQL DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related