Convert JSON to CSV-BASH

Question

JSON file:

"UserDetailList": [
        {
            "UserName": "citrix-xendesktop-ec2-provisioning", 
            "GroupList": [], 
            "CreateDate": "2017-11-07T14:20:14Z", 
            "UserId": "1234556", 
            "Path": "/", 
            "AttachedManagedPolicies": [
                {
                    "PolicyName": "AmazonEC2FullAccess", 
                    "PolicyArn": "arn:aws:iam::aws:policy/AmazonEC2FullAccess"
                }, 
                {
                    "PolicyName": "AmazonS3FullAccess", 
                    "PolicyArn": "arn:aws:iam::aws:policy/AmazonS3FullAccess"
                }
            ], 
            "Arn": "arn:aws:iam::1234567890:user/citrix-xendesktop-ec2-provisioning"
        }, 
        {
            "UserName": "rundeck-read-only-iam-permissions", 
            "GroupList": [], 
            "CreateDate": "2018-03-09T11:13:38Z", 
            "UserId": "AIDAJQOQGKISLCWDXG6EQ", 
            "Path": "/", 
            "AttachedManagedPolicies": [
                {
                    "PolicyName": "IAMReadOnlyAccess", 
                    "PolicyArn": "arn:aws:iam::aws:policy/IAMReadOnlyAccess"
                }
            ], 
            "Arn": "arn:aws:iam::279052847476:user/rundeck-read-only-iam-permissions"
        }
    ],

with jq -r '.UserDetailList[] | [.UserName] | @csv' output.json > fileout2.csv

I can get

citrix-xendesktop-ec2-provisioning"
"rundeck-read-only-iam-permissions"

How to get IAM policies for these 2 users, i need to extract AmazonEC2FullAccess and AmazonS3FullAccess under AttachedManagedPolicies ?

so output can be

citrix-xendesktop-ec2-provisioning",AmazonEC2FullAccess
citrix-xendesktop-ec2-provisioning",AmazonS3FullAccess
rundeck-read-only-iam-permissions,IAMReadOnlyAccess

What do you mean by ```i need to extract AmazonEC2FullAccess and AmazonS3FullAccess under AttachedManagedPolicies``` ? Do you need to list all managed policies that are attached to the specified user? — ALex_hha, Mar 09 '18 at 12:48
@ALex_hha, i need for all users all attached policies, in this case i have 2 users and 3 policies (2 policies attached to 1 user) — Milister, Mar 09 '18 at 12:50

score 3 · Answer 1 · answered Mar 09 '18 at 14:09

3

got it working: jq -r '.UserDetailList[] | .UserName as $u | .AttachedManagedPolicies[] | [$u, .PolicyName] | @csv' output.json > fileout.csv

answered Mar 09 '18 at 14:09

Milister

193
1
2
8

Convert JSON to CSV-BASH

1 Answers1