query nested path with AWS CLI and output as Table

Question

I'm trying to ouput a table using the --query command in AWSCLI, but for some reason I cant get the values in the same row. What is the error?

the .json file is

{
"Reservations": [
    {
        "Groups": [],
        "Instances": [
            {
                "AmiLaunchIndex": 0,
                "ImageId": "ami-2a7d75c0",
                "InstanceId": "i-099569e17a368446b",
                "InstanceType": "t2.micro",
                "KeyName": "ar_ecs_key",
                "LaunchTime": "2018-08-18T17:59:49.000Z",
                "Monitoring": {
                    "State": "disabled"
                },
                "Placement": {
                    "AvailabilityZone": "eu-west-1c",
                    "GroupName": "",
                    "Tenancy": "default"
                },
                "PrivateDnsName": "ip-172-31-46-99.eu-west-1.compute.internal",
                "PrivateIpAddress": "172.31.46.99",
                "ProductCodes": [],
                "PublicDnsName": "ec2-34-245-58-217.eu-west-1.compute.amazonaws.com",
                "PublicIpAddress": "34.245.58.217",
                "State": {
                    "Code": 16,
                    "Name": "running"
                },
                "StateTransitionReason": "",
                "SubnetId": "subnet-3407646f",
                "VpcId": "vpc-24ddec43",
                "Architecture": "x86_64",
                "BlockDeviceMappings": [
                    {
                        "DeviceName": "/dev/sda1",
                        "Ebs": {
                            "AttachTime": "2018-08-18T17:59:49.000Z",
                            "DeleteOnTermination": true,
                            "Status": "attached",
                            "VolumeId": "vol-0d9f4ab048e7999b7"
                        }
                    }
                ],
                "ClientToken": "",
                "EbsOptimized": false,
                "EnaSupport": true,
                "Hypervisor": "xen",
                "NetworkInterfaces": [
                    {
                        "Association": {
                            "IpOwnerId": "amazon",
                            "PublicDnsName": "ec2-34-245-58-217.eu-west-1.compute.amazonaws.com",
                            "PublicIp": "34.245.58.217"
                        },
                        "Attachment": {
                            "AttachTime": "2018-08-18T17:59:49.000Z",
                            "AttachmentId": "eni-attach-0bbfc61bf0a6729ad",
                            "DeleteOnTermination": true,
                            "DeviceIndex": 0,
                            "Status": "attached"
                        },
                        "Description": "",
                        "Groups": [
                            {
                                "GroupName": "default",
                                "GroupId": "sg-98ce7ee3"
                            }
                        ],
                        "Ipv6Addresses": [],
                        "MacAddress": "0a:df:58:fe:e5:d2",
                        "NetworkInterfaceId": "eni-02dcc4cbc45726b77",
                        "OwnerId": "300746241447",
                        "PrivateDnsName": "ip-172-31-46-99.eu-west-1.compute.internal",
                        "PrivateIpAddress": "172.31.46.99",
                        "PrivateIpAddresses": [
                            {
                                "Association": {
                                    "IpOwnerId": "amazon",
                                    "PublicDnsName": "ec2-34-245-58-217.eu-west-1.compute.amazonaws.com",
                                    "PublicIp": "34.245.58.217"
                                },
                                "Primary": true,
                                "PrivateDnsName": "ip-172-31-46-99.eu-west-1.compute.internal",
                                "PrivateIpAddress": "172.31.46.99"
                            }
                        ],
                        "SourceDestCheck": true,
                        "Status": "in-use",
                        "SubnetId": "subnet-3407646f",
                        "VpcId": "vpc-24ddec43"
                    }
                ],
                "RootDeviceName": "/dev/sda1",
                "RootDeviceType": "ebs",
                "SecurityGroups": [
                    {
                        "GroupName": "default",
                        "GroupId": "sg-98ce7ee3"
                    }
                ],
                "SourceDestCheck": true,
                "Tags": [
                    {
                        "Key": "webserver",
                        "Value": "production"
                    }
                ],
                "VirtualizationType": "hvm"
            }
        ],
        "OwnerId": "300746241447",
        "ReservationId": "r-047aab5e090b741af"
    }
]

}

my query is:

$ aws ec2 describe-instances --instance-ids i-099569e17a368446b --query 'Reservations[*].Instances[].{InstanceId:InstanceId,type:InstanceType,Devicename:BlockDeviceMappings.DeviceName,Persistence_on:BlockDeviceMappings[].Ebs.DeleteOnTermination,Status:BlockDeviceMappings[*].Ebs.Status,Volume_id:BlockDeviceMappings.Ebs.VolumeId}' --output table

and the result is:

I'm sure it has something to do with the flattening operator [], but I dont know what. Tried all versions already...

merci in advance

A

Would you want a solution using cli queries or jq? Both will be drastically different. — Jeff Mercado, Aug 20 '18 at 23:41

score 4 · Accepted Answer · answered Aug 21 '18 at 05:14

4

All the values in the nested BlockDeviceMappings are in an array. So you would need to flatten it down to the individual values. There's a couple of ways you could achieve this using JMESPath, you could just index to the first block device.

Reservations[].Instances[].{
    InstanceId: InstanceId,
    type: InstanceType,
    Devicename: BlockDeviceMappings[0].DeviceName,
    Persistence_on: BlockDeviceMappings[0].Ebs.DeleteOnTermination,
    Status: BlockDeviceMappings[0].Ebs.Status,
    Volume_id: BlockDeviceMappings[0].Ebs.VolumeId
}

But I think for readability sake, I would opt for this approach (assuming aws-cli supports merge()):

Reservations[].Instances[].merge(
    {InstanceId: InstanceId, type: InstanceType},
    BlockDeviceMappings[0].{
        Devicename: DeviceName,
        Persistence_on: Ebs.DeleteOnTermination,
        Status: Ebs.Status,
        Volume_id: Ebs.VolumeId
    }
)

On the other hand, I find jq a lot easier to work with. This should yield the same results as json.

$ aws-cli ... | jq '[.Reservations[].Instances[] |
    { InstanceId, type: .InstanceType } +
    (.BlockDeviceMappings[] | {
        Devicename: .DeviceName,
        Persistence_on: .Ebs.DeleteOnTermination,
        Status: .Ebs.Status,
        Volume_id: .Ebs.VolumeId
    })
]'

answered Aug 21 '18 at 05:14

Jeff Mercado

129,526
32
251
272

Hi @Jeff Mercado thanks, i would love to use the jq solution but it doesnt deliver a table output like jmespath does...or does it? – aerioeus Aug 21 '18 at 08:56
worked great: I just did ```$ aws ec2 describe-instances --instance-ids i-099569e17a368446b --query 'Reservations[].Instances[].{InstanceId: InstanceId,type: InstanceType,Devicename: BlockDeviceMappings[0].DeviceName,Persistence_on:BlockDeviceMappings[0].Ebs.DeleteOnTermination,Status: BlockDeviceMappings[0].Ebs.Status,Volume_id: BlockDeviceMappings[0].Ebs.VolumeId}' --output table``` – aerioeus Aug 21 '18 at 09:00
Well you can output as csv or tsv, so you can pass that output to something that could output as a table. You don't have to restrict yourself to using a single program when features you want can be filled by others. Out of curiosity, does the second JMESPath solution work using `merge()`? I don't have access to be able to test that out. – Jeff Mercado Aug 21 '18 at 09:30
is there actually a way to do line breaks in the json part of the statement like / for the shell statement? – aerioeus Aug 21 '18 at 11:58
no the merge didnt work. as for the jq - could you give me a simple example how to ouput something with jq as a table in terminal? merci A – aerioeus Aug 21 '18 at 11:59
one more question: why does [0] pick all elements from the array, i thougt 0 stands for the first element? – aerioeus Aug 22 '18 at 12:27
I'm not sure what you mean about the line breaks. To output csv in jq, check other questions like [this](https://stackoverflow.com/q/32960857/390278). The key is to map the values of the row into an array and pass each to the `@csv` filter. You could use a tool like `column` to print in the terminal if you needed it. The `[0]` will only select the first item of the current node, in this case, the first `BlockDeviceMappings` object. – Jeff Mercado Aug 22 '18 at 16:51

query nested path with AWS CLI and output as Table

1 Answers1