Question 110874 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Sort json data into array of arrays by field

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 }
Tags:

2

Answers


  1. 0

    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…

    Login or Signup to reply.
  2. 0

    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[[string] $_.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'.

      • Also note the need for an explicit [string] cast, even though PowerShell normally implicitly stringifies arrays by joining their (stringified) elements with spaces; in fact, it does so on creating an entry, but not on looking it up: in the latter case, the array’s elements are looked up individually. Again, tip of the hat to mclayton, who posted code to illustrate the problem here.

    • 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search