Merging 2 json files with unique ID Powershell

Question

I need to combine 2 json files with powershell that both have unique ID.

Json1

   [
    {
      "id": "1",
      "tags": {
        "tag1": "value1",
        "tag2": "value2"
      }
    },
    {
      "id": "2",
      "tags": {
        "tag1": "value1",
        "tag2": "value2",
        "tag3": "value3",
        "tag4": "value4"
       
      }
    }
   ]

Json2

 [     
  {
    "ID": "1",
    "Name": "name1",
    "State": "Enabled"
  },
  {
    "ID": "2",
    "Name": "name2",
    "State": "Disabled"
  }
 ]

And the result should look like that:

 [     
  {
    "ID": "1",
    "Name": "name1",
    "State": "Enabled",
    "tags": {
        "tag1": "value1",
        "tag2": "value2"
      }
  },
  {
    "ID": "2",
    "Name": "name2",
    "State": "Disabled",
    "tags": {
        "tag1": "value1",
        "tag2": "value2",
        "tag3": "value3",
        "tag4": "value4"
       
      }
  }
 ]

Also it would be nice if new file could be converted to csv i tried ConvertTo-CSV but output was unusable.

Answer 1

Assuming you're only interested in matching pairs and both documents contain only objects with unique ID values, the easiest way is to pick any of them and built an index table - either manually using a hashtable/dictionary, or by using Group-Object -AsHashtable:

# create empty hashtable to store the objects from the tags documents
$tagStoreIndex = @{}

# read and parse json document, use Write-Output to enumerate the top-level array
Get-Content path\to\tags.json |ConvertFrom-Json |Write-Output |ForEach-Object {
  # store object by ID
  $tagStoreIndex[$_.ID] = $_
}

# alternatively use `Group-Object -AsHashTable` to construct the table
$tagStoreIndex = Get-Content path\to\tags.json |ConvertFrom-Json |Write-Output |Group-Object ID -AsHashTable 

Now you can iterate over the objects in the second document and use the index table to quickly fetch the corresponding tags:

# read and parse second document
Get-Content path\to\states.json |ConvertFrom-Json |Write-Output |ForEach-Object {
  if ($tagStoreIndex.ContainsKey($_.ID)) {
    # construct new output object by attaching the tags from the other document
    $_ |Select-Object *,@{Name='tags';Expression={$tagStoreIndex[$_.ID].tags |ConvertTo-Json -Compress}}
  }
} |Export-Csv path\to\output.csv -NoTypeInformation

Using ConvertTo-Json on the tags object gives you a string value that you can later convert back to the original tags object.

If you only need the tag values, change the property expression to

@{Name='tags';Expression={$tagStoreIndex[$_.ID].tags.psobject.Properties.Value -join ', '}}

If you want the tag names and values as bareword tokens, do:

If you only need the tag values, change the property expression to

@{Name='tags';Expression={$tagStoreIndex[$_.ID].tags.psobject.Properties.ForEach({$_.Name,$_.Value -join ':'}) -join ', '}}

Answer 2

As a common solution in addition to the helpful answer from Mathias R. Jessen:
For the last months I have been busy creating a common Object Graph Tools set as we dealing with similar queries in our company. This tools set is build on a general PowerShell Object Parser.
(Note that it is all still under development)

For this specific question you might use the included Merge-ObjectGraph cmdlet that (recursively) merges dictionaries (or [PSCustomObject]s) while respecting the given PrimaryKey(s):

Install-Module -Name ObjectGraphTools

$Data1 = ConvertFrom-Json $Json1
$Data2 = ConvertFrom-Json $Json2
,$Data1 | Merge-ObjectGraph $Data2 -PrimaryKey ID | ConvertTo-Json
[
  {
    "id": "1",
    "tags": {
      "tag1": "value1",
      "tag2": "value2"
    },
    "Name": "name1",
    "State": "Enabled"
  },
  {
    "id": "2",
    "tags": {
      "tag1": "value1",
      "tag2": "value2",
      "tag3": "value3",
      "tag4": "value4"
    },
    "Name": "name2",
    "State": "Disabled"
  }
]

For flattening the object graph, you might play with $Object | Get-ChildNode -Recurse -Leaf