How to improve performance on a slow jq script?

Question

I have a JSON doc that looks like:

{
    "SecurityGroups": [
        {
            "GroupName": "database",
            "GroupId": "sg-xxxxxx",
            "VpcId": "vpc-yyyyyyy",
            "IpPermissions": [
                {
                    "FromPort": 22,
                    "ToPort": 22,
                    "IpProtocol": "tcp",
                    "IpRanges": [
                        {
                            "CidrIp": "10.200.0.0/16"
                        },
                        {
                            "CidrIp": "10.200.30.79/32"
                        },
                        {
                            "CidrIp": "10.200.42.0/24"
                        }
                    ],
                    "UserIdGroupPairs": []
                },
                {
                    "FromPort": 5555,
                    "ToPort": 5555,
                    "IpProtocol": "tcp",
                    "IpRanges": [
                        {
                            "CidrIp": "10.200.0.0/16"
                        },
                        {
                            "CidrIp": "10.200.0.155/32"
                        }
                    ],
                    "UserIdGroupPairs": []
                },
                {
                    "FromPort": 4506,
                    "ToPort": 4506,
                    "IpProtocol": "tcp",
                    "IpRanges": [
                        {
                            "CidrIp": "10.200.0.0/16"
                        }
                    ],
                    "UserIdGroupPairs": []
                }
            ]
        }
    ]
}

The output I need to generate is as follow:

sg-xxxxxx|database|22|22|tcp|10.200.0.0/16
sg-xxxxxx|database|22|22|tcp|10.200.30.79/32
sg-xxxxxx|database|22|22|tcp|10.200.42.0/24
sg-xxxxxx|database|5555|5555|tcp|10.200.0.0/16
sg-xxxxxx|database|5555|5555|tcp|10.200.0.155/32
sg-xxxxxx|database|4506|4506|tcp|10.200.0.0/16

I'm able to achieve that by using using jq first to generate a list of GroupId's and then loop through the list to filter data into jq twice. Here's how I did it:

cat json.in | jq -r '.SecurityGroups[]|"\(.GroupId) \(.GroupName)"' | while read groupid groupname
do
        cat json.in | jq ".SecurityGroups[]|{GroupId,IpPermissions,IpPermissionsEgress}|select(.GroupId == \"$groupid\")" | jq -r '.IpPermissions[]|"\(.FromPort)|\(.ToPort)|\(.IpProtocol)|\(.IpRanges[].CidrIp)"' | sed "s/^/$groupid|$groupname|/"
done

My solution is slow and I would like to improve on it, Any pointers?

Answer 1

Here is a more efficient approach. With the -r option, the following filter

    .SecurityGroups[]
  | .GroupId as $gid
  | .GroupName as $gname
  | (.IpPermissions[], .IpPermissionsEgress[]?)
  | .FromPort as $from
  | .ToPort as $to
  | .IpProtocol as $pro
  | .IpRanges[]
  | "\($gid)|\($gname)|\($from)|\($to)|\($pro)|\(.CidrIp)"

with the sample data produces

sg-xxxxxx|database|22|22|tcp|10.200.0.0/16
sg-xxxxxx|database|22|22|tcp|10.200.30.79/32
sg-xxxxxx|database|22|22|tcp|10.200.42.0/24
sg-xxxxxx|database|5555|5555|tcp|10.200.0.0/16
sg-xxxxxx|database|5555|5555|tcp|10.200.0.155/32
sg-xxxxxx|database|4506|4506|tcp|10.200.0.0/16

Note that this includes .IpPermissionsEgress[]? because although it's absent from your sample data and unused in the second part of your script it is nevertheless present in the first part of your sample script so I think you may have intended to include it.