CODEWITHSUNDEEP
jsonkeyjq
M. Gleria
M. Gleria

Reputation: 513

How to build a parent key based on a sub-string of another value using jq?

I'm re-shaping a JSON file which base content is the output of an AWS API call. From the raw output, I'm currently extracting the fields that I'm interested in the format I want except for a small detail that motivated this question.

Specifically, my input comes from the output of aws rds describe-db-instances command and contains the info of multiple RDS instances. Taking an example with 2 instances, this is how it looks:

{
"DBInstances": [
    {
        "DBInstanceIdentifier": "db1-name",
        "DBInstanceClass": "db.m5.xlarge",
        "Engine": "oracle-ee",
        "DBInstanceStatus": "available",
        "MasterUsername": "user",
        "DBName": "RANDOM",
        "Endpoint": {
            "Address": "some-endpoint.rds.amazonaws.com",
            "Port": 5698,
            "HostedZoneId": "GHDSFHFSDHSDH"
        },
        "AllocatedStorage": 4000,
        "InstanceCreateTime": "2018-07-23T23:21:42.361000+00:00",
        "PreferredBackupWindow": "09:30-07:00",
        "BackupRetentionPeriod": 14,
        "DBSecurityGroups": [],
        "VpcSecurityGroups": [
            {
                "VpcSecurityGroupId": "sg-xxxxxxxxxxxxxxxxx",
                "Status": "active"
            },
            {
                "VpcSecurityGroupId": "sg-xxxxxxxxxxxxxxxxx",
                "Status": "active"
            }
        ],
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "DB1-parameter",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "AvailabilityZone": "ZONE1",
        "DBSubnetGroup": {
            "DBSubnetGroupName": "dbsubnetgroup-1",
            "DBSubnetGroupDescription": "dbsubnetgroup-1",
            "VpcId": "vpc-xxxxxxxxxxxxxxxxx",
            "SubnetGroupStatus": "Complete",
            "Subnets": [
                {
                    "SubnetIdentifier": "subnet-xxxxxxxxxxxxxxxxx",
                    "SubnetAvailabilityZone": {
                        "Name": "az1"
                    },
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-xxxxxxxxxxxxxxxxx",
                    "SubnetAvailabilityZone": {
                        "Name": "az2"
                    },
                    "SubnetStatus": "Active"
                }
            ]
        },
        "PreferredMaintenanceWindow": "sat:07:00-sat:07:30",
        "PendingModifiedValues": {},
        "LatestRestorableTime": "2020-03-27T18:54:25+00:00",
        "MultiAZ": false,
        "EngineVersion": "X.X.X",
        "AutoMinorVersionUpgrade": false,
        "ReadReplicaDBInstanceIdentifiers": [],
        "LicenseModel": "bring-your-own-license",
        "Iops": 5000,
        "OptionGroupMemberships": [
            {
                "OptionGroupName": "optiongroupName",
                "Status": "in-sync"
            }
        ],
        "CharacterSetName": "WE8ISO8859P15",
        "PubliclyAccessible": false,
        "StorageType": "io1",
        "DbInstancePort": 0,
        "StorageEncrypted": true,
        "KmsKeyId": "someKey",
        "DbiResourceId": "db-xxxxxxxxxxxxxxxxxxxxxxxxx",
        "CACertificateIdentifier": "rds-ca-2019",
        "DomainMemberships": [],
        "CopyTagsToSnapshot": true,
        "MonitoringInterval": 0,
        "DBInstanceArn": "someARN",
        "IAMDatabaseAuthenticationEnabled": false,
        "PerformanceInsightsEnabled": false,
        "DeletionProtection": false,
        "AssociatedRoles": []
    },
    {
        "DBInstanceIdentifier": "db2-name",
        "DBInstanceClass": "db.m5.large",
        "Engine": "oracle-ee",
        "DBInstanceStatus": "available",
        "MasterUsername": "user2",
        "DBName": "XXXX",
        "Endpoint": {
            "Address": "endpoint2.rds.amazonaws.com",
            "Port": 8974,
            "HostedZoneId": "FASDFDS54FSA"
        },
        "AllocatedStorage": 100,
        "InstanceCreateTime": "2020-04-23T21:38:53.023000+00:00",
        "PreferredBackupWindow": "01:00-05:30",
        "BackupRetentionPeriod": 35,
        "DBSecurityGroups": [],
        "VpcSecurityGroups": [
            {
                "VpcSecurityGroupId": "sg-xxxxxxxxxxxxxxxxx",
                "Status": "active"
            }
        ],
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "default",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "AvailabilityZone": "AZ-2",
        "DBSubnetGroup": {
            "DBSubnetGroupName": "subnet-group",
            "DBSubnetGroupDescription": "",
            "VpcId": "vpc-xxxxxxxxxxxxxxxxx",
            "SubnetGroupStatus": "Complete",
            "Subnets": [
                {
                    "SubnetIdentifier": "subnet-xxxxxxxxxxxxxxxxx",
                    "SubnetAvailabilityZone": {
                        "Name": "AZ-1"
                    },
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-xxxxxxxxxxxxxxxxx",
                    "SubnetAvailabilityZone": {
                        "Name": "AZ-2"
                    },
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-xxxxxxxxxxxxxxxxx",
                    "SubnetAvailabilityZone": {
                        "Name": "AZ-3"
                    },
                    "SubnetStatus": "Active"
                }
            ]
        },
        "PreferredMaintenanceWindow": "sun:08:39-sun:09:09",
        "PendingModifiedValues": {},
        "LatestRestorableTime": "2020-07-27T18:53:18+00:00",
        "MultiAZ": false,
        "EngineVersion": "X.X.X",
        "AutoMinorVersionUpgrade": false,
        "ReadReplicaDBInstanceIdentifiers": [],
        "LicenseModel": "bring-your-own-license",
        "Iops": 2000,
        "OptionGroupMemberships": [
            {
                "OptionGroupName": "optiongroup-name",
                "Status": "in-sync"
            }
        ],
        "CharacterSetName": "AL32UTF8",
        "PubliclyAccessible": false,
        "StorageType": "io1",
        "DbInstancePort": 0,
        "StorageEncrypted": true,
        "KmsKeyId": "someARN",
        "DbiResourceId": "db-xxxxxxxxxxxxxxxxx",
        "CACertificateIdentifier": "rds-ca-2019",
        "DomainMemberships": [],
        "CopyTagsToSnapshot": false,
        "MonitoringInterval": 0,
        "DBInstanceArn": "someARN",
        "IAMDatabaseAuthenticationEnabled": false,
        "PerformanceInsightsEnabled": false,
        "DeletionProtection": false,
        "AssociatedRoles": []
    }
]

}

This is my current output:

[
  {
    "DBInstancePrefix": {
      "DBInstanceClass": "db.m5.xlarge",
      "DBInstanceIdentifier": "db1-name",
      "DBName": "RANDOM",
      "DBParameterGroupName": "DB1-parameter",
      "DBSubnetGroupName": "dbsubnetgroup-1",
      "KmsKeyId": "someKey",
      "OptionGroupName": "optiongroupName",
      "VpcSecurityGroupIds": [
        "sg-xxxxxxxxxxxxxxxxx",
        "sg-xxxxxxxxxxxxxxxxx"
      ]
    }
  },
  {
    "DBInstancePrefix": {
      "DBInstanceClass": "db.m5.large",
      "DBInstanceIdentifier": "db2-name",
      "DBName": "XXXX",
      "DBParameterGroupName": "default",
      "DBSubnetGroupName": "subnet-group",
      "KmsKeyId": "someARN",
      "OptionGroupName": "optiongroup-name",
      "VpcSecurityGroupIds": [
        "sg-xxxxxxxxxxxxxxxxx"
      ]
    }
  }
]

This is my current JQ filter:

. | [.[] | map(.) | .[] | {DBInstancePrefix: {DBInstanceClass: .DBInstanceClass, DBInstanceIdentifier: .DBInstanceIdentifier, DBName: .DBName, DBParameterGroupName:.DBParameterGroups[].DBParameterGroupName, DBSubnetGroupName: .DBSubnetGroup.DBSubnetGroupName, KmsKeyId:.KmsKeyId, OptionGroupName: .OptionGroupMemberships[].OptionGroupName, VpcSecurityGroupIds: [.VpcSecurityGroups | .[] | .VpcSecurityGroupId] }}]

You can verify it on this snippet on jqplay.org.

What I need is to turn the parent key "DBInstancePrefix" dynamic based on a substring from "DBInstanceIdentifier" key. So for the example names I wrote would be:

"db1-name" >>> "db1"

"db2-name" >>> "db2"

So, my desired output would be:

[
  {
    "db1": {
      "DBInstanceClass": "db.m5.xlarge",
      "DBInstanceIdentifier": "db1-name",
      "DBName": "RANDOM",
      "DBParameterGroupName": "DB1-parameter",
      "DBSubnetGroupName": "dbsubnetgroup-1",
      "KmsKeyId": "someKey",
      "OptionGroupName": "optiongroupName",
      "VpcSecurityGroupIds": [
        "sg-xxxxxxxxxxxxxxxxx",
        "sg-xxxxxxxxxxxxxxxxx"
      ]
    }
  },
  {
    "db2": {
      "DBInstanceClass": "db.m5.large",
      "DBInstanceIdentifier": "db2-name",
      "DBName": "XXXX",
      "DBParameterGroupName": "default",
      "DBSubnetGroupName": "subnet-group",
      "KmsKeyId": "someARN",
      "OptionGroupName": "optiongroup-name",
      "VpcSecurityGroupIds": [
        "sg-xxxxxxxxxxxxxxxxx"
      ]
    }
  }
]

Any ideas or suggestions? Thanks for reading.

Upvotes: 1

Views: 101

Answers (2)

M. Gleria
M. Gleria

Reputation: 513

I took the long path and since I couldn't resolve it on the same filter I did another one that takes the output of the first one and thus achieved the desired result. But it is awful compared with the answer selected as the solution.

Based on Inian answer, I did a small modification since my real DBInstanceIdentifier values have the following format: <name>-db-<environment> and I need DBInstancePrefix becomes <name>-db.

So, my final filter is:

.DBInstances |
map
(      
  {        
    ( .DBInstanceIdentifier|split("-")[0:2] | join("-") ): {
      DBInstanceClass,
      DBInstanceIdentifier,
      DBName,
      DBParameterGroupName:.DBParameterGroups[].DBParameterGroupName,
      DBSubnetGroupName: .DBSubnetGroup.DBSubnetGroupName,
      KmsKeyId,
      OptionGroupName: .OptionGroupMemberships[].OptionGroupName,
      VpcSecurityGroupIds: [.VpcSecurityGroups[] | .VpcSecurityGroupId ]        
      }     
  } 
)

Upvotes: 0

Inian
Inian

Reputation: 85530

To manipulate the object key-name as you desire, you can apply the filter operation inside (..). Any operation done inside it, the result is preserved "literally".

Your case demands the DBInstanceIdentifier to be split by - and using the first element in the resultant array.

With that and few other trivial modifications, you need

.DBInstances |
map
(
  {
    ( .DBInstanceIdentifier | split("-")[0] ): {
         DBInstanceClass,
         DBInstanceIdentifier,
         DBName,
         DBParameterGroupName:.DBParameterGroups[].DBParameterGroupName,
         DBSubnetGroupName: .DBSubnetGroup.DBSubnetGroupName,
         KmsKeyId,
         OptionGroupName: .OptionGroupMemberships[].OptionGroupName,
         VpcSecurityGroupIds: [.VpcSecurityGroups[] | .VpcSecurityGroupId ]
      }
  }
)

jqplay - Demo

Upvotes: 2

Related Questions