How to add multiple json objects to one json object in PowerShell

Question

cls

#function to Execute a stored procedure
function ExecSproc
{
    param ($Conn, $Sproc, $Parameters=@{})

    $MethodName = "ExecSproc"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
    $SqlCmd.Connection = $Conn
    $SqlCmd.CommandText = $Sproc
    foreach($p in $Parameters.Keys){
        [Void] $SqlCmd.Parameters.AddWithValue("@$p",$Parameters[$p])
    }
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)
    $DataSet = New-Object System.Data.DataSet
    [Void] $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    return $DataSet.Tables[0]
}

function ExecuteSqlQuery ($SQLQuery, $ConnectionString) 
{
    $Datatable = New-Object System.Data.DataTable

    $MethodName = "ExecuteSqlQuery"
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Datatable.Load($Reader)
    $Connection.Close()

    return $Datatable
}           

function SFSCheck()
{
    $OptionalField1 = ""
    $OptionalField2 = ""
    $SecondaryEmail = ""
    $EvaluationGroupCode = ""
    $DefaultRubricId = ""
    $DefaultWalktoolId = ""
    $EvaluatedInSFS = $true
    $EmploymentStatus = ""
    $Cert = ""
    $PrimaryEvalutor_payrollID = ""
    $SecondaryEvaluator_payrollID = ""
    $Access = ""
    $GoogleEmail = ""

    $PrimaryEvalutor_payrollID = $ManagerEmployeeNumber
    $OptionalField1 = ($EffectiveDate -as [string]).Split(" ")[0]

    $SFS = ExecSproc -Conn $AcctProConnectionString -Sproc SchoologySFSeSchoolGet -Parameters @{JobClassCode=$JobClassCode;Location=$CheckLocation}
    if ([string]::IsNullOrEmpty($SFS))
    {
        Write-Host "No Standards for Success account needed for combination of Job Class Code and location."
    }
    else
    {
        if ($SFS[7] -eq "Y")
        {
            if ($SFS[9] -ne "N")
            {
                $Access = $SFS[9]
                $Cert = $true

                #Check the Action Code to determine the status field
                if ($ActionCode -eq "LEAV")
                {
                    $EmploymentStatus = "Inactive"
                }
                elseif ($ActionCode -eq "TERM")
                {
                    $EmploymentStatus = "Remove"
                }
                elseif($ActionCode -eq "HIRE")
                {
                    $EmploymentStatus = "Add"
                }
                else
                {
                    $EmploymentStatus = ""
                }

                if(-not([string]::IsNullOrEmpty($StateId)))
                {
                    $StateId = $StateId.Substring(4).Trim()
                }

                #$personAsJson = "{SIEN=`"$StateId`";Payroll_id=`"$EmployeeNumber`";Email=`"$EmployeeEmail`";FirstName=`"$FirstName`";LastName=`"$LastName`";Position=`"$LongDescription`";Optional_Field1=`"$OptionalField1`";Optional_Field2=`"$OptionalField2`";Location_id=`"$CheckLocation`";Primary_Evalutor_id=`"$PrimaryEvalutor_payrollID`";Secondary_Evaluator_ids=`"$SecondaryEvaluator_payrollID`";EvalGroup_id=`"$EvaluationGroupCode`";Rubric_id=`"$DefaultRubricId`";Walk_id=`"$DefaultWalktoolId`";isEvaluated=`"$EvaluatedInSFS`";Employment_Status=`"$EmploymentStatus`";isCertified=`"$Cert`";Access=`"$Access`";Google_Email=`"$GoogleEmail`"}" 
                $User = New-Object Users
                $User.SIEN = $StateId
                $User.Payroll_id = $EmployeeNumber
                $User.Email = $EmployeeEmail
                $User.FirstName = $FirstName
                $User.LastName = $LastName
                $User.Position = $LongDescription
                $User.Optional_Field1 = $OptionalField1
                $User.Optional_Field2 = $OptionalField2
                $User.Location_id = $CheckLocation
                $User.Primary_Evaluator_id = $PrimaryEvalutor_payrollID
                $User.Secondary_Evaluator_ids = $SecondaryEvaluator_payrollID
                $User.EvalGroup_id = $EvaluationGroupCode
                $User.Rubric_id = $DefaultRubricId
                $User.Walk_id = $DefaultWalktoolId
                $User.is_Evaluated = $EvaluatedInSFS
                $User.Employment_Status = $EmploymentStatus
                $User.isCertified = $Cert
                $User.Access = $Access
                $User.Google_Email = $GoogleEmail

                $json.Add($User)
                #$response = Invoke-RestMethod 'http://example.com/api/SFS' -Method Put -Body $json

                #Write-Host "Adding $FirstName $LastName to Standards for Success file" -ForegroundColor Green
            }
        }
        else
        {
            Write-Host "Account does not need Standards for Success." -ForegroundColor Red
        }       
    }
}

$source = @"
public class Users
{
    public string SIEN;
    public string Payroll_id;
    public string Email;
    public string FirstName;
    public string LastName;
    public string Position;
    public string Optional_Field1;
    public string Optional_Field2;
    public string Location_id;
    public string Primary_Evaluator_id;
    public string Secondary_Evaluator_ids;
    public string EvalGroup_id;
    public string Rubric_id;
    public string Walk_id;
    public bool is_Evaluated;
    public string Employment_Status;
    public bool isCertified;
    public string Access;
    public string Google_Email;
}
"@

Add-Type -TypeDefinition $source

$AcctProConnectionString = "Server='167.217.10.172';Database='AccountProvision';trusted_connection=true;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $AcctProConnectionString

$Query = "SELECT TOP 3 * FROM FacultyAduit WHERE CaptureDateTime = '2017-03-16 10:00:00.000' ORDER BY CaptureDateTime DESC"
$AuditData = New-Object System.Data.DataTable
$AuditData = ExecuteSqlQuery -SQLQuery $Query -ConnectionString $AcctProConnectionString
$json = New-Object System.Collections.Generic.List[System.Object]

foreach($row in $AuditData)
{
    $EmployeeNumber = ""
    $FirstName = ""
    $LastName = ""
    $EffectiveDate = ""
    $JobClassCode = ""
    $LongDescription = ""
    $StateId = ""
    $CheckLocation = ""
    $ManagerEmployeeNumber = ""
    $ActionCode = ""
    $EmployeeEmail = ""

    $EmployeeNumber = $Row[2]
    $FirstName = $row[3].ToLower()
    $LastName = $Row[4].Replace(" ", "").Replace("-","").Replace("'","").ToLower()
    $EffectiveDate = $row[7]
    $JobClassCode = $row[16]
    $LongDescription = $Row[18].ToLower()
    $StateId = $Row[28]
    $CheckLocation = $Row[29]
    $ManagerEmployeeNumber = $row[33]
    $ActionCode = $Row[36]
    $EmployeeEmail = $Row[37].ToLower()

    SFSCheck
    Write-Host ""
}

$json | ConvertTo-Json

I have a script that generates json user objects such as this:

{
"SIEN" : "",
"Payroll_id" : "",
"Email" : "[email protected]",
"FirstName" : "102_TEACHER",
"LastName" : "SANDBOX",
"Position" : "",
"Optional_Field1" : "",
"Optional_Field2" : "",
"Location_id" : "B102",
"EvalGroup_id" : "2182",
"Rubric_id" : "2344",
"Walk_id" : "",
"Employment_Status" : "active",
"isCertified" : true,
"Google_Email" : ""
}

However I would like to generate ONE json object that includes all of the user objects that I am generating that looks like this:

{
"Users":
[
    {
        "SIEN":  "",
        "Payroll_id":  "1203",
        "Email":  "[email protected]",
        "FirstName":  "shannon",
        "LastName":  "hild",
        "Position":  "media assistant",
        "Optional_Field1":  "01/09/1989",
        "Optional_Field2":  "",
        "Location_id":  "B094",
        "Primary_Evalutor_id":  "12269",
        "Secondary_Evaluator_ids":  "",
        "EvalGroup_id":  "",
        "Rubric_id":  "",
        "Walk_id":  "",
        "isEvaluated":  "True",
        "Employment_Status":  "Inactive",
        "isCertified":  "True",
        "Access":  "Staff - Certified",
        "Google_Email":  ""
    },
    {
        "SIEN":  "10804749",
        "Payroll_id":  "8895",
        "Email":  "[email protected]",
        "FirstName":  "cynthia",
        "LastName":  "chizer",
        "Position":  "special education tchr",
        "Optional_Field1":  "08/25/1999",
        "Optional_Field2":  "",
        "Location_id":  "B105",
        "Primary_Evalutor_id":  "558",
        "Secondary_Evaluator_ids":  "",
        "EvalGroup_id":  "",
        "Rubric_id":  "",
        "Walk_id":  "",
        "isEvaluated":  "True",
        "Employment_Status":  "Inactive",
        "isCertified":  "True",
        "Access":  "Staff - Certified",
        "Google_Email":  ""
    }
]
}

I am sending this one json object to an API, but am unsure how to append the users to the one object. I've tried creating a string and adding everything to one string and converting that string to a json object, however this does not work either. Any help is much appreciated.

As JSON is a known format in powershell, you could just create an array and add all your objects to it. Use ConvertFrom-Json for getting your objects and use ConvertTo-Json to export the array. — restless1987
– restless1987, Commented Mar 17, 2017 at 16:30
If your code contains sensitive data you can still post it : just obfuscate the sensitive data. — bluuf
– bluuf, Commented Mar 17, 2017 at 16:32
I have updated the description to include my code so you can see what I am attempting to do — johnnyjohnson
– johnnyjohnson, Commented Mar 20, 2017 at 15:20

rufer7 · Accepted Answer · 2017-03-20 16:00:54Z

1

You can do by creating an array list, converting the user from JSON to a PSCustomObject by using ConvertFrom-Json, add the user to the list and convert the list back to JSON by using ConvertTo-Json.

$users = [System.Collections.ArrayList]::new();

$userAsJson = '
{
    "SIEN" : "",
    "Payroll_id" : "",
    "Email" : "[email protected]",
    "FirstName" : "102_TEACHER",
    "LastName" : "SANDBOX",
    "Position" : "",
    "Optional_Field1" : "",
    "Optional_Field2" : "",
    "Location_id" : "B102",
    "EvalGroup_id" : "2182",
    "Rubric_id" : "2344",
    "Walk_id" : "",
    "Employment_Status" : "active",
    "isCertified" : true,
    "Google_Email" : ""
}';
$user = $userAsJson | ConvertFrom-Json;
$null = $users.Add($user);
$null = $users.Add($user);

$result = @{ Users = $users};

$result | ConvertTo-Json;

This produces the following output

{
    "Users":  [
                  {
                      "SIEN":  "",
                      "Payroll_id":  "",
                      "Email":  "[email protected]",
                      "FirstName":  "102_TEACHER",
                      "LastName":  "SANDBOX",
                      "Position":  "",
                      "Optional_Field1":  "",
                      "Optional_Field2":  "",
                      "Location_id":  "B102",
                      "EvalGroup_id":  "2182",
                      "Rubric_id":  "2344",
                      "Walk_id":  "",
                      "Employment_Status":  "active",
                      "isCertified":  true,
                      "Google_Email":  ""
                  },
                  {
                      "SIEN":  "",
                      "Payroll_id":  "",
                      "Email":  "[email protected]",
                      "FirstName":  "102_TEACHER",
                      "LastName":  "SANDBOX",
                      "Position":  "",
                      "Optional_Field1":  "",
                      "Optional_Field2":  "",
                      "Location_id":  "B102",
                      "EvalGroup_id":  "2182",
                      "Rubric_id":  "2344",
                      "Walk_id":  "",
                      "Employment_Status":  "active",
                      "isCertified":  true,
                      "Google_Email":  ""
                  }
              ]
}

edited Mar 20, 2017 at 16:00

answered Mar 17, 2017 at 16:32

rufer7

4,2293 gold badges26 silver badges31 bronze badges

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

2 Comments

johnnyjohnson Over a year ago

I've tried converting from json to json, but when I try this way the json gets all messed up with imporper formatting.

rufer7 Over a year ago

@johnnyjohnson I realized, that you expect a JSON object called Users that contains the list of users. I edited my answer so that the ouptut matches your required result. Ok for you?

Collectives™ on Stack Overflow

How to add multiple json objects to one json object in PowerShell

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related