Need to remove Value and Count from Powershell JSON nested object

Question

In powershell I'm trying to convert CSV file to JSON with nested array of Size object by grouping products. In code below $ProductLines come from CSV file as powershell object.

 $UniqueProducts = $ProductLines | Group-Object -Property 'ProductNumber' | ForEach-Object {    

    $SizeObj = ($_.Group | Select-Object -Property `
      @{name="SizeCode"; Expression = {$_.Sizes}}, @{name="UPC"; Expression = {$_.UPCNo}} `
    )
    #$SizeObj | ConvertTo-Json -depth 10 | Out-File "C:\POWERSHELL\Sizes.txt"

    $_.Group | Select-Object -Property ProductNumber, ProductName, `
         @{name="Sizes"; Expression = {$SizeObj}} -Unique
  }

$UniqueProducts | ConvertTo-Json -depth 10 | Out-File "C:\POWERSHELL\UniqueProducts.txt"

The above code produces JSON file with below structure with additional layer value: and Count:

[
    {
        "ProductNumber":  "EBAGS101-00008",
        "ProductName":  "EBAGS101",
        "Sizes":  {
                      "value":  [
                                    {
                                        "SizeCode":  "XS",
                                        "UPC":  "201112291509"
                                    },
                                    {
                                        "SizeCode":  "S",
                                        "UPC":  "201112291510"
                                    }
                                ],
                      "Count":  2
                  }
    },
    {
        "ProductNumber":  "EBAGS101-001",
        "ProductName":  "EBAGS101",
        "Sizes":  {
                      "value":  [
                                    {
                                        "SizeCode":  "XS",
                                        "UPC":  ""
                                    },
                                    {
                                        "SizeCode":  "S",
                                        "UPC":  "098617106215"
                                    }
                                ],
                      "Count":  2
                  }
    }
]

How can I remove value and Count layer from my result. When I output just $SizeObj it doesn't have value and Count, I'm not sure how value and Count ends up in $UniquProducts. In my end result I'm trying to create SizeObj in the below format (without value and Count):

  "Sizes":   [
        {
            "SizeCode":  "XS",
            "UPC":  "201112291509"
        },
        {
            "SizeCode":  "S",
            "UPC":  "201112291510"
        }
    ]

UPDATE: using answer provided by @RoadRunner here is the modified solution with csv source; where $ProductLines is converted Powershell object from csv file:

$Products = New-Object -TypeName System.Collections.ArrayList
$ProductLines | Group-Object -Property 'ProductNumber' | ForEach-Object {
  $SizeRanges = New-Object -TypeName System.Collections.ArrayList
  foreach ($object in $_.Group) {
    $SizeVal = [PSCustomObject]@($object | Select-Object -Property `
        @{name="SizeCode"; Expression = {$_.Sizes}}, `
        @{name="UPC"; Expression = {$_.UPCNo}}`
      )
      $SizeRanges.AddRange($SizeVal)|Out-Null
  }
  $grp = $_.Group[0];
  $product =  [PSCustomObject]@{
      ProductNumber = $grp.ProductNumber
      ProductName = $grp.ProductName
      Sizes = $SizeRanges
  }
  $products.Add($product)|Out-Null
} | ConvertTo-Json -Depth 3 | Out-File -FilePath output.json

RoadRunner · Accepted Answer · 2020-04-03 05:34:21Z

2

I would just rebuild the JSON with the properties you want to keep.

First you can get the JSON data with Get-Content and ConvertFrom-Json. Then you can iterate each JSON object, creating a new PSCustomObject which keeps the ProductNumber, ProductName and Sizes properties, where the Sizes is just the array from Sizes.value. We can then convert to a JSON structure with ConvertTo-Json, keeping the first three levels with -Depth 3, and export the result to an output file with Out-File.

$json = Get-Content -Path .\data.json | ConvertFrom-Json

& {
    foreach ($object in $json)
    {
        [PSCustomObject]@{
            ProductNumber = $object.ProductNumber
            ProductName = $object.ProductName
            Sizes = $object.Sizes.value
        }
    }
} | ConvertTo-Json -Depth 3 | Out-File -FilePath output.json

You could probably also integrate the above logic in what you already have, so you don't need to rebuild the JSON in the first place. Unfortunately I can't really show how to do that because all I can see is the JSON you are producing, so I can only recommend a solution to rebuild the JSON to your requirements.

output.json

[
  {
    "ProductNumber": "EBAGS101-00008",
    "ProductName": "EBAGS101",
    "Sizes": [
      {
        "SizeCode": "XS",
        "UPC": "201112291509"
      },
      {
        "SizeCode": "S",
        "UPC": "201112291510"
      }
    ]
  },
  {
    "ProductNumber": "EBAGS101-001",
    "ProductName": "EBAGS101",
    "Sizes": [
      {
        "SizeCode": "XS",
        "UPC": ""
      },
      {
        "SizeCode": "S",
        "UPC": "098617106215"
      }
    ]
  } 
]

edited Apr 3, 2020 at 5:34

answered Apr 3, 2020 at 5:04

RoadRunner

26.4k6 gold badges46 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Mneyan Over a year ago

Thank you so much. The logic you've provided helped me solve the problem. Had to tweak a little the code since my source is flat csv file using the above logic. I'll update my question with the tweaked code.

Geograph · Accepted Answer · 2022-12-06 13:41:55Z

2

Workaround: Remove-TypeData -TypeName System.Array

answered Dec 6, 2022 at 13:41

Geograph

2,62427 silver badges23 bronze badges

Collectives™ on Stack Overflow

Need to remove Value and Count from Powershell JSON nested object

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related