sort output of describe-instances?

Question

I saw the previous question on this topic, but the answer was just "pipe it to a scripting language!", which I find unsatisfying. I know that JMESPath has sort_by, and sort, but I can't figure out how to use them.

I have

aws ec2 describe-instances \
   --filters "Name=tag:Group,Values=production" "Name=instance-state-name,Values=running" "Name=tag:Name,Values=prod-*-${CURRENT_SHA}-*" \
   --query 'Reservations[*].Instances[*].[LaunchTime,InstanceId,PrivateIpAddress,Tags[?Key==`Name`] | [0].Value]' \
   --output table

And it outputs the right data, just in a random order. I want to sort by the last column of the data, Tag Name, aka Tags[?Key==`Name`], which in raw form looks like this:

{
  "Tags": [{
    "Value": "application-server-ab3634b34364a-2",
    "Key": "Name"
  }, {
    "Value": "production",
    "Key": "Group"
  }]
}

Thoughts?

Since JMESPath is evaluated against JSON data, please write the `Tags` line in JSON format, not Python. That is: `{"Tags": [{"Value": "application-server-ab3634b34364a-2", "Key": "Name"}, {"Value": "production", "Key": "Group"}]}` — myrdd, Jun 19 '18 at 06:38

myrdd · Answer 1 · 2018-06-29T07:00:32.367

short answer

add

[] | sort_by(@, &[3])

at the end of your expression. The brackets ([]) will flatten the structure, sort_by(...) will sort the result (which is a four-column table) by the fourth column. The full query will be:

--query 'Reservations[*].Instances[*].[LaunchTime,InstanceId,PrivateIpAddress,Tags[?Key==`Name`] | [0].Value][] | sort_by(@, &[3])'

long answer

inspecting your current query result

According to the describe-instances docs, the structure of the describe-instances output looks like this:

{
  "Reservations": [
    {
      "Instances": [
        {
          "LaunchTime": "..LaunchTime..",
          "InstanceId": "R1I1",
          "PrivateIpAddress": "..PrivateIpAddress..",
          "Tags": [{"Key": "Name", "Value": "foo"}]
        },
        {
          "LaunchTime": "..LaunchTime..",
          "InstanceId": "R1I2",
          "PrivateIpAddress": "..PrivateIpAddress..",
          "Tags": [{"Key": "Name", "Value": "baz"}]
        }
      ]
    },
    {
      "Instances": [
        {
          "LaunchTime": "..LaunchTime..",
          "InstanceId": "R2I1",
          "PrivateIpAddress": "..PrivateIpAddress..",
          "Tags": [{"Key": "Name", "Value": "bar"}]
        }
      ]
    }
  ]
}

Using your original query

--query 'Reservations[*].Instances[*].[LaunchTime,InstanceId,PrivateIpAddress,Tags[?Key==`Name`] | [0].Value]'

will output

[
  [
    [
      "..LaunchTime..",
      "R1I1",
      "..PrivateIpAddress..",
      "foo"
    ],
    [
      "..LaunchTime..",
      "R1I2",
      "..PrivateIpAddress..",
      "baz"
    ]
  ],
  [
    [
      "..LaunchTime..",
      "R2I1",
      "..PrivateIpAddress..",
      "bar"
    ]
  ]
]

flattening the query result

You can see in the above result of your query that you're getting a list of tables ([[{},{}],[{}]]). I suppose you instead want a single non-nested table ([{},{},{}]). To achieve that, simply add [] at the end of your query, i.e.

--query 'Reservations[*].Instances[*].[LaunchTime,InstanceId,PrivateIpAddress,Tags[?Key==`Name`] | [0].Value][]'

This will flatten the structure, resulting in

[
  [
    "..LaunchTime..",
    "R1I1",
    "..PrivateIpAddress..",
    "foo"
  ],
  [
    "..LaunchTime..",
    "R1I2",
    "..PrivateIpAddress..",
    "baz"
  ],
  [
    "..LaunchTime..",
    "R2I1",
    "..PrivateIpAddress..",
    "bar"
  ]
]

Now it's time to sort the table.

sorting the table

When using sort_by you shouldn't forget to prepend the expression by & (ampersand). This way you specify a reference to that expression, which is then passed to sort_by.

example: data | sort_by(@, &@) is equivalent to data | sort(@).

The TagName in the table you create ([LaunchTime,InstanceId,PrivateIpAddress,TagName]) is the fourth column. You can get that column by piping the table to the expression [3]:

TableExpression | [3]

But instead, you want to sort the table by the fourth column. You can do so like this:

TableExpression | sort_by(@, &[3])

and the resulting query will be:

--query 'Reservations[*].Instances[*].[LaunchTime,InstanceId,PrivateIpAddress,Tags[?Key==`Name`][] | [0].Value] | sort_by(@, &[3])'

Query result:

[
  [
    "..LaunchTime..",
    "R2I1",
    "..PrivateIpAddress..",
    "bar"
  ],
  [
    "..LaunchTime..",
    "R1I2",
    "..PrivateIpAddress..",
    "baz"
  ],
  [
    "..LaunchTime..",
    "R1I1",
    "..PrivateIpAddress..",
    "foo"
  ]
]

Thanks, you got me close, it was actually `TableExpression | sort_by(@, &@[0][3])` — ColinK, Jun 19 '18 at 03:12

score 6 · Answer 2 · answered Apr 29 '19 at 18:21

6

As an enhancement to @ColinK's answer, I wanted to sort a table that had custom column headers but struggled with the syntax. I eventually got it to work so I thought I'd share in case someone else wanted to do the same. I added a column for State and sorted by that column.

--query 'sort_by(Reservations[*].Instances[*].{LaunchTime:LaunchTime, ID:InstanceId,IP:PrivateIpAddress,State:State.Name,Name:Tags[?Key==`Name`] | [0].Value}[], &State)'

answered Apr 29 '19 at 18:21

TreverW

440
7
16

Thanks for the answer! Adding link to the jmespath documentation (which aws cli uses) of sort_by function for reference https://jmespath.org/examples.html#sort-by – Kishan B May 16 '23 at 07:26

score 3 · Answer 3 · answered Dec 12 '19 at 10:24

3

Here is an other example that works also:

aws ec2 describe-instances --query 'Reservations[*].Instances[*].{Name:Tags[?Key==`Name`]|[0].Value,Instance:InstanceId} | sort_by(@, &[0].Name)'

answered Dec 12 '19 at 10:24

toto

375
2
10

This kinda worked for my needs, but it failed when the Name value is "None" – Casper Feb 03 '22 at 19:59

score 2 · Accepted Answer · answered Jun 19 '18 at 03:14

2

The answer is to add | sort_by(@, &@[0][3])

aws ec2 describe-instances \
  --filters "Name=tag:Group,Values=production" "Name=instance-state-name,Values=running" "Name=tag:Name,Values=prod-*-${CURRENT_SHA}-*" \
  --query 'Reservations[*].Instances[*].[LaunchTime,InstanceId,PrivateIpAddress,Tags[?Key==`Name`] | [0].Value]| sort_by(@, &@[0][3])' \
  --output table

answered Jun 19 '18 at 03:14

ColinK

438
1
3
8

So in fact you've got a *list of tables* instead of just one table. No? I mean, something like this: `[[ "", "" ],[ "", "" ]]` where `R1`/`R2` and `I1/I2` mean *Reservation 1/2* and *Instance 1/2*, respectively. Please clarify your question. Don't you want to flatten the tables into one table? Right now you are sorting the tables themselves by the `TagName` of each table's first entry. – myrdd Jun 19 '18 at 06:27
/shrug I'm not very familiar with aws ec2, I just pasted the actual query we are using. The final answer I pasted does indeed generate exactly the output I wanted. In the end, I see tables whose rows are sorted by the last column in the table, which is all I wanted. – ColinK Jun 29 '18 at 04:49
1

Okay, I checked the aws ec2 docs, and it's indeed a ”list of tables“. I've updated my answer. I think `| sort_by(@, &@[0][3])` is *not* the correct answer, because it will sort the ”list of tables“ by the `TagName` of each table's first row (!). – myrdd Jun 29 '18 at 07:05

sort output of describe-instances?

4 Answers4

short answer

long answer

inspecting your current query result

flattening the query result

sorting the table

Linked

Related