Multiple array level of json rendering with jq

Question

Here is all-ec2-instance.json output from ec2-describe-instances:

{
    "Reservations": [
        {
            "OwnerId": "",
            "ReservationId": "",
            "Groups": [],
            "Instances": [
                {

                    "InstanceId": "i-11111111",
                    "Hypervisor": "xen",
                    "BlockDeviceMappings": [
                        {
                            "DeviceName": "/dev/sda1",
                            "Ebs": {
                                "Status": "attached",
                                "DeleteOnTermination": false,
                                "VolumeId": "vol-11111111",
                                "AttachTime": "2016-04-19T15:53:53.000Z"
                            }
                        },
                        {
                            "DeviceName": "/dev/sdf",
                            "Ebs": {
                                "Status": "attached",
                                "DeleteOnTermination": false,
                                "VolumeId": "vol-22222222",
                                "AttachTime": "2016-05-25T08:22:33.000Z"
                            }
                        },
                        {
                            "DeviceName": "/dev/sdg",
                            "Ebs": {
                                "Status": "attached",
                                "DeleteOnTermination": false,
                                "VolumeId": "vol-33333333",
                                "AttachTime": "2016-02-28T04:22:07.000Z"
                            }
                        }
                    ],
                     "Tags": [
                        {
                            "Value": "ec2-test1",
                            "Key": "Name"
                        }
                    ]
                }
            ]
        },
        {
            "OwnerId": "",
            "ReservationId": "",
            "Groups": [],
            "Instances": [
                {

                    "InstanceId": "i-22222222",
                    "Hypervisor": "xen",
                    "BlockDeviceMappings": [
                        {
                            "DeviceName": "/dev/sda1",
                            "Ebs": {
                                "Status": "attached",
                                "DeleteOnTermination": false,
                                "VolumeId": "vol-44444444",
                                "AttachTime": "2016-05-19T15:53:53.000Z"
                            }
                        },
                        {
                            "DeviceName": "/dev/sdf",
                            "Ebs": {
                                "Status": "attached",
                                "DeleteOnTermination": false,
                                "VolumeId": "vol-55555555",
                                "AttachTime": "2015-08-25T08:22:33.000Z"
                            }
                        },
                        {
                            "DeviceName": "/dev/sdg",
                            "Ebs": {
                                "Status": "attached",
                                "DeleteOnTermination": false,
                                "VolumeId": "vol-66666666",
                                "AttachTime": "2016-07-28T04:22:07.000Z"
                            }
                        }
                    ],
                     "Tags": [
                        {
                            "Value": "ec2-test2",
                            "Key": "Name"
                        }
                    ]
                }
            ]
        }
    ]
}

This is a further question from the previous answer given https://stackoverflow.com/a/39204803/567761 . I could now use the following syntax to get the instance Tag value and also the EBS volumeid:

cat all-ec2-instance.json |jq -r '.Reservations[] | .Instances[] |  .Tags[].Value  +" "+ .BlockDeviceMappings[].Ebs.VolumeId '

but if I want to get another value from the array BlockDeviceMappings I will have wrong result, which mean I can't get the value of different level array to display correctly, for example, Tag value and VolumeId and DeviceName, I will still get redundant of output. I tried:

cat all-ec2-instance.json |jq -c -r '.Reservations[] | .Instances[] |  .Tags[].Value +" "+ .BlockDeviceMappings[].Ebs.VolumeId +" "+ .BlockDeviceMappings[].DeviceName '

I will get 16 result which is wrong, suppose to be 6 only. If by only taking VolumeId and DeviceName which are in the same level array BlockDeviceMappings we can do this:

cat all-ec2-instance.json |jq -c -r '.Reservations[] | .Instances[] |  .BlockDeviceMappings[] |.Ebs.VolumeId +" "+ .DeviceName '

In jq, how do we store the value of the previous level of array and assign it to a later array loop ? I tried using variables and other jq function but not success :(

Here is the desired output (comma or any other delimiter are fine):

ec2-test1,vol-11111111,/dev/sda1
ec2-test1,vol-22222222,/dev/sdf
ec2-test1,vol-33333333,/dev/sdg
ec2-test2,vol-44444444,/dev/sda1
ec2-test2,vol-55555555,/dev/sdf
ec2-test2,vol-66666666,/dev/sdg

Answer 1

I will make another answer to work with jq

First you can rewrite the first query

cat all-ec2-instance.json \
| jq -r '.Reservations[].Instances[] | .Tags[].Value +" "+ .BlockDeviceMappings[].Ebs.VolumeId'

Then if you want to add additional value from the Json like `DeviceName``

cat all-ec2-instance.json \
| jq -r '.Reservations[].Instances[] | .Tags[].Value +" "+(.BlockDeviceMappings[] | .Ebs.VolumeId +" "+ .DeviceName)'

you get the following

"ec2-test1 vol-11111111 /dev/sda1"
"ec2-test1 vol-22222222 /dev/sdf"
"ec2-test1 vol-33333333 /dev/sdg"
"ec2-test2 vol-44444444 /dev/sda1"
"ec2-test2 vol-55555555 /dev/sdf"
"ec2-test2 vol-66666666 /dev/sdg"

which are the 6 lines you expect

Answer 2

As I mentioned in my answer to your original post, you can do most of it directly from the AWS CLI

get the instance Tag value and also the EBS volumeid:

aws ec2 describe-instances --query "Reservations[].Instances[].[BlockDeviceMappings[].Ebs.VolumeId, Tags[].Value]"

If you want to extend to get the DeviceName field

aws ec2 describe-instances --query "Reservations[].Instances[].[BlockDeviceMappings[].[DeviceName, Ebs.VolumeId], Tags[].Value]"

and if you want to keep text (as you run raw data from jq) you can add the --output text flag to the CLI command