如何用jq提取JSON中IAM角色-策略映射并转换为CSV

Hey there! Let's walk through how to use jq to convert your IAM role-policy mappings from that JSON file into a clean CSV. I'll assume your JSON has a structure where each entry has a role identifier (like RoleName) and an array of policies (each with a PolicyName or similar field)—adjust the field names if your structure differs, and I'll cover variations below.

Basic Command (One Role-Policy Pair Per Row) #

This is the most common use case, where each role-policy association gets its own line in the CSV (great for filtering or sorting later):

jq -r '
# Write the CSV header first
["RoleName", "PolicyName"] | @csv,
# Iterate over every role entry in the JSON array
.[] | 
# Store the current role name in a variable to reuse across policies
.RoleName as $role_name | 
# Iterate over each policy linked to the role
.Policies[] | 
# Create an array of role + policy name, then convert to CSV format
[$role_name, .PolicyName] | @csv
' output.json > role-policy-mapping.csv

Breakdown of the Command #

• -r: Tells jq to output raw strings (no extra JSON quotes around values)
• ["RoleName", "PolicyName"] | @csv: Generates the header row for your CSV
• .[]: Loops through every top-level object in your JSON array (each object is an IAM role entry)
• .RoleName as $role_name: Saves the current role's name so we can use it with each of its policies
• .Policies[]: Iterates over every policy in the role's policy array
• [$role_name, .PolicyName] | @csv: Creates a 2-element array with the role and policy name, then converts it to properly escaped CSV (handles commas or quotes in names automatically)

Handling Edge Cases #

1. Roles with No Policies: If some roles don't have any policies attached, add // [] to avoid errors (it treats missing Policies fields as empty arrays):

   jq -r '
   ["RoleName", "PolicyName"] | @csv,
   .[] | .RoleName as $role_name | 
   (.Policies // [])[] | 
   [$role_name, .PolicyName] | @csv
   ' output.json > role-policy-mapping.csv
   

2. Policies Are Just Strings (Not Objects): If your Policies field is a simple array of strings (like ["s3-access-policy", "ec2-admin-policy"]) instead of objects, adjust the command to use . instead of .PolicyName:

   jq -r '
   ["RoleName", "PolicyName"] | @csv,
   .[] | .RoleName as $role_name | 
   (.Policies // [])[] | 
   [$role_name, .] | @csv
   ' output.json > role-policy-mapping.csv
   

3. Combine Multiple Policies Per Role into One Line: If you prefer all policies for a single role to be in one comma-separated field:

   jq -r '
   ["RoleName", "Policies"] | @csv,
   .[] | 
   [
     .RoleName,
     # Collect all policy names into an array, then join with commas
     (.Policies[]?.PolicyName // "") | join(",")
   ] | @csv
   ' output.json > role-policy-mapping.csv
   

Just swap out RoleName or PolicyName with the actual field names from your JSON if they're different (like RoleArn or PolicyId). Once you run the command, you'll have a role-policy-mapping.csv file ready to use!

内容的提问来源于stack exchange，提问作者Milister