Powershell joining 2 different JSON files

Question

I have 2 JSON files that I would like to combine in order to pull out some meaningful data. The first file contains groups and details. The second file contains a list of records that are attached to the group.

File 1:

{
    "count":  1,
    "results":  [
                    {
                        "key":  "card_sets",
                        "id":  "551175"
                    }
                ],
    "card_sets":  {
                           "551175":  {
                                          "title":  "Title",
                                          "account_default":  false,
                                          "active_currencies":  "EUR",
                                          "default_currencies":  "EUR GBP",
                                          "destroyable":  true,
                                          "created_at":  "2020-04-20T08:09:15-07:00",
                                          "updated_at":  "2020-04-20T08:09:15-07:00",
                                          "id":  "551175"
                                      }
                       },
    "meta":  {
                 "count":  1,
                 "page_count":  1,
                 "page_number":  1,
                 "page_size":  200
             }
}

File 2:

{
    "count":  1,
    "results":  [
                    {
                        "key":  "cards",
                        "id":  "847355"
                    }
                ],
    "cards":  {
                       "847355":  {
                                      "currency":  "EUR",
                                      "created_at":  "2020-04-20T08:09:15-07:00",
                                      "updated_at":  "2020-04-20T08:09:15-07:00",
                                      "card_set_id":  "551175",
                                      "id":  "847355"
                                  }
                   },
    "meta":  {
                 "count":  1,
                 "page_count":  1,
                 "page_number":  1,
                 "page_size":  200
             }
}

For the sake of clarity I have reduced the output.

What I want to achieve is to join these 2 files together where they have corresponding IDs.

File 1 key would be card_sets.{id}.id

File 2 key would be cards.{id}.card_set_id

I don't really care about any of the results in the count/results/meta elements, but I hope to pull together a list that sort of looks like

File1.id, File1.title, File1.active_currencies, File2.id, File2.currency

I've only been working with PowerShell for a day and a half and just getting the JSON files out has been a big step for me but all the google searches I can find so far are based on identical json files being merged into one. This seems a little more complex and I'm stumped for the moment.

Can anyone assist?

Answer 1

You could try the following:

$json1 = Get-Content file1.json | ConvertFrom-Json
$json2 = Get-Content file2.json | ConvertFrom-Json
$cardsets = $json1.card_sets | Select-Object -Expand *
$cards = $json2.cards | Select-Object -Expand *

foreach ($cardset in $cardsets) {
    $card = $cards | Where card_set_id -eq $cardset.id
    if ($cardset.id -in $cards.card_set_id) {
        [pscustomobject]@{
            'File1.id' = $cardset.id
            'File1.title' = $cardset.title
            'File1.active_currencies' = $cardset.active_currencies
            'File2.id' = $card.id
            'File2.currency' = $card.currency
        }
    }
}