How to put data from CSV to JSON?

Question

I have a CSV containing two "columns" 'User id' and 'email' example:

User id,email
1234-1234-1234,[email protected]
321-1235-44432,[email protected]
322136231345,[email protected]

And a JSON looking like this:

    [{
            "externalId": "100000",
            "watchers": ["[email protected]", "[email protected]", "[email protected]"]
        },
{
            "externalId": "100002",
            "watchers": ["[email protected]", "[email protected]"]
        }
    ]

What I'm trying to do is to replace the email addresses in the JSON with the 'User id' from the CSV accordingly. So far I have the inefficient foreach in foreach code but it only replaces the first email in the watchers array.

$usersCSV = Import-Csv 'users.csv'
$watchersJSON = Get-Content -Path "watchers.json" -raw |ConvertFrom-Json

foreach ($watchersJSONdata in $watchersJSON) {
    foreach ($usersCSVdata in $usersCSV){
        if ($watchersJSONdata.watchers -eq $usersCSVdata.email) {
            $watchersJSONdata.watchers = $usersCSVdata.'User id'
        }
    }
} $watchersJSON |ConvertTo-Json | out-file  "watchers-with-ID.json"

Result is:

[{
        "externalId": "100000",
        "watchers": ["1234-1234-1234"]
    }
]

I'm still working on it but a little help would be great.

I wouldn't mind a completely different approach using a single line jq but I don't know jq at all.

Answer 1

Easiest and more efficient way to do it is with Group-Object -AsHashtable to leverage a dictionary type for fast lookups. See about_Hash_Tables for more details.

$usersCSV = Import-Csv 'users.csv' | Group-Object email -AsHashTable
$watchersJSON = Get-Content -Path 'watchers.json' -Raw | ConvertFrom-Json
foreach ($object in $watchersJSON) {
    $object.watchers = $usersCSV[$object.watchers].'User id'
}
ConvertTo-Json @($watchersJSON) | Out-File 'watchers-with-ID.json'