1

when I query describe-instances in the below way:

aws ec2 --profile $profile --region $region describe-instances --query "Reservations[*].Instances[*].[InstanceId,LaunchTime,State]"

Output is showing in the below order:

[
    [
        [
            "i-0123456789",
            "2023-07-17T04:00:18+00:00",
            {
                "Code": 8,
                "Name": "running"
            }
        ]
    ],
    [
        [
            "i-9876543210",
            "2023-07-17T04:00:16+00:00",
            {
                "Code": 16,
                "Name": "running"
            }
        ]
    ]
]

with the help of iteration, I would like to see the output in the below format or any other way, so that for one value/item I would filter/iterate the other item

instanceid            launchdateb               code        Name       
i-0123456789       2023-07-17T04:00:18+00:00      8        running   
i-9876543210       2023-07-17T04:00:16+00:00     16        running

Have tried with for-each/foreach-object loop but it didn't work out, looking for your ideas/assistance on this.

and while doing the looping/iterating - how do I compare the Launchdate with any other date separately ?

Latest update of snippets, but not able to see the iterated values:

    $objects = aws ec2 --profile $profile --region $region describe-instances --query "Reservations[*].Instances[*].[InstanceId,LaunchTime,State]" | Convertform-json 
  $objects | ForEach-Object {  
    # Construct and implicitly output a [pscustomobject]
    # instance from the elements of each nested array.
    $a = [pscustomobject] @{
      instanceid = $_[0][0]
      launchdateb = [datetimeoffset] $_[0][1]
      code = $_[0][2].Code
      name = $_[0][2].Name
      
   }
   Write-Output ("instanceid :"+$a.ToString())
}
Money Times
  • 135
  • 9
  • You have a typo in your update: `Convertform-json` -> `ConvertFrom-json`. The core problem is your attempt to use `.ToString()` on a `[pscustomobject]` instance, which - regrettably, due to a long-standing bug - yields the _empty string_ (see [GitHub issue #6163](https://github.com/PowerShell/PowerShell/issues/6163)). Replace `Write-Output ("instanceid:"+$a.ToString())` with `Write-Output "instanceid: $a"` or just `"instanceid : $a"` to fix this problem (that is, the bug doesn't surface in _string interpolation_). – mklement0 Jul 22 '23 at 16:30
  • @mklement0 Sorry Sir, I have rectified those errors and have tried in various way but still not able to get any value on **$a** , I have used **Write-Output ("instanceid :"+$a)** to check the vaule but this is showing blank – Money Times Jul 25 '23 at 13:54
  • Does the self-contained sample code at the bottom of my answer not produce the expected output for you? If so, then the implication is that your real-life JSON input data doesn't have the structure that you think it does, which is an unrelated problem. – mklement0 Jul 25 '23 at 13:58
  • @mklement0 you helped on this a lot perhaps I'm still making some issue within this snippet which I'm not able to catch it, I will accept it as an Answer and will ask for the community for a reference on this since the output looks queit uniform in this case - something is going wrong from myside. Thank you Sir, Really appreciate and admire your knowledge on it – Money Times Jul 25 '23 at 14:05
  • My pleasure; I hope a follow-up question will solve the mystery. – mklement0 Jul 25 '23 at 14:09

1 Answers1

2

Given your fixed structure of nested JSON arrays, convert them to [pscustomobject] instances as follows:

# Note: Note how the whole ConvertFrom-Json call
#       must be enclosed in (...) in *Windows PowerShell*.
(ConvertFrom-Json (
  aws ec2 --profile $profile --region $region describe-instances --query "Reservations[*].Instances[*].[InstanceId,LaunchTime,State]"
)) | 
  ForEach-Object {  
    # Construct and implicitly output a [pscustomobject]
    # instance from the elements of each nested array.
    [pscustomobject] @{
      instanceid = $_[0][0]
      launchdateb = [datetimeoffset] $_[0][1]
      code = $_[0][2].Code
      name = $_[0][2].Name
   }
}

Note:

  • Enclosing the ConvertFrom-Json call as a whole in (...) ensures that the array resulting from the parsed JSON is enumerated in the pipeline, meaning that its elements are sent to ForEach-Object, one by one.

  • This is only necessary in Windows PowerShell, where ConvertFrom-Json unexpectedly outputs arrays as a whole; this has been corrected in PowerShell (Core) 7+, so you don't strictly need the (...) enclosure there - see this answer for details.

The above directly outputs the resulting [pscustomobject] instances and produces display output similar to the one you request in the question; to capture these objects in a variable, as an array, prepend something like $objects = to the entire command.

Note that casting the .launchdateb property values to [datetimeoffset] or [datetime] is only needed in Windows PowerShell; in PowerShell (Core) 7+, JSON string property values containing ISO 8601 timestamp strings are automatically converted to - invariably local - [datetime] instances by ConvertFrom-Json.

  • The PowerShell 7+ behavior - up to at least v7.3.6 - is somewhat unfortunate, in that the automatic conversion to a local [datetime] (System.DateTime) involves the loss of the specific UTC offset (+00:00 in your data) in the JSON data (which a subsequent [datetimeoffset] cannot recover).

  • GitHub issue #13598 is a proposal to add a -DateKind parameter to ConvertFrom-Json, which would enable -DateKind Offset, causing [datetimeoffset] (System.DateTimeOffset) instances that preserve the UTC offset to be returned. The proposal has been green-lit, but is yet to be implemented as of this writing.

Storing [datetimeoffset] / [datetime] instances in the .launchdateb properties should make it easy to perform date comparisons.

To spell out how to capture each [pscustomobject] in a variable and operate on it inside the ForEach-Object script block, providing the JSON input via a here-string:

(@'
[
  [
      [
          "i-0123456789",
          "2023-07-17T04:00:18+00:00",
          {
              "Code": 8,
              "Name": "running"
          }
      ]
  ],
  [
      [
          "i-9876543210",
          "2023-07-17T04:00:16+00:00",
          {
              "Code": 16,
              "Name": "running"
          }
      ]
  ]
]
'@ | ConvertFrom-Json) |
  ForEach-Object {  
    $obj = 
      [pscustomobject] @{
        instanceid = $_[0][0]
        launchdateb = [datetimeoffset] $_[0][1]
        code = $_[0][2].Code
        name = $_[0][2].Name
      }
    # Output a string representation of the captured object.
    "Object: $obj"
}

The above yields (note the hashtable-like representation of [pscustomobject]s in the context of string interpolation (inside "...", an expandable (double-quoted) string)):

Object: @{instanceid=i-0123456789; launchdateb=07/17/2023 04:00:18 +00:00; code=8; name=running}
Object: @{instanceid=i-9876543210; launchdateb=07/17/2023 04:00:16 +00:00; code=16; name=running}

Note:

  • Do not use "Object: " + $obj.ToString() in lieu of "Object: $obj" above: due to a long-standing bug - see GitHub issue #6163 - calling .ToString() on [pscustomobject] instances yields the empty string, up to at least PowerShell v7.3.6; by contrast, string interpolation ("$obj") is not affected.
mklement0
  • 382,024
  • 64
  • 607
  • 775
  • Excellent answer. I'd suggest considering `[DateTimeOffset]` for the `launchdateb` type, it'll retain consisting string formatting with the input (still comparable to `[datetime]`) :) – Mathias R. Jessen Jul 20 '23 at 21:42
  • Thanks, @MathiasR.Jessen. Please see my update: I've switched to `[datetimeoffset]`, but note that it wouldn't help in PS Core; the linked GitHub proposal may offer a solution in the future. – mklement0 Jul 20 '23 at 22:02
  • @MathiasR.Jessen, what I meant to say is that a `[datetimeoffset]` cast doesn't help in PS Core with respect to the loss of the _UTC-offset information_ - but it _is_ worth doing in WinPS. As for _string formatting_: the default stringification of a `[datetimeoffset]` instance doesn't use an ISO 8601 format and will therefore be somewhat different - though it _will_ show a UTC offset (which is probably what you meant); however, in PS Core, the to-local conversion means that, unless the caller happens to be in the same time zone, the stringification will show a _different_ UTC offset. – mklement0 Jul 20 '23 at 22:23
  • @mklement0 sorry for asking this, Sir would you let me know that how should I get output of each object ? I am using $Value = Your_Answer, then dong $Value.instanceid but not getting anything ? And how do I construct and implicitly output a [pscustomobject] if you kindly give an example to elaborate this part please – Money Times Jul 21 '23 at 10:52
  • @MoneyTimes, the command already constructs and implicitly outputs the `[pscustomobject]` instances, so there's nothing else to do but to _capture_ the output objects, which `$value = ConvertFrom-Json ...` should indeed do, and `$value.instanceId` should then list _all_ instance IDs. If, by contrast, you want to store _each_ `[pscustomobject]` instance in a variable, so as to work with it _inside the `ForEach-Object` script block_, use `$value = [pscustomobject] @{ ... }` – mklement0 Jul 21 '23 at 11:32
  • @mklement0 I am doing this but not getting value sofar :( ForEach-Object { # Construct and implicitly output a [pscustomobject] # instance from the elements of each nested array. $a = [pscustomobject] @{ instanceid = $_[0][0] launchdateb = [datetimeoffset] $_[0][1] code = $_[0][2].Code name = $_[0][2].Name } Write-Output ("value "+$a) } Am I missing anything here ? or am I correctly using write-out – Money Times Jul 21 '23 at 12:39
  • @MoneyTimes, the lack of line breaks in comments and the limited formatting makes code hard to read. If the problem is that the property _values_ of the object stored in `$a` are blank, the implication is that the actual JSON your `aws` command returns differs from the sample JSON shown in your question. Also, just to rule out an obvious problem: Your `ForEach-Object` call must be directly preceded by `ConvertFrom-Json ( aws ec2 --profile $profile --region $region describe-instances --query "Reservations[*].Instances[*].[InstanceId,LaunchTime,State]" ) |` – mklement0 Jul 21 '23 at 12:44
  • @MathiasR.Jessen if you kindly post that how do I see the output of each object separately by Write-object, that would be great – Money Times Jul 21 '23 at 13:26
  • 1
    @MoneyTimes I'm not quite sure what you mean. You can assign the output from the pipeline suggested in the answer: `[array]$results = ConvertFrom-Json ...` and then inspect each item in the resulting array individually: eg. `$results[0]` for the first object – Mathias R. Jessen Jul 21 '23 at 13:39
  • @MoneyTimes, I forgot to consider that you may be running _Windows PowerShell_ rather than [_PowerShell (Core) 7+_](https://github.com/PowerShell/PowerShell/blob/master/README.md). If so, you need to enclose the entire `ConvertFrom-Json` call in `(...)` - please see my update for details; I've also added a bottom section that spells out a full example with your sample JSON data. – mklement0 Jul 21 '23 at 14:32
  • @mklement0 Yes I have used part which I have updated in the answer but somehow the iterated values are not parsing through object, perhaps I made some misses here !!? trying to figure it out Note able to fetch out any value while doing **$a.** – Money Times Jul 22 '23 at 16:15
  • @mklement0 once you are available, could you take a look the part I updated with your answer in my question below plz ? just would like to see if it's working on powershell ise – Money Times Jul 24 '23 at 13:40
  • @MoneyTimes, I've already left a comment directly on your question to explain the problem with the snippet currently at the bottom of your question. As an aside, regarding use of the Windows PowerShell ISE, I suggest avoiding it and migrating to Visual Studio Code - see the next comment. – mklement0 Jul 24 '23 at 13:42
  • As an aside: The PowerShell ISE is [no longer actively developed](https://docs.microsoft.com/en-us/powershell/scripting/components/ise/introducing-the-windows-powershell-ise#support) and [there are reasons not to use it](https://stackoverflow.com/a/57134096/45375) (bottom section), notably not being able to run PowerShell (Core) 7+. The actively developed, cross-platform editor that offers the best PowerShell development experience is [Visual Studio Code](https://code.visualstudio.com/) with its [PowerShell extension](https://marketplace.visualstudio.com/items?itemName=ms-vscode.PowerShell). – mklement0 Jul 24 '23 at 13:42