Powershell ConvertFrom-Json: Selecting Value of Nested Array Based on Property Name

Question

I have a json file that I'm trying to export to csv using powershell, but I only need certain nested values. It looks like the following:

[
   {
      "ItemName": "A",
      "ItemID": "I001",
      "ItemDate": "2021-03-01",
      "ItemValue": "1000",
      "ItemTags": [
         {
            "Name": "First tag name",
            "Value": "medium"
         },
         {
            "Name": "Another tag name",
            "Value": "red"
         },
         {
            "Name": "Tag 3",
            "Value": null
         },
         {
            "Name": "Tag 4",
            "Value": "Yes"
         }
      ]
   },
   {
      "ItemName": "B",
      "ItemID": "I002",
      "ItemDate": "2021-02-01",
      "ItemValue": "3000",
      "ItemTags": [
         {
            "Name": "First tag name",
            "Value": "best"
         },
         {
            "Name": "Another tag name",
            "Value": "green"
         },
         {
            "Name": "Tag 3",
            "Value": null
         },
         {
            "Name": "Tag 4",
            "Value": "No"
         }
      ]
   }
]

The nested "ItemTags" portion is where I'm having trouble. The actual json file has dozens of Item Tags and ordering of them can vary so what I'd like to do is only select ones based on the "Name" of the ItemTag, for example select the ItemTag Value where ItemTag Name is Tag 4.

I've only been able to do it based on the order like this:


$obj1 = Get-Content -Path "C:\Temp\sample.json"  | ConvertFrom-Json
$obj1 | select ItemName, ItemID, ItemDate, ItemValue, @{Name = 'Tag 4'; Expression ={$_.ItemTags[3].Value}} | Export-CSV "C:\Temp\items.csv" -NoTypeInformation

But again, because the ItemTags ordering can be different from one day to the next, this won't quite do it. How would I modify this to select based on the ItemTag Name of "Tag 4"?

zett42 · Accepted Answer · 2021-04-03T20:45:20.327

1

This works for me:

Expression = { ($_.ItemTags | Where-Object Name -eq 'Tag 4').Value }

By specifying just $_.ItemTags we are creating an array of all ItemTags objects. Using Where-Object we select only these elements, whose Name member equals 'Tag 4'. Finally we get the Value member from the resulting elements. Note this wil be an array, when there are more than one matching elements, which is true for your sample data ('Yes','No').

edited Apr 03 '21 at 20:45

answered Apr 03 '21 at 20:33

zett42

25,437
3
35
72

1

Nicely done; because the `.Where()` array method doesn't get enough love: a more efficient alternative: `Expression = { ($_.ItemTags.Where({ $_.Name -eq 'Tag 4' }).Value }` – mklement0 Apr 03 '21 at 20:46
@zett42 thanks! this worked perfectly. really appreciate the help. – wd510 Apr 03 '21 at 21:44

Powershell ConvertFrom-Json: Selecting Value of Nested Array Based on Property Name

1 Answers1