Question 416233 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to put data from CSV to JSON?

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.

Tags:

2

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    Here’s a jq-only solution, which does however assume the CSV is easy to parse:

    < input.json jq --rawfile csv input.csv '
  def toDict: reduce .[] as [$v, $k] ({}; .[$k] = $v);
  ($csv
   | split("n")
   | map(select(length>0) | split(","))
   | toDict) as $dict
  | walk(if type == "object" and .watchers 
         then .watchers |= map( $dict[.] // .) end)
'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search