How to output multiple elements via AWS query?

Question

I trying to output in a column format three keys from 'rds'. If I start with this statement:

aws rds describe-db-instances --region us-east-1 --query 'DBInstances[*].[DBInstanceIdentifier, PubliclyAccessible, VpcSecurityGroupId]' --output text

...I get:

DBId true none

However, if I include another element to pull the security group:

aws rds describe-db-instances --region us-east-1 --query 'DBInstances[*].VpcSecurityGroups[*].[DBInstanceIdentifier, PubliclyAccessible, VpcSecurityGroupId]' --output text

...I get:

none none sg-id

I am not quite sure why my first two columns now become none and the sg-id is presented.

Answer 1

The difference is VpcSecurityGroups[*]:

'DBInstances[*].[DBInstanceIdentifier, PubliclyAccessible, VpcSecurityGroupId]'
vs
'DBInstances[*].VpcSecurityGroups[*].[DBInstanceIdentifier, PubliclyAccessible, VpcSecurityGroupId]'

In the second one, you are receiving none because DBInstanceIdentifier and PubliclyAccessible are not attributes of VpcSecurityGroups.

Here's a portion of an output to show the hierarchy:

{
    "DBInstances": [
        {
            "PubliclyAccessible": false,
            "VpcSecurityGroups": [
                {
                    "Status": "active",
                    "VpcSecurityGroupId": "sg-1203dc23"
                }
            ],
            "DBInstanceIdentifier": "mydbinstance-1"
        }
    ]
}

Try this instead:

'DBInstances[*].[DBInstanceIdentifier, PubliclyAccessible, VpcSecurityGroups[*].VpcSecurityGroupId]'

It won't look very pretty if there are multiple security groups, since it is a one-to-many relationship.