Powershell JSON data group by-CodePudding

I'm working on automation to trigger emails wherin I need to send an email to each email ID(TestValue3) which is read from the JSON file and body of the email should be containing the values of TestValue1, TestValue2 & TestValue4 respective to email ID.

Here same email IDs are appearing more than once so instead of sending multiple emails, I need to group all the information of respective email ID(which are repeating) and send it as one email.

{
"Data":  [
             {
                 "TestValue1":  "MyCode35",
                 "TestValue2":  "Some Test Value1",
                 "TestValue3":  "[email protected]",
                 "TestValue4":  "Account1"
             },
             {
                 "TestValue1":  "MyCode38",
                 "TestValue2":  "Some Test Value2",
                 "TestValue3":  "[email protected]",
                 "TestValue4":  "Account2"
             },
             {
                 "TestValue1":  "MyCode56",
                 "TestValue2":  "Some Test Value2",
                 "TestValue3":  "[email protected]",
                 "TestValue4":  "Account3"
             },
             {
                 "TestValue1":  "MyCode35",
                 "TestValue2":  "Some Test Value1",
                 "TestValue3":  "[email protected]",
                 "TestValue4":  "Account8"
             },
             {
                 "TestValue1":  "MyCode35",
                 "TestValue2":  "Some Test Value1",
                 "TestValue3":  "[email protected]",
                 "TestValue4":  "Account11"
             },
             {
                 "TestValue1":  "MyCode88",
                 "TestValue2":  "Some Test Value1",
                 "TestValue3":  "[email protected]",
                 "TestValue4":  "Account32"
             },
             {
                 "TestValue1":  "MyCode45",
                 "TestValue2":  "Some Test Value1",
                 "TestValue3":  "[email protected]",
                 "TestValue4":  "Account18"
             }
        ]
}

After updating the code, it returned grouped data.

$json = Get-Content -Raw -Path .\filename.json | ConvertFrom-Json
$json | ForEach-Object Data | Group-Object -Property TestValue3

Sample email body wherein I will be using these TestValue1, TestValue2 & TestValue4

User Details

Codes Are: $TestValue1
Test Value Are: $TestValue2
Accounts Are: $TestValue4

Any idea or suggestions?

CodePudding user response：

Changed the data to better illustrate the usage...

$json = @'
{
    "Data": [
                {
                    "Codes":      "MyCode35",
                    "TestValue":  "Some Test Value1",
                    "Recipient":  "[email protected]",
                    "Account":    "Account1"
                },
                {
                    "Codes":      "MyCode38",
                    "TestValue":  "Some Test Value2",
                    "Recipient":  "[email protected]",
                    "Account":    "Account2"
                },
                {
                    "Codes":      "MyCode56",
                    "TestValue":  "Some Test Value2",
                    "Recipient":  "[email protected]",
                    "Account":    "Account3"
                },
                {
                    "Codes":      "MyCode35",
                    "TestValue":  "Some Test Value1",
                    "Recipient":  "[email protected]",
                    "Account":    "Account8"
                },
                {
                    "Codes":      "MyCode35",
                    "TestValue":  "Some Test Value1",
                    "Recipient":  "[email protected]",
                    "Account":    "Account11"
                },
                {
                    "Codes":      "MyCode88",
                    "TestValue":  "Some Test Value1",
                    "Recipient":  "[email protected]",
                    "Account":    "Account32"
                },
                {
                    "Codes":      "MyCode45",
                    "TestValue":  "Some Test Value1",
                    "Recipient":  "[email protected]",
                    "Account":    "Account18"
                }
            ]
}
'@

Group and traverse...

$SmtpGateway = '127.0.0.1'

$GroupedRecipents = $json | ConvertFrom-Json | select -ExpandProperty Data | group Recipient

foreach ($GroupItem in $GroupedRecipents){
    $body = $GroupItem.Group | Select * -ExcludeProperty Recipient | ConvertTo-Html -Fragment
    Send-MailMessage `
        -From '[email protected]' `
        -SmtpServer $SmtpGateway `
        -To $GroupItem.Name `
        -Subject 'Here is the mail' `
        -BodyAsHtml "$body"
}