PowerShell 5.1.1, I need to remove an empty array from JSON

This my first post so thank you to everyone that contributes. I have trawled this site and it feels like all of Google but still can’t find a solution to my problem.

I have a task at work to supply a third party with some data in JSON format, this is taken from MS SQL Server and saved as a csv. I then use PowerShell to format it the way they want it in JSON.

After days of work its all working as it should apart from one bit, I need to remove empty arrays that will happen in my data if a customer doesn’t have a device. Here is my PowerShell script;

$jsonBase = @{}
$requestor = 'exportedData'
$filename = (get-date).ToUniversalTime().ToString("yyyyMMddHHmmss")
$csvData   = Import-Csv -Path example.csv

Function Remove-Null {
    [CmdletBinding()]
    Param(
        # Object from which to remove the null values.
        [Parameter(ValueFromPipeline,Mandatory)]
        $InputObject,
        # Instead of also removing values that are empty strings, include them
        # in the output.
        [Switch]$LeaveEmptyStrings,
        # Additional entries to remove, which are either present in the
        # properties list as an object or as a string representation of the
        # object.
        # I.e. $item.ToString().
        [Object[]]$AlsoRemove = @()
    )
    Process {
        # Iterate InputObject in case input was passed as an array
        ForEach ($obj in $InputObject) {
            $obj | Select-Object -Property (
                $obj.PSObject.Properties.Name | Where-Object {
                    -not (
                        # If prop is null, remove it
                        $null -eq $obj.$_ -or
                        # If -LeaveEmptyStrings is not specified and the property
                        # is an empty string, remove it
                        (-not $LeaveEmptyStrings.IsPresent -and
                            [string]::IsNullOrEmpty($obj.$_)) -or
                        # If AlsoRemove contains the property, remove it
                        $AlsoRemove.Contains($obj.$_) -or
                        # If AlsoRemove contains the string representation of
                        # the property, remove it
                        $AlsoRemove.Contains($obj.$_.ToString()) -OR
                        # remove if it contains a string of 'NULL'
                        'NULL' -eq $obj.$_

                    )
                }
            )
        }
    }
}


# get an array of PSObjects
# we use 'Group-Object customerID' here to allow extra orders and PIDs
$allcustomers = $csvData | Group-Object customerID | ForEach-Object {
    $orders = $_.Group | Select-Object -Unique orderType,orderStart,orderEnd,supplier
    $devices = $_.Group | Select-Object -Unique deviceID,@{Name='deviceName';Expression={$_.customerID}},allocationStart,allocationEnd | Where-Object {$_.deviceID -ne 'NULL'}
    $customer = $_.Group[0] | Select-Object -Unique * -ExcludeProperty orderType,orderStart,orderEnd,supplier,deviceID,allocationStart,allocationEnd

    $newdevices = $devices | Remove-Null

    $customer | Add-Member -MemberType NoteProperty -Name 'orders' -Value @($orders)
    $customer | Add-Member -MemberType NoteProperty -Name 'devices' -Value @($newdevices)

    # output the customer object
$customer
}

# gathered above into a new object and convert that to JSON

$json1 = [PsCustomObject]@{
    applicationID = "1"
    customers = @($allcustomers)
}

$jsonBase.Add("exportedData",$json1)

$jsonBase | ConvertTo-Json -Depth 5 | Set-Content -Encoding UTF8 -Path "${filename}.json"

Here is the JSON output;

{
    "exportedData":  {
                         "applicationID":  "1",
                         "customers":  [
                                           {
                                               "customerID":  "1339306",
                                               "customerName":  "Fake Customer 1",
                                               "customerDateOfBirth":  "19870525",
                                               "customerAddressText":  "Fake Address 1",
                                               "Manager":  "Manager 1",
                                               "xID":  "UCYWIU",
                                               "xRef":  "AHZVXP",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Online",
                                                                  "orderStart":  "20210411230000",
                                                                  "orderEnd":  "20220410230000",
                                                                  "supplier":  "Supplier A"
                                                              }
                                                          ],
                                               "devices":  [
                                                               {
                                                                   "deviceID":  "637148",
                                                                   "deviceName":  "1339306",
                                                                   "allocationStart":  "20210412192100"
                                                               }
                                                           ]
                                           },
                                           {
                                               "customerID":  "1339321",
                                               "customerName":  "Fake Customer 2",
                                               "customerDateOfBirth":  "19960102",
                                               "customerAddressText":  "Fake Address 2",
                                               "Manager":  "Manager 2",
                                               "xID":  "ULYKHN",
                                               "xRef":  "STKRFZ",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Online",
                                                                  "orderStart":  "20210413230000",
                                                                  "orderEnd":  "20210720230000",
                                                                  "supplier":  "Supplier B"
                                                              }
                                                          ],
                                               "devices":  [
                                                               {
                                                                   "deviceID":  "641358",
                                                                   "deviceName":  "1339321",
                                                                   "allocationStart":  "20210417183200",
                                                                   "allocationEnd":  "20210508181500"
                                                               },
                                                               {
                                                                   "deviceID":  "641358",
                                                                   "deviceName":  "1339321",
                                                                   "allocationStart":  "20210508181500",
                                                                   "allocationEnd":  "20210612190500"
                                                               },
                                                               {
                                                                   "deviceID":  "641358",
                                                                   "deviceName":  "1339321",
                                                                   "allocationStart":  "20210612190500",
                                                                   "allocationEnd":  "20210721193400"
                                                               }
                                                           ]
                                           },
                                           {
                                               "customerID":  "1339325",
                                               "customerName":  "Fake Customer 3",
                                               "customerDateOfBirth":  "19750405",
                                               "customerAddressText":  "Fake Address 3",
                                               "Manager":  "Manager 3",
                                               "xID":  "BCQPQJ",
                                               "xRef":  "MTTJBJ",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Email",
                                                                  "orderStart":  "20210418230000",
                                                                  "orderEnd":  "20220418230000",
                                                                  "supplier":  "Supplier C"
                                                              }
                                                          ],
                                               "devices":  [
                                                               {
                                                                   "deviceID":  "641360",
                                                                   "deviceName":  "1339325",
                                                                   "allocationStart":  "20210419205600",
                                                                   "allocationEnd":  "20210602211100"
                                                               },
                                                               {
                                                                   "deviceID":  "646142",
                                                                   "deviceName":  "1339325",
                                                                   "allocationStart":  "20210602211100",
                                                                   "allocationEnd":  "20210628194500"
                                                               },
                                                               {
                                                                   "deviceID":  "641831",
                                                                   "deviceName":  "1339325",
                                                                   "allocationStart":  "20210628194600"
                                                               }
                                                           ]
                                           },
                                           {
                                               "customerID":  "1355689",
                                               "customerName":  "Fake Customer 4",
                                               "customerDateOfBirth":  "19891005",
                                               "customerAddressText":  "Fake Address 4",
                                               "Manager":  "Manager 4",
                                               "xID":  "OYNLQL",
                                               "xRef":  "DMSUAP",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Email",
                                                                  "orderStart":  "20210728230000",
                                                                  "orderEnd":  "20220728230000",
                                                                  "supplier":  "Supplier B"
                                                              }
                                                          ],
                                               "devices":  [

                                                           ]
                                           },
                                           {
                                               "customerID":  "1355826",
                                               "customerName":  "Fake Customer 5",
                                               "customerDateOfBirth":  "19891218",
                                               "customerAddressText":  "Fake Address 5",
                                               "Manager":  "Manager 5",
                                               "xID":  "XSWTNH",
                                               "xRef":  "FCIIYO",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Online",
                                                                  "orderStart":  "20210728230000",
                                                                  "orderEnd":  "20220728230000",
                                                                  "supplier":  "Supplier B"
                                                              }
                                                          ],
                                               "devices":  [

                                                           ]
                                           }
                                       ]
                     }
}

What I want to do is remove any empty arrays, so the ones that don’t have devices I want to hide/remove the array completely so that the “devices” : doesn’t appear.

I have used a function to remove elements from an array if the value is a string ‘NULL’.

I have looked at ForEach and ForEach-Object but cant seem to iterate over the arrays themselves to remove them. As a last attempt I was going to read the JSON back in and use -replace to change the string but couldnt get it to work with the carriage returns and spaces.

Someone suggested that I use a recursive function but first making use of the -AsHashTable parameter from the ConvertFrom-Json function. I can’t use this as the version I’m using at work is limited to 5 and this cant be updated.

This is my first project in PowerShell so has been a very steep learning curve.

This is what the CSV looks like.

Thank you for any help.

UPDATE:

This has now been solved.

function Remove-EmptyArrays ($Object) {
    if ($Object -is [Array]) {
        foreach ($Item in $Object) { Remove-EmptyArrays $Item }
    }
    elseif ($Object -is [HashTable]) {
        foreach ($Key in @($Object.get_Keys())) {
            if ($Object[$Key] -is [Array] -and $Object[$Key].get_Count() -eq 0) {
                $Object.Remove($Key)
            }
            else { Remove-EmptyArrays $Object[$Key] }
        }
    }
    elseif ($Object -is [PSCustomObject]) {
        foreach ($Name in @($Object.psobject.properties.Name)) {
            if ($Object.$Name -is [Array] -and $Object.$Name.get_Count() -eq 0) {
                $Object.PSObject.Properties.Remove($Name)
            }
            else { Remove-EmptyArrays $Object.$Name }
        }
    }
}

Remove-EmptyArrays $jsonBase

$jsonBase | ConvertTo-Json -Depth 5 | Set-Content -Encoding UTF8 -Path "${filename}.json"

Paul

1 Like

As you’ve said, those are empty arrays from the groups, so just set any empty array to NULL and then convert to JSON.

$obj = @"
{
    "exportedData":  {
                         "applicationID":  "1",
                         "customers":  [
                                           {
                                               "customerID":  "1339306",
                                               "customerName":  "Fake Customer 1",
                                               "customerDateOfBirth":  "19870525",
                                               "customerAddressText":  "Fake Address 1",
                                               "Manager":  "Manager 1",
                                               "xID":  "UCYWIU",
                                               "xRef":  "AHZVXP",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Online",
                                                                  "orderStart":  "20210411230000",
                                                                  "orderEnd":  "20220410230000",
                                                                  "supplier":  "Supplier A"
                                                              }
                                                          ],
                                               "devices":  [
                                                               {
                                                                   "deviceID":  "637148",
                                                                   "deviceName":  "1339306",
                                                                   "allocationStart":  "20210412192100"
                                                               }
                                                           ]
                                           },
                                           {
                                               "customerID":  "1339321",
                                               "customerName":  "Fake Customer 2",
                                               "customerDateOfBirth":  "19960102",
                                               "customerAddressText":  "Fake Address 2",
                                               "Manager":  "Manager 2",
                                               "xID":  "ULYKHN",
                                               "xRef":  "STKRFZ",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Online",
                                                                  "orderStart":  "20210413230000",
                                                                  "orderEnd":  "20210720230000",
                                                                  "supplier":  "Supplier B"
                                                              }
                                                          ],
                                               "devices":  [
                                                               {
                                                                   "deviceID":  "641358",
                                                                   "deviceName":  "1339321",
                                                                   "allocationStart":  "20210417183200",
                                                                   "allocationEnd":  "20210508181500"
                                                               },
                                                               {
                                                                   "deviceID":  "641358",
                                                                   "deviceName":  "1339321",
                                                                   "allocationStart":  "20210508181500",
                                                                   "allocationEnd":  "20210612190500"
                                                               },
                                                               {
                                                                   "deviceID":  "641358",
                                                                   "deviceName":  "1339321",
                                                                   "allocationStart":  "20210612190500",
                                                                   "allocationEnd":  "20210721193400"
                                                               }
                                                           ]
                                           },
                                           {
                                               "customerID":  "1339325",
                                               "customerName":  "Fake Customer 3",
                                               "customerDateOfBirth":  "19750405",
                                               "customerAddressText":  "Fake Address 3",
                                               "Manager":  "Manager 3",
                                               "xID":  "BCQPQJ",
                                               "xRef":  "MTTJBJ",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Email",
                                                                  "orderStart":  "20210418230000",
                                                                  "orderEnd":  "20220418230000",
                                                                  "supplier":  "Supplier C"
                                                              }
                                                          ],
                                               "devices":  [
                                                               {
                                                                   "deviceID":  "641360",
                                                                   "deviceName":  "1339325",
                                                                   "allocationStart":  "20210419205600",
                                                                   "allocationEnd":  "20210602211100"
                                                               },
                                                               {
                                                                   "deviceID":  "646142",
                                                                   "deviceName":  "1339325",
                                                                   "allocationStart":  "20210602211100",
                                                                   "allocationEnd":  "20210628194500"
                                                               },
                                                               {
                                                                   "deviceID":  "641831",
                                                                   "deviceName":  "1339325",
                                                                   "allocationStart":  "20210628194600"
                                                               }
                                                           ]
                                           },
                                           {
                                               "customerID":  "1355689",
                                               "customerName":  "Fake Customer 4",
                                               "customerDateOfBirth":  "19891005",
                                               "customerAddressText":  "Fake Address 4",
                                               "Manager":  "Manager 4",
                                               "xID":  "OYNLQL",
                                               "xRef":  "DMSUAP",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Email",
                                                                  "orderStart":  "20210728230000",
                                                                  "orderEnd":  "20220728230000",
                                                                  "supplier":  "Supplier B"
                                                              }
                                                          ],
                                               "devices":  [

                                                           ]
                                           },
                                           {
                                               "customerID":  "1355826",
                                               "customerName":  "Fake Customer 5",
                                               "customerDateOfBirth":  "19891218",
                                               "customerAddressText":  "Fake Address 5",
                                               "Manager":  "Manager 5",
                                               "xID":  "XSWTNH",
                                               "xRef":  "FCIIYO",
                                               "orders":  [
                                                              {
                                                                  "orderType":  "Online",
                                                                  "orderStart":  "20210728230000",
                                                                  "orderEnd":  "20220728230000",
                                                                  "supplier":  "Supplier B"
                                                              }
                                                          ],
                                               "devices":  [

                                                           ]
                                           }
                                       ]
                     }
}
"@ | ConvertFrom-Json

$obj.exporteddata.customers | Where{!$_.Devices} | foreach{$_.Devices = $null}

$obj | ConvertTo-Json -Depth 5
1 Like

@parthur I understand that you have a solution, but this post intrigued me. I will remember the allcustomer variable for sure—it reminded me a little of XSLT.

I tried my hand at a solution because I think my work might use something similar in future.

Code

(for demonstration purposes only)

  • Uses a custom SQL View for the data source (Issue.CustomerGoods).
    • Data are sourced from the AdventureWorks2014 database.
    • Import-Csv is costly on my home computer.
  • The variable dataSet exists to transform SQL datetime2(0) data types for clearer output.
    • It is an option given the SQL source data.
  • If no devices are found (e.g., CustomerID = 11004, all DeviceID elements are null) the devices array is not created.
  • I did not include applicationID element and the customers array.
  • The SQL source data set is something that I whipped together. Some IDs and datetime values are not unique.
using namespace System.Collections;
using namespace System.Data;
using namespace Microsoft.SqlServer.Management.Smo;

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

[Server]$sourceServer = "MyServer";
[Database]$sourceDatabase = $sourceServer.Databases.Item("MyDatabase");

$results = $sourceDatabase.ExecuteWithResults('SELECT * FROM Issue.CustomerGoods;')

[DataSet] $dataSet = $results.Clone();
($dataSet.Tables[0].Columns[2,8,9,12,13])|ForEach-Object{
    $_.DataType = [string]
}

foreach($sd in $results.Tables[0].Rows)
{
    $dataSet.Tables[0].ImportRow($sd)
}

$exportData = [ArrayList]::new();

$allcustomers = $dataSet.Tables[0]|Group-Object CustomerID|ForEach-Object{
    $orders = $_.Group|Select-Object -Unique OrderType,OrderStart,OrderEnd,Supplier;
    $devices = ($_.Group)|Select-Object -Unique DeviceID,@{l='DeviceName';e={$_.CustomerID}},AllocationStart,AllocationEnd|Where-Object{$_.deviceID -ne 'NULL'};
    $customer = $_.Group[0]|Select-Object -Unique * -ExcludeProperty orderType,orderStart,orderEnd,supplier,deviceID,allocationStart,allocationEnd;

    $customer|Add-Member -MemberType NoteProperty -Name 'orders' -Value @($orders);
    if($devices.DeviceID -ne [DBNULL]::Value)
    {
        $customer|Add-Member -MemberType NoteProperty -Name 'devices' -Value @($devices);
    }
    $exportData.Add($customer);
}

$exportData|Select-Object * -ExcludeProperty HasErrors,ItemArray,RowError,RowState,Table|ConvertTo-Json -Depth 3|Out-File -FilePath A:\datasets\customer.json;

Results

  • CustomerID 11004 has all source rows where DeviceID is NULL.
  • The JSON output does not contain the devices array for this customer.

  • All other customers have one or more devices.
  • The JSON output contains the devices array when DeviceID is not null.
    • CustomerID = 11006 has 2 devices in the data source.