CODEWITHSUNDEEP
jsonpowershell
Kishori
Kishori

Reputation: 151

Compare 2 JSON and retrieve subset from one of them based on condition in Powershell

I have two JSON files abc.json and xyz.json. Content in abc.json is:

[{"id": "121",
  "name": "John",
  "location": "europe"
 },
 {"id": "100",
  "name": "Jane",
  "location": "asia"
 },
 {"id": "202",
  "name": "Doe",
  "location": "america"
 }
]

Updated -> Content in xyz.json is:

{
  "value": [
    {
     "id": "111",
     "city": "sydney",
     "profession": "painter"
     },
     {
      "id": "200",
      "city": "istanbul",
      "profession": "actor"
     },
     {
      "id": "202",
     "city": "seattle",
     "profession": "doctor"
     }
    ],
   "count": {
    "type": "Total",
    "value": 3
  }
}

I want to get those records of abc.json in when the id in both objects are equal.In this case:

{"id": "202",
  "name": "Doe",
  "location": "america"
 }

I need to do this in Powershell and the version I am using is 5.1.This is what I have tried:

$OutputList = @{}

$abcHash = Get-Content 'path\to\abc.json' | Out-String | ConvertFrom-Json
$xyzHash = Get-Content 'path\to\xyz.json' | Out-String | ConvertFrom-Json
$xyzResp = $xyzHash.value

foreach($item in $xyzResp){
    foreach ($record in $abcHash){
        if ($item.id -eq $record.id){
          $OutputList.Add($record, $null)
        }
    }
}

Write-Output $OutputList

But on printing the OutputList , I get like this:

Key: @{"id": "202",
  "name": "Doe",
  "location": "america"
 }
Value:
Name:@{"id": "202",
  "name": "Doe",
  "location": "america"
 }

What I require is more of a PSObject like:

id: 202
name:Doe
location:america

I tried using Get-Member cmdlet but could not quite reach there. Is there any suggestion I could use?

Upvotes: 1

Views: 146

Answers (1)

Theo
Theo

Reputation: 61093

I have corrected your example xyz.json because there was an extra comma in there that should not be there. Also, the example did not have an iten with id 202, so there would be no match at all..

xyz.json

{
  "value": [
    {
     "id": "111",
     "city": "sydney",
     "profession": "painter"
     },
     {
      "id": "202",
      "city": "denver",
      "profession": "painter"
     },
     {
      "id": "111",
     "city": "sydney",
     "profession": "painter"
     }
    ],
   "count": {
    "type": "Total",
    "value": 3
  }
}

That said, you can use a simple Where-Object{...} to get the item(s) with matching id's like this:

$abc = Get-Content 'path\to\abc.json' -Raw | ConvertFrom-Json
$xyz = Get-Content 'path\to\xyz.json' -Raw | ConvertFrom-Json

# get the items with matching id's as object(s)
$abc | Where-Object { $xyz.value.id -contains $_.id}

Output:

id  name location
--  ---- --------
202 Doe  america

Of course you can capture the output first and display as list and/or save to csv, convert back to json and save that.

Upvotes: 1

Related Questions