Adding square brackets in json after split and convert from CSV in PowerShell

Question

I have a scenario where I have to split a csv in certain number of rows, and for each batch there should be json file generated through PowerShell Script.

Here is what I am doing as of now:

$csv = "C:\Desktop\report.csv"

[int]$totalRows = 0
$reader = New-Object IO.StreamReader $csv
while($reader.ReadLine() -ne $null) { $totalRows++ }
$reader.Dispose()

$totalRows
$startRow = 0
$counter = 1

while($startRow -lt $totalRows)
{   
    Import-CSV $csv | Select-Object @{expression = { "Append this value"+ $_.Name}; label = 'NewName'}, @{expression = {$_.Account}; label = 'AccountNumber'} | Select-Object -skip $startRow -first 2 | ConvertTo-Json | Add-Content -Path "C:\Desktop\r_$($counter).json"
    
    $startRow += 2
    $counter++
}

The only problem here is that I am not able to enclose the Account number values in square bracket []:

Actual : "AccountNumber": "123" expected : "AccountNumber": ["123"]

Also I am not sure on how to put whole json in each file under a root element through this. Also not sure if this "ConvertTo-Json" is the way to go as csv data needs to be edited, Please help.

Here is a csv for reference-

Name,Account,Role
John,123,Second
Rocky,345,Third
Tony,234,First
Rocky,345,Second
Matt,999,Second
Bernard,888,Third
Matt,999,First
Jacob,789,Second
Angela,777,Second
Jacob,789,First

Expected Output

First File:

{   
    "details":
    [
        {
            "NewName":  "Append this valueJohn",
            "AccountNumber":  ["123","333"]
        },
        {
            "NewName":  "Append this valueRocky",
            "AccountNumber":  ["345"]
        }
    ]
}

Second File:

{   
    "details":
    [
        {
            "NewName":  "Append this valueTony",
            "AccountNumber":  ["234"]
        },
        {
            "NewName":  "Append this valueRocky",
            "AccountNumber":  ["345"]
        }
    ]
}

So on till 6th file:

{   
    "details":
    [
        {
            "NewName":  "Append this valueAngela",
            "AccountNumber":  ["777"]
        },
        {
            "NewName":  "Append this valueJacob",
            "AccountNumber":  ["789"]
        }
    ]
}

Thanks

Answer 1

Continuing from my comments as I can't use newlines in there:

Converting the below PowerShell object to Json (JavaScript Object Notation):

@{
    details = @{
        NewName = 'Append this valueJohn'
        AccountNumber = 123
    }, @{
        NewName = 'Append this valueRocky'
        AccountNumber = 345
    }
} |ConvertTo-Json

Results in

{
  "details": [
    {
      "NewName": "Append this valueJohn",
      "AccountNumber": 123
    },
    {
      "NewName": "Append this valueRocky",
      "AccountNumber": 345
    }
  ]
}

If you want to quote the values, you will need to force them to a string
(by using quotes or e.g. the [String] initializer):

@{
    details = @{
        NewName = 'Append this valueJohn'
        AccountNumber = "123"
    }, @{
        NewName = 'Append this valueRocky'
        AccountNumber = [String]345
    }
} |ConvertTo-Json

Results:

{
  "details": [
    {
      "NewName": "Append this valueJohn",
      "AccountNumber": "123"
    },
    {
      "NewName": "Append this valueRocky",
      "AccountNumber": "345"
    }
  ]
}

If you also want to put the single item (scalar) strings between square brackets (which represents an array), you will need to force the values to an array using the Array subexpression operator @( ) or the Comma operator ,. Note that this automatically happens when it concerns multiple values, e.g.: AccountNumber = "123", "333"

@{
    details = @{
        NewName = 'Append this valueJohn'
        AccountNumber = @("123")
    }, @{
        NewName = 'Append this valueRocky'
        AccountNumber = ,[String]345
    }
} |ConvertTo-Json -Depth 9

^{(Also note the -Depth parameter)}
Results:

{
  "details": [
    {
      "NewName": "Append this valueJohn",
      "AccountNumber": [
        "123"
      ]
    },
    {
      "NewName": "Append this valueRocky",
      "AccountNumber": [
        "345"
      ]
    }
  ]
}

Note that the square brackets aren't on the same line as per default the ConvertTo-Json expands the output. You might consider to use the -Compress parameters but that will put everything on the same line:

{"details":[{"NewName":"Append this valueJohn","AccountNumber":["123"]},{"NewName":"Append this valueRocky","AccountNumber":["345"]}]}

In other words, even the appearance is different, it all technically equal and represents the same object.