Automated API cURL command with SQL to upload data

PowerShell Help
1

I’m hoping someone can help me and point me to some useful resources.

I’ve been tasked with creating an export of data from SQL which needs to be in the json format, and then bulk upload to create records on a third parties cloud based software. The documentation requires me to upload the data via a cURL command. I have produced an SQL query which can output the data into json, and with the integration of SQL in PowerShell, I wondered if it would be the right tool for the job?

The example command I have requires the json data to be read from a file, which can then be uploaded via the web link using an api key.

I’ve been looking into how this could be achieved, and have found articles on using Invoke-WebRequest, but not seen much with regards to reading a json file containing the data to upload. Is this possible in PowerShell? The version installed on the machine is 5.1.

The example command is:

curl -F “file=@/path_to_file/filename.json” https://site-domain-name/api/enrollearners?apikey=yourkey

2

The -Body would be the JSON payload, which can be retrieved with Get-Content for a file or better to get the JSON directly, but you didn’t specify how the JSON is generated. The -Uri is https://site-domain-name/api/enrollearners?apikey=yourkey. API’s normally want a Method like PUT or POST to SET content, so you should look at the documentation and provide the correct -Method.

3

Thank you for the response I will look into Get-Content.

In answer to you question about the JSON, I’m generating the it directly using sql and the FOR JSON clause. I’m attempting to build this into the full script, but am having difficulty so far. I’m using the bcp utility to try and run and then export the data into a JSON file, but get the error:

Copy direction must be either 'in', 'out' or 'format'.
I have included the copy direction, but not sure why this error is being returned. Here is my script so far:

$bcp = “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe”
$db = “DB_Server_Name”
$view = “SELECT * FROM sql_view_name FOR JSON PATH, ROOT (‘data’)”
$path = “C:\temp\json_file_name.json”
$bcpCommand = “$($view) out $path -S $db -T -E -n”
& $bcp @bcpCommand

Am I missing something in order for this to run correctly?

4

There are Powershell commands to execute SQL. With the SQL Management studio installed, it additionally installs a Powershell module. I’ve not returned JSON from a command, but typically I return the data I need and then use ConvertTo-Json. Use Invoke-SqlCmd:

$sqlCmd = @"
SELECT * 
FROM sql_view_name
"@

$results = Invoke-Sqlcmd -Query $sqlCmd -ServerInstance "MyComputer\MainInstance"

Once I have the results, which we’ll just say is a FirstName and LastName, then you can build the JSON like this:

$results = [pscustomobject]@{
    FirstName = 'John'
    LastName = 'Smith'
}

#Add data root
$data = [pscustomobject]@{
    data = $results
}

$body = ConvertTo-Json -InputObject $data

Output:

{
    "data":  {
                 "FirstName":  "John",
                 "LastName":  "Smith"
             }
}
5

Thank you again Rob.

I have something to generate the JSON now, however the structure I require needs to have an additional array in the JSON. The structure I need is as follows:

{
"data":[
{
"firstname":"John",
"lastname":"Smith",
"courses":[
{
"projectid":"1",
"courseid":"1",
"startdate":"01/09/2020"
}
] }
] }

In my SQL view I have setup the aliases for the array sections to courses.projectid, courses.courseid and courses.startdate. This works when using the FOR JSON, this doesn’t work using ConvertTo-Json. Can the sql aliases still get picked up using ConverTo-Json, or do I need to add something else for this to work?

6

On the Aliases, I would say no, that’s an internal method of grouping in SQL. Two choices:

  • Use SQL to build the JSON - You could probably return the JSON as like a nvarchar(max), but don't know how large this file would be with the number of records you are getting, but you could do something like this: 
    $sqlCmd = @"
SELECT JSON_RESULT 
FROM (
    SELECT * 
    FROM sql_view_name
)
"@

$results = Invoke-Sqlcmd -Query $sqlCmd -ServerInstance "MyComputer\MainInstance"
$body = $results.JSON_RESULT
  • Use Powershell to build it the JSON - The data from SQL would be a relational structure and you are getting a flat result back, so it would be more like this: 
    $data = @()
$data += [pscustomobject]@{
    'id'                = 1
    'firstname'         = 'John'
    'lastname'          = 'Smith'
    'courses.projectid' = 1
    'courses.courseid'  = 1
    'courses.startdate' = Get-Date
}
$data += [pscustomobject]@{
    'id'                = 1
    'firstname'         = 'John'
    'lastname'          = 'Smith'
    'courses.projectid' = 2
    'courses.courseid'  = 2
    'courses.startdate' = Get-Date
}
$data += [pscustomobject]@{
    'id'                 = 2
    'firstname'         = 'Sally'
    'lastname'          = 'Franklin'
    'courses.projectid' = 3
    'courses.courseid'  = 3
    'courses.startdate' = Get-Date
}
$data += [pscustomobject]@{
    'id'                 = 2
    'firstname'         = 'Sally'
    'lastname'          = 'Franklin'
    'courses.projectid' = 2
    'courses.courseid'  = 2
    'courses.startdate' = Get-Date
}



$results = foreach ($grp in $data | Group-Object -Property id) {
    [pscustomobject]@{
        Data = $grp.Group[0] | Select firstname,lastname
        Courses = $grp.Group | Select courses.projectid,courses.courseid,courses.startdate
    }
}

$results

$body = ConvertTo-Json -InputObject $results
$body

    Generating:

    PS C:\Users\rasim> $results

Data                                  Courses                                                                                                                                                                 
----                                  -------                                                                                                                                                                 
@{firstname=John; lastname=Smith}     {@{courses.projectid=1; courses.courseid=1; courses.startdate=5/22/2020 4:24:41 PM}, @{courses.projectid=2; courses.courseid=2; courses.startdate=5/22/2020 4:24:41 PM}}
@{firstname=Sally; lastname=Franklin} {@{courses.projectid=3; courses.courseid=3; courses.startdate=5/22/2020 4:24:41 PM}, @{courses.projectid=2; courses.courseid=2; courses.startdate=5/22/2020 4:24:41 PM}}



PS C:\Users\rasim> $body
[
    {
        "Data":  {
                     "firstname":  "John",
                     "lastname":  "Smith"
                 },
        "Courses":  [
                        "@{courses.projectid=1; courses.courseid=1; courses.startdate=05/22/2020 16:24:41}",
                        "@{courses.projectid=2; courses.courseid=2; courses.startdate=05/22/2020 16:24:41}"
                    ]
    },
    {
        "Data":  {
                     "firstname":  "Sally",
                     "lastname":  "Franklin"
                 },
        "Courses":  [
                        "@{courses.projectid=3; courses.courseid=3; courses.startdate=05/22/2020 16:24:41}",
                        "@{courses.projectid=2; courses.courseid=2; courses.startdate=05/22/2020 16:24:41}"
                    ]
    }
]
7

By the way, curl.exe comes with Windows 10 now. I use it when I don’t want to send any headers.

8

Thank you for the responses, I’m almost there with it now. But I’ve noticed that my output doesn’t include the square brackets for a json array.

Any ideas why this might be, I’ve ran my json through an Invoke-WebRequest and and Invoke-RestMethod, but no users appear to have been created on the third party software, and I’m wondering if it is because of the square brackets issue?

This is my code in full now:

$sqlCmd = @"
SELECT *
FROM SQL_View
"@

$results = Invoke-SqlCmd -Query $sqlCmd -ServerInstance "SQL_DB"

$data = foreach ($grp in $results | Group-Object -Property idnumber){
[ordered]@{
data = $grp.Group[0] | Select email,firstname,surname,idnumber,title,gender,nino
courses = $grp.Group | Select courses.projectid,courses.courseid,courses.startdate
}
}

$body = ConvertTo-Json -InputObject $data

$sendInfo = Invoke-RestMethod -Uri 'https://site-domain-name/api/enrollearners?apikey=mykey' -Body $body -Method 'POST' -ContentType "application/json"

JSON output:

{
    "data":
        {
            "email":"john.smith@somewhere.com"
            "firstname":"John",
            "lastname":"Smith",
            "idnumber":"1234",
            "title":"Mr",
            "gender":"M",
            "nino":"AB123456C"
        "courses":
        {
            "projectid":"1",
            "courseid":"1",
            "startdate":"01/09/2020"
        }
    }
}

JSON output needed:

{
    "data":[
        {
            "email":"john.smith@somewhere.com"
            "firstname":"John",
            "lastname":"Smith",
            "idnumber":"1234",
            "title":"Mr",
            "gender":"M",
            "nino":"AB123456C"
        "courses":[
        {
            "projectid":"1",
            "courseid":"1",
            "startdate":"01/09/2020"
        }
        ]
    }
    ]
}
9

Try making them an array:

$data = foreach ($grp in $results | Group-Object -Property idnumber){
[ordered]@{
data = @($grp.Group[0] | Select email,firstname,surname,idnumber,title,gender,nino)
courses = @($grp.Group | Select courses.projectid,courses.courseid,courses.startdate)
}
}
10

Thanks Rob, I’ve just made the amendment, although it has change the output to the following:

[
    {
    "data":[
        @{"email":"john.smith@somewhere.com";"firstname":"John";"lastname":"Smith";"idnumber":"1234";"title":"Mr";"gender":"M";"nino":"AB123456C"}
    ],
        "courses":[
        @{"projectid":"1";"courseid":"1";"startdate":"01/09/2020"}
        ]
    }
]