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