使用jq转JSON到CSV遇字符串问题及sts:AssumeRole多服务主体异常

Let's break down and fix both of your problems step by step:

1. Parsing JSON with empty strings to CSV #

Empty strings in JSON can throw off CSV conversion if not handled correctly—jq's @csv filter expects consistent values, and unhandled nulls/empty fields might lead to malformed output or silent failures.

Fix: #

Use the // "" operator to explicitly replace any null/undefined values with empty strings before passing to @csv. This ensures every field has a valid, CSV-compliant value.

Example command for a basic JSON structure:

jq -r '.[] | [.field1, .field2//"", .field3//""] | @csv' input.json > output.csv

• // "" acts as a fallback: if the field is null or missing, it uses an empty string instead.
• @csv automatically wraps empty strings in double quotes, which follows standard CSV formatting rules.

2. Handling array values for sts:AssumeRole.Principal.Service #

The root issue here is that Service can be either a single string or an array of strings. When it's an array, jq outputs it as ["ssm.amazonaws.com", "ec2.amazonaws.com"] by default, which breaks CSV structure. We need to normalize this to a clean, readable format.

Fix: #

Add a conditional check to detect if Service is an array, then join its elements into a comma-separated string. For single strings, we'll keep the value as-is.

Here's a tailored command for your output.json structure:

jq -r '
.InstanceProfileList[]
| .Roles[]
| .AssumeRolePolicyDocument.Statement[]
| select(.Action == "sts:AssumeRole")
| [
    .InstanceProfileId,
    (.Principal.Service // "") | if type == "array" then . | join(", ") else . end
  ] | @csv' output.json > result.csv

• (.Principal.Service // "") handles cases where Service is missing or null, replacing it with an empty string to avoid gaps in your CSV.
• The if type == "array" then ... else ... end block converts arrays to comma-separated strings (e.g., "ssm.amazonaws.com, ec2.amazonaws.com") while leaving single strings unchanged.
• select(.Action == "sts:AssumeRole") ensures we only process the relevant statement entries.

If you need to expand array elements into separate rows instead of comma-separated values, use this variant:

jq -r '
.InstanceProfileList[]
| .Roles[]
| .AssumeRolePolicyDocument.Statement[]
| select(.Action == "sts:AssumeRole")
| .Principal.Service as $service
| if $service | type == "array" then $service[] else $service end
| [.InstanceProfileId, .] | @csv' output.json > result.csv

This creates one row per service in the array, which is helpful if you need individual entries for each service principal.

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