This my first post but have used this forum for years for some great advice 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"
{
"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.
This is what the CSV looks like.
The CSV that is imported and transformed in PowerShell
If anyone could help that would be so great.
