I am working on a project to extract rows from a large JSON document and insert them into an SQL Server database.
The current code I have successfully inserts one row at a time into the table, which seems to be every 1000th row.
add-type -path "C:\Program Files\WindowsPowerShell\Modules\newtonsoft.json\1.0.2.201\libs\newtonsoft.json.dll"
install-module sqlserver -AllowClobber
class EliteCoords {
[double] $x
[double] $y
[double] $z
}
class EliteSystem {
[int] $id
[long] $id64
[string] $name
[EliteCoords] $coords
[string] $date
}
$dt = New-Object system.data.datatable
$dt.Columns.Add("ID",[int])
$dt.Columns.Add("ID64",[long])
$dt.Columns.Add("Name",[string])
$dt.Columns.Add("Coordsx",[decimal])
$dt.Columns.Add("Coordsy",[decimal])
$dt.Columns.Add("Coordsz",[decimal])
$dt.Columns.Add("DiscoveryDate",[string])
$stream = (Get-Item c:\code\systemsWithCoordinates.json).OpenText()
$reader = [Newtonsoft.Json.JsonTextReader]::new($stream)
while ($reader.Read()) {
$cnt = 0
$dt.Clear()
while ($cnt -le 1000) {
$cnt = $cnt + 1
if ($reader.TokenType -eq 'StartObject') {
$row = [Newtonsoft.Json.JsonSerializer]::CreateDefault().Deserialize($reader, [EliteSystem])
$dr = $dt.NewRow()
$dr["ID"] = $row.id
$dr["ID64"] = $row.id64
$dr["Name"] = $row.name
$dr["Coordsx"] = $row.coords.x
$dr["Coordsy"] = $row.coords.y
$dr["Coordsz"] = $row.coords.z
$dr["DiscoveryDate"] = $row.date
$dt.Rows.Add($dr)
}
}
write-sqltabledata -ServerInstance ELITEDANGEROUS -Database EDSM -Schema Staging -Table SystemsWithCoordinates -InputData $dt
}
$stream.Close()
I have identified that the issue lies in skipping the if block for all iterations except the first within the inner while loop due to changes in token type from StartObject to StartArray.
Attempts to resolve this include adding another reader loop inside, but it ends up reading the entire file or simply reading the array instead of object, which fails due to nested JSON structure.
To batch process and handle 1000 rows effectively, what would be the optimal way to structure the loops?