Sort json data into array of arrays by field-CodePudding

name        : SN608-ps-iLevel-C (74617)
total_bytes : 693123
sessions    : 30
planname    : Professional Plan Flexible Data
tags        : {All Island}
sms         : 0
usage       : {@{network_name=T-Mobile USA, Inc; bytes=693123; sessions=30}}name

SN523-ps-iLevel-C (02146)
total_bytes : 660570
sessions    : 31
planname    : Professional Plan Flexible Data
tags        : {All Island}
sms         : 0
usage       : {@{network_name=T-Mobile USA, Inc; bytes=660570; sessions=31}}


name        : SN517-ps-iLevel-C (01460)
total_bytes : 643331
sessions    : 29
planname    : Professional Plan Flexible Data
tags        : {Olympia Fuel}
sms         : 0
usage       : {@{network_name=T-Mobile USA, Inc; bytes=643331; sessions=29}}


name        : SN514-ps-iLevel-C (08713)
total_bytes : 364413
sessions    : 24
planname    : Professional Plan Flexible Data
tags        : {Olympia Fuel}
sms         : 0
usage       : {@{network_name=T-Mobile USA, Inc; bytes=364413; sessions=24}}

I want to generate a hashtable with key = $_.tags and value = ($_.name, $_.total)bytes) to look like

All Island (All Island SN608-ps-iLevel-C (74617) 693123,
            All Island SN523-ps-iLevel-C (02146) 660570)
Olympia Fuel (Olympia Fuel SN517-ps-iLevel-C (08713) 364413,
              Olympia Fuel SN514-ps-iLevel-C (08713) 364413)

This code does not work:

$response.data | ForEach-Object {
                $DeviceData = @($_.name, $_.usage.bytes)
                $BillData  = @($_.tags, $DeviceData)
                }
                
                $BillData | Sort-Object { $_.tags }

CodePudding user response：

I'm not totally sure exactly what output you're after, but this seems like it's close:

First, define some test data:

$json = @"
{
  "data" : [
    {
      "name"        : "SN608-ps-iLevel-C (74617)",
      "total_bytes" : 693123,
      "sessions"    : 30,
      "planname"    : "Professional Plan Flexible Data",
      "tags"        : [ "All Island" ],
      "sms"         : 0,
      "usage": {
        "network_name" : "T-Mobile USA, Inc",
        "bytes"        : 693123,
        "sessions"     : 30
      }
    },
    {
      "name"        : "N523-ps-iLevel-C (02146)",
      "total_bytes" : 660570,
      "sessions"    : 31,
      "planname"    : "Professional Plan Flexible Data",
      "tags"        : [ "All Island" ],
      "sms"         : 0,
      "usage"       : {
        "network_name" : "T-Mobile USA, Inc",
        "bytes"        : 660570,
        "sessions"     : 31
      }
    },
    {
      "name"        : "SN517-ps-iLevel-C (01460)",
      "total_bytes" : 643331,
      "sessions"    : 29,
      "planname"    : "Professional Plan Flexible Data",
      "tags"        : [ "Olympia Fuel" ],
      "sms"         : 0,
      "usage"       : {
        "network_name" : "T-Mobile USA, Inc",
        "bytes"        : 643331,
        "sessions"     : 29
      }
    },
    {
      "name"        : "SN514-ps-iLevel-C (08713)",
      "total_bytes" : 364413,
      "sessions"    : 24,
      "planname"    : "Professional Plan Flexible Data",
      "tags"        : [ "Olympia Fuel" ],
      "sms"         : 0,
      "usage"       : {
        "network_name" : "T-Mobile USA, Inc",
        "bytes"        : 364413,
        "sessions"     : 24
      }
    }
  ]
}
"@

$response = $json | ConvertFrom-Json;

Then, group the entries using the first tag as the key:

$data `
  | select-object *, @{"n"="key";"e"={$_.tags[0]}} `
  | group-object -Property "key" `
  | sort-object -Property "Name" `
  | foreach-object `
      -Begin   { $result = [ordered] @{} } `
      -Process {
        $result[$_.Name] = @(
          $_.Group | select-object "name", @{"n"="usage_bytes";"e"={$_.usage.bytes}}
        )
      }

$result | ft

# Name         Value
# ----         -----
# All Island   {@{name=SN608-ps-iLevel-C (74617); usage_bytes=693123}, @{name=N523-ps-iLevel-C (0214...
# Olympia Fuel {@{name=SN517-ps-iLevel-C (01460); usage_bytes=643331}, @{name=SN514-ps-iLevel-C (087...

You might need to add some error handling for if there are zero tags for an entry, or more than one tag as you haven't described what should happen in those cases in your question, but this will hopefully be enough to get you started...

CodePudding user response：

Your own attempt is creating an array, whereas you say you want a hashtable (dictionary).

Given that you want output sorted by keys, you can use a System.Collections.Generic.SortedList`2 instead, which, after it has been filled from the JSON data, automatically outputs its entries in key sort order:

$sortedList = [System.Collections.Generic.SortedList[string, array]]::new()

$response.data | ForEach-Object {
   $sortedList[$_.tags]  = $_.Name, $_.total_bytes
}

$sortedList # output

Note:

The sorted list uses case-sensitive lookups for keys by default; if you want case-insensitive behavior (as you get by default with PowerShell's hashtables), pass [System.StringComparer]::InvariantCultureIgnoreCase to the ::new() call.
It looks like the .tags property contains an array, though the sample data shows only one value; the above simply converts that to a single string value; if multiple values are present, they are joined with spaces to form the key.
- As mclayton points out, this can create ambiguity; e.g., tag values
  'one', 'trick pony' and 'one trick', 'pony' would both result in the same key, 'one trick pony'.
I'm using $_.total_bytes, not $_.usage.bytes (the latter is what you used in your own coding attempt), because your description and the sample output suggest the former - adjust as needed.