powershell

Powershell from Table to Json grouped by new column

I have a executed an sql query that i want to transform it into a json

$QueryResult_uce = Execute-SqlQuery -Server $SQLServer -Database $SQLDBName -UseWindowsAuthentication $True -Username $Username -Password $Password -Query $query_uce;

$QueryResult_uce | Format-Table;

and i want to build a json using the data extracted from DB using a group by Here is my table data from console : enter image description here

i want to build a json like this :

  {
    "date": "20240329",
  "indicatorGroupNames": [    
    "Climate Alignment Scenario"    
  ],
  "positions": [
    {
      "portfolioCode": "6009",
      "positionId": 1,
      "instrumentId": 3270089,
      "weight": 0.00818749252267209
      
    } ,{
      "portfolioCode": "6009",
      "positionId": 1,
      "instrumentId": 4027,
      "weight": 0.01818749252267209
      
    }   
  ],
  "globalValuation": 9704981607.00212,
}

where

  "portfolioCode": corrspond to positions_portfolioCode,
  "positionId": corrspond to positions_positionID,
  "instrumentId": corrspond to positions_instrumentId,
  "weight": corrspond to positions_weight

should be grouped into a group called positions.

i tried

($QueryResult_uce | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors ) | ConvertTo-Json

that build a json but not grouped and when i add

($QueryResult_uce | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors ) | Group-Object positions_portfolioCode | ForEach{@{$_.Name = $_.Group.Type}} | ConvertTo-Json

it is really giving me a bad result, thnx in advance.

Solution

  • THE NEW HOPE ANSWER:

    $GroupedResults = $QueryResult_uce | 
    Group-Object -Property date, indicatorGroupNames, globalValuation | 
    ForEach-Object {
        
        [PSCustomObject]@{
            Date                = $_.Group.Date | Select-Object -Unique
            indicatorGroupNames = , ($_.Group.indicatorGroupNames | Select-Object -Unique )
            positions           = [System.Collections.Generic.List[object]]::new()
            globalValuation     = $_.Group.globalValuation | Select-Object -Unique
        }
    } 
        
$GroupedResults | ForEach-Object {
    $Group = $_
    $QueryResult_uce | 
        Where-Object { 
            $_.date -eq $Group.date -and 
            $_.indicatorGroupNames -in $Group.indicatorGroupNames -and
            $_.globalValuation -eq $Group.globalValuation } | 
        ForEach-Object { 
            $Group.positions.add(
                [pscustomobject]@{
                    portfolioCode = $_.positions_portfolioCode
                    positionId    = $_.positions_positionID
                    instrumentId  = $_.positions_instrumentId
                    weight        = $_.positions_weight
                }
            ) 
        }
    }
            
            
    $GroupedResults | ConvertTo-Json -Depth 100
    #>

    OLD AND BUSTED:

    $JsonObj = $QueryResult_uce | ForEach-Object {
    [PSCustomObject]@{
        Date                = $_.Date
        indicatorGroupNames = , 'Climate Alignment Scenario'  
        positions           = , [pscustomobject]@{
            portfolioCode = $_.positions_portfolioCode
            positionId    = $_.positions_positionID
            instrumentId  = $_.positions_instrumentId
            weight        = $_.positions_weight
        }
        globalValuation     = $_.globalValuation
    }
}

$JsonObj | ConvertTo-Json -Depth 100