0

I have a query defined as follows: 

$Query = "Select field1, field2, field3 from table

field3 is a comma-separated field: valueA,valueB,valueC

I'm using powershell's ConvertTo-Json method to return the results in a Json object. So my results looks like this:

{
    "field1" : "value1",
    "field2" : "value2",
    "field3" : "valueA,valueB,valueC"
}

What I'm trying to accomplish is for field3 values to get stored in an array object so my desire results would like this:

{
   "field1" : "value1",
   "field2" : "value2",
   "field3" : [ 
            "valueA",
            "valueB",
            "valueC" 
          ]

}

Is this possible to accomplish using powershell?

GabrieleMartini
  • 1,665
  • 2
  • 19
  • 26
pmoore65
  • 1
  • 2
  • You'll need to pre-process `field3` first. `"valueA,valueB,valueC" -split "," | ConvertTo-Json` does the trick, but it can't smell that `field3` is to be split. – Jeroen Mostert Jan 17 '19 at 16:36

2 Answers2

0
'{
"field1" : "value1",
"field2" : "value2",
"field3" : "valueA,valueB,valueC" 
}' | ConvertFrom-JSON | select field1, field2, @{n = 'field3' ;e = {$_.field3 -split ','}} | ConvertTo-Json

Do you need something like this? Result would be like that: enter image description here

Victor Dronov
  • 139
  • 1
  • 12
-1
Thank you for your responses. I FINALLY figured it out.  
I hope this can help someone else. Here is my code:

$connectionString = "Server="ServerName"; User ID="UserName";
 password="password"    database="Database"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connection
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = "select field1, field2, field3 from table  "
$result = $command.ExecuteReader()

##Create an empty array on the target field and finalResult:
$arrayField3 = @()
$finalResult = @()

while($result.Read())
{
    $Field1 = $result["field1"]
    $Field2 = $result["field2"]
    $Field3 = $result["field3"]
    $Field3Split = $Field3.Split(",")
    foreach($item in $Field3Split)
    {
        $arrayField3 += @($item)
    }
 $finalResult += New-Object psObject -Property @{'Field1'=$Field1; 
'Field2'=$Field2; 'Field3'= $arrayField3}
}
## DON'T FORGET TO ADD A DEPTH
$Json = $finalResult | ConvertTo-Json -Depth 5 

### The results of $Json looks like this:
{
    "Field1" : "Value1",
    "Field2" : "Value2",
    "Field3" : [
                 "ValueA",
                 "ValueB",
                 "ValueC"  
               ]
}
@Victor Dronov: Your solution is better (less code).  
How do you remove the "Count": 3 as one of the field values?
pmoore65
  • 1
  • 2
  • Welcome to Stack Overflow! It's good practice on Stack Overflow to add an explanation as to why your solution should work. For more information read [How To Answer](//stackoverflow.com/help/how-to-answer). – Samuel Liew Jan 22 '19 at 23:05
  • Could be used one of the workarounds from this page https://stackoverflow.com/questions/20848507/why-does-powershell-give-different-result-in-one-liner-than-two-liner-when-conve/38212718#38212718 Ex: Remove-TypeData System.Array before my sample will sovle problem with counts\values – Victor Dronov Feb 21 '19 at 18:53