Create json document from SQL Server query

Hi, I am working on creating json document from SQL Server query and save the resulting json resultset as a json document on a physical drive. I am able to generate json data but not sure how to save results as result.json to the drive “C:\jsonDocs\result.json”.

$InstanceName = "SQLDB\TST"
$connectionString = "Server=$InstanceName;Database=dbadb;Integrated Security=True;"
$query = "SELECT * FROM Employee"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object "System.Data.DataTable"
$table.Load($result)
$table | select $table.Columns.ColumnName | ConvertTo-Json | Set-Content "C:\JsonDocs\result.json"

$connection.Close()

Thank you.

Simply add a call to Set-Content “C:\jsonDocs\result.json”. after your convertTo-Json

 

e.g.

$table | select $table.Columns.ColumnName | ConvertTo-Json | Set-Content "C:\JsonDocs\result.json"

Alternatively, you can save your json to an object and then save the content as a separate step

e.g.

$Json = $table | select $table.Columns.ColumnName | ConvertTo-Json 

$Json | Set-Content "C:\JsonDocs\result.json"

one caution, if your data has multiple levels (unlikely in this case) then you may need to add a -Depth parameter to your ConvertTo-Json.

Hi Justin M, Thanks very much for your response. The solution works.

Also if I were to do the same call an api (GET) from powershell. How to save the results of json payload.

For instance, when I call the api http://myapi.com/v1/weatheradata. This gives me the below json payload.

I would now have this saved as “C:\jsonDocs\weather.json”.

{4 items
“message”:“accurate”
“cod”:“200”
“count”:1
“list”:[1 item
0:{11 items
“id”:2643743
“name”:“London”
“coord”:{2 items
“lat”:51.5085
“lon”:-0.1257
}
“main”:{6 items
“temp”:291.57
“feels_like”:288.33
“temp_min”:290.37
“temp_max”:292.59
“pressure”:1024
“humidity”:42
}
“dt”:1595275779
“wind”:{2 items
“speed”:3.1
“deg”:10
}
“sys”:{1 item
“country”:“GB”
}
“rain”:NULL
“snow”:NULL
“clouds”:{1 item
“all”:7
}
“weather”:[1 item
0:{4 items
“id”:800
“main”:“Clear”
“description”:“clear sky”
“icon”:“01n”
}
]
}
]
}

Regards.

you have several options, if you want to save the raw json, its the same

$downloadedjson | set-content 'filepath'

 

if you want you can also convert it to an object:

$MyObj = $downloadedJson | ConvertFrom-Json

 

you can also save this native “object” using

$downloadedJson | ConvertFrom-Json | Export-CliXML -Path 'objfile.xml'

and then can re-import using

$loadedobj = import-CliXml -path 'objfile.xml'

and you can convert that to json using:

 $loadedObj | ConvertTo-Json -Depth ##

where ## is an appropriate depth for the complexity of the object.

Hi Justin M, Thank you for your response. I am using the below. Please let me know if you see any issues with it.

<span style="font-family:">$username = ''</span>
<span style="font-family:">$password = ''</span>

<span style="font-family:">$params = @{uri = 'https:/api.myapi.com/v1/URIpath';</span>
<span style="font-family:">Method = 'Get'; </span>
<span style="font-family:">Headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("$($username):$($password)"));</span>
<span style="font-family:">} #end headers hash table</span>
<span style="font-family:">} #end $params hash table</span>

<span style="font-family:">$var = invoke-restmethod @params -ContentType "application/json".Content | ConvertTo-Json | Set-Content "C:\TEMP\data.json"</span>

Regards

i’m not familiar with bearer tokens myself, however if this is a rest method, you would better to use the Invoke-RestMethod API

 

if using PS6+, per the documentation:

-Token
The OAuth or Bearer token to include in the request. Token is required by certain Authentication options. It can't be used independently.

Token takes a SecureString that contains the token. To supply the token, manually use the following:

Invoke-RestMethod -Uri $uri -Authentication OAuth -Token (Read-Host -AsSecureString)

This parameter was introduced in PowerShell 6.0.

Type: SecureString
note the use of SecureString, in this example it prompts but if you ahve it in a variable you may need to convert it using:
$MySecureStr = $myStr | ConvertTo-SecureString -AsPlainText -Force
 

if using PS < 5, per the documentation

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-restmethod?view=powershell-5.1

This example demonstrates, how to pass multiple headers in from a hash-table to a REST API.

<code class="lang-powershell" data-author-content="$headers = @{
    'userId' = 'UserIDValue'
    'token' = 'TokenValue'
}
Invoke-RestMethod -Uri $uri -Method Post -Headers $headers -Body $body">$headers = @{
   <code class="lang-powershell" data-author-content="$headers = @{
    'userId' = 'UserIDValue'
    'token' = 'TokenValue'
}
Invoke-RestMethod -Uri $uri -Method Post -Headers $headers -Body $body">'userId' = 'UserIDValue'<code class="lang-powershell" data-author-content="$headers = @{
    'userId' = 'UserIDValue'
    'token' = 'TokenValue'
}
Invoke-RestMethod -Uri $uri -Method Post -Headers $headers -Body $body">
    'token' = 'TokenValue'
<code class="lang-powershell" data-author-content="$headers = @{
    'userId' = 'UserIDValue'
    'token' = 'TokenValue'
}
Invoke-RestMethod -Uri $uri -Method Post -Headers $headers -Body $body">} 

<code class="lang-powershell" data-author-content="$headers = @{
    'userId' = 'UserIDValue'
    'token' = 'TokenValue'
}
Invoke-RestMethod -Uri $uri -Method Post -Headers $headers -Body $body">Invoke-RestMethod -Uri $uri -Method Post -Headers $headers -Body $body

Thanks very much Justin M. I modified my code as well in the previous post.

Regards