Comparing CSV to JSON Objects within Powershell

Question

I am looking to build a basic config file using JSON. To begin with, I query a SQL database to get 3 values. These 3 values are stored in CSV, I would like to iterate through my JSON config file looking for a match. Should there be a match, I would like to pass the header and all the key pairs to a Powershell variable.

I have managed to get this working using a CSV as the config file but it seems JSON doesn't support the ".where" and I am completely stuck.

This is my JSON File. The 3 values I am comparing my SQL file are DFS, Message queue, errors. Should they match, I would like to eventually email the team assigned the 3 values.

    {
  "Messagequeue1": [
    {
      "dfs": "OAKHILL",
      "Messagequeue": "IMPORTSEQ",
      "errormsg": "Input in non-input field",
      "Ignore" : "false",
      "team": "[email protected]",
      "teamcc": "[email protected]",
      "escalate": "60"
    }
        ]
    ,"Messagequeue2": [
    {
      "dfs": "MQ",
      "Messagequeue": "MX",
      "errormsg": "ERRORMESS",
      "Ignore" : "false",
      "team": "[email protected]",
      "teamcc": "",
      "escalate": "60"
    }           
        ]
}

Powershell Below.

$json = "C:\Temp\Example.json"
$MessageQueue = Get-Content -Path $json | ConvertFrom-Json 

$Param = @{
    Header = 'DFS','MessageQueue','ErrorMessage','CreatedDate','StartTime'
    Path = 'C:\temp\file.csv'
}
$SqlFile = Import-Csv @Param | Select-Object -Skip 2 | Select-Object -SkipLast 2 

$teamemails = @{}
$itsemails = @()

$MessageQueue | gm -MemberType Methods

ForEach ($sqlerror In $SqlFile){
    
    if ($Config = $MessageQueue.where({ $_.DFS -Match $sqlerror.DFS -And $_.ErrorMsg -Match $sqlerror.ErrorMessage -And $_.MessageQueue -Match $sqlerror.MessageQueue}))
    {
    
        Write-Host "Match sending to " -NoNewline
        Write-Output -InputObject $Config.Team

        foreach ($c in $config)
        {
            $c.Resend
        
            if ($teamemails[$c.Team] -eq $null) {
                $teamemails[$c.Team] = @()
            }

            $teamemails[$c.Team] += $sqlerror
        }
    }
    
    else {
        Write-host  'No Match sending to default'
        $itsemails += $sqlerror
    }
}

At first glance, your $MessageQueue.where({...}) statement is incorrect, you're trying to refer to nested properties of Messagequeue1 and Messagequeue2. Try this for example to see the difference: $MessageQueue = Get-Content -Path $json | ConvertFrom-Json => $MessageQueue.PSObject.Properties.ForEach({$_.where({$_.Value.DFS -eq 'OAKHILL'})}).Value — Santiago Squarzon
– Santiago Squarzon, Commented Nov 30, 2021 at 21:48

Mathias R. Jessen · Accepted Answer · 2021-11-30 21:50:23Z

1

$MessageQueue contains a single custom object with two properties MessageQueue1 and MessageQueue2 - .Where({}) only operates meaningfully on collections.

You could change the condition to test @($MessageQueue.MessageQueue1, $MessageQueue.MessageQueue2).Where({...}), but this obviously breaks if the json file contains more properties.

You can use the hidden psobject memberset to enumerate the properties like this:

# create a flat array from the individual property values
$MessageQueue = $MessageQueue.psobject.Properties |ForEach-Object Value

... or use Get-Member to discover the property names:

# same as above, create a flat array from the individual property values
$MessageQueue = ($MessageQueue |Get-Member -MemberType Property) |ForEach-Object { $MessageQueue.$_ }

answered Nov 30, 2021 at 21:50

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

Collectives™ on Stack Overflow

Comparing CSV to JSON Objects within Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related