Powershell - Update .JSON

Question

I need some pointers on how to update values in my .json file. Here is a sample fruits.json that I have:

   [
    {
        "ExpireDate":  "",
        "Origin":  "",
        "FruitName":  "Apple"
    },
    {
        "Expiredate":  "",
        "Origin":  "",
        "FruitName":  "Orange"
    }
    ]

I have another .JSON file call fruitdetail.json serving as an input file. I need to update the above fruits.json with the info from fruitdetail.json. Basically, lookup the "Fruit Name" from fruitdetail.json and update both "Origin" and "Expire date" keys in fruits.json. Below is what I am starting out with but it doesn't look right.

$fruits = (Get-Content -Path "C:\temp\fruits.json" | ConvertFrom-Json)
$fruitDetail = (Get-Content -Path "C:\temp\fruitdetail.json" | ConvertFrom-Json)

$json = foreach ($fruit in $fruits) {
    If ($fruit.FruitName = $fruitDetail.FruitName) {
        $Fruit.Origin = $FruitDetail.Origin
        $Fruit.ExpireDate = $FruitDetail.ExpireDate
    }
}

$json.update

Thanks in advance.

UPDATE: Updated the above code

Sounds like a school appointment. What have you researched so far? — marsze, Oct 15 '20 at 17:34
Updated the original code. Thinking of using foreach but not sure where to go from there. — Ultra GC, Oct 15 '20 at 20:19

Steven · Accepted Answer · 2020-10-19T13:18:10.200

You're trying to relate data using a common field, similar to an SQL Join. Presumably the fruit name in can be used in this case and for demonstration purposes. Not sure this is perfect, but it should demonstrate the concept.

# Establish Test Date
$JSON_Fruit =
@"
[
{
    "Expire Date":  "",
    "Origin":  "",
    "Fruit Name":  "Apple"
},
{
    "Expire date":  "",
    "Origin":  "",
    "Fruit Name":  "Orange"
}
]
"@  | 
ConvertFrom-Json

# Establish Test Date
$JSON_FruitDetail = @{}
(@"
[
{
    "Expire Date":  "10/27/2020",
    "Origin":  "California",
    "Fruit Name":  "Apple"
},
{
    "Expire date":  "11/10/2020",
    "Origin":  "Arizona",
    "Fruit Name":  "Orange"
}
]
"@ | 
ConvertFrom-Json) |
ForEach-Object{
    $JSON_FruitDetail.Add( $_.'Fruit Name', $_ )
}


ForEach( $Fruit in $JSON_Fruit )
{    
    $Fruit."Expire Date" = $JSON_FruitDetail[$Fruit.'Fruit Name']."Expire Date"
    $Fruit.Origin        = $JSON_FruitDetail[$Fruit.'Fruit Name'].Origin
}

$JSON_Fruit

Note: The parenthesis are to avoid a problem in PowerShell versions prior to 7 (both Windows and Core) In PowerShell 7+ you don't need to use them. For more info see this answer and this one .

Basically you're converting the JSON into PSCustom objects via ConvertFrom-Json. Then storing one side of the data in a dictionary collection keyed on the common field, in this example the "Fruit Name". Then we're looping through the object collection using the "Fruit Name" to reference the entry in the hash table and make the assignment to the destination object's properties.

Note: I've seen a number of PowerShell functions in the community with names like Join-Object that may also be suitable for this, but I usually use techniques like above.

Thanks Steve. That gives me a very good idea of where to go from here. — Ultra GC, Oct 16 '20 at 05:03

Powershell - Update .JSON

1 Answers1