Using JMESPath and the aws command

16 Apr 2021

If one runs any aws command, one could potentially get a lot of json output! For instance:

aws --output json rds describe-db-cluster-snapshots \
  --db-cluster-identifier my-aurora-cluster \
  --snapshot-type manual \
  --max-items 110

Yields all of this:

{
    "DBClusterSnapshots": [
        {
            "AvailabilityZones": [
                "us-east-1a",
                "us-east-1c",
                "us-east-1d"
            ],
            "DBClusterSnapshotIdentifier": "aurora-prod-mysql-pre-upgrade-11-23-19",
            "DBClusterIdentifier": "my-aurora-cluster",
            "SnapshotCreateTime": "2019-11-23T13:00:06.128000+00:00",
            "Engine": "aurora-mysql",
            "AllocatedStorage": 468,
            "Status": "available",
            "Port": 0,
            "VpcId": "vpc-457afa21",
            "ClusterCreateTime": "2019-06-12T17:43:03.459000+00:00",
            "MasterUsername": "mymasterusername",
            "EngineVersion": "5.7.12",
            "LicenseModel": "aurora-mysql",
            "SnapshotType": "manual",
            "PercentProgress": 100,
            "StorageEncrypted": true,
            "KmsKeyId": "arn:aws:kms:us-east-1:999999999999:key/a41b1046-22ea-4a54-928b-53d73a43841b",
            "DBClusterSnapshotArn": "arn:aws:rds:us-east-1:999999999999:cluster-snapshot:aurora-prod-mysql-pre-upgrade-11-23-19",
            "IAMDatabaseAuthenticationEnabled": false
        },
        {
            "AvailabilityZones": [
                "us-east-1a",
                "us-east-1c",
                "us-east-1d"
            ],
            "DBClusterSnapshotIdentifier": "my-snapshot-2020-12-09-01-00",
            "DBClusterIdentifier": "my-aurora-cluster",
            "SnapshotCreateTime": "2020-12-09T01:00:33.122000+00:00",

...

But If one only wants to print the needed fields of each record, one can do

aws --output json rds describe-db-cluster-snapshots \
  --db-cluster-identifier my-aurora-cluster \
  --snapshot-type manual \
  --max-items 110 \
  --query 'DBClusterSnapshots[*].[DBClusterSnapshotIdentifier, SnapshotCreateTime, DBClusterSnapshotArn]'

and get

[
    [
        "aurora-prod-mysql-pre-upgrade-11-23-19",
        "2019-11-23T13:00:06.128000+00:00",
        "arn:aws:rds:us-east-1:999999999999:cluster-snapshot:aurora-prod-mysql-pre-upgrade-11-23-19"
    ],
    [
        "my-snapshot-2020-12-09-01-00",
        "2020-12-09T01:00:33.122000+00:00",
        "arn:aws:rds:us-east-1:999999999999:cluster-snapshot:my-snapshot-2020-12-09-01-00"
    ],
    [
        "my-snapshot-2020-12-10-01-00",
        "2020-12-10T01:00:33.926000+00:00",
        "arn:aws:rds:us-east-1:999999999999:cluster-snapshot:my-snapshot-2020-12-10-01-00"
    ],
...

And if one wants to sort the output by SnapshotCreateTime, one can do

aws --output json rds describe-db-cluster-snapshots \
  --db-cluster-identifier my-aurora-cluster \
  --snapshot-type manual \
  --max-items 110 \
  --query 'DBClusterSnapshots[*].[DBClusterSnapshotIdentifier, SnapshotCreateTime, DBClusterSnapshotArn] | sort_by(@, &[1])'

and get

[
    [
        "aurora-prod-mysql-pre-upgrade-11-23-19",
        "2019-11-23T13:00:06.128000+00:00",
        "arn:aws:rds:us-east-1:999999999999:cluster-snapshot:aurora-prod-mysql-pre-upgrade-11-23-19"
    ],
    [
        "my-snapshot-2020-12-09-01-00",
        "2020-12-09T01:00:33.122000+00:00",
        "arn:aws:rds:us-east-1:999999999999:cluster-snapshot:my-snapshot-2020-12-09-01-00"
    ],
    [
        "my-snapshot-2020-12-10-01-00",
        "2020-12-10T01:00:33.926000+00:00",
        "arn:aws:rds:us-east-1:999999999999:cluster-snapshot:my-snapshot-2020-12-10-01-00"
    ],
...

More details here: