Need help to remove special characters and split to columns

[Georgia_H]

Hello, I need help to remove special characters on Resource column and split the strings into columns (Version, URL, Type, Data, Location) respectively.

Please see sample data and expected result as attached.

Sample data:

Name	Asset_Type	Resource
//cloudsql.googleapis.com/projects/prusgkass-nprd-uat-mjn8cs-5aec/backups/	sqladmin.googleapis.com/Backup	{"v":{"f":[{"v":"v1beta4"},{"v":"https://www.googleapis.com/discovery/v1/apis/sqladmin/v1beta4/rest"},{"v":"Backup"},{"v":null},{"v":null},{"v":"{\"backupInterval\":{\"endTime\":\"2024-04-05T00:22:42.758Z\",\"startTime\":\"2024-04-05T00:21:16.922Z\"},\"backupKind\":\"SNAPSHOT\",\"instance\":\"neo-dd9c9330\",\"kind\":\"sql#backup\",\"kmsKey\":\"projects/prusgrass-nprd-infra-shared/locations/asia-southeast1/keyRings/prusgrass-nprd-uat-mjn4cs-5aec-az1/cryptoKeys/default\",\"kmsKeyVersion\":\"projects/prusgrass-nprd-infra-shared/locations/asia-southeast1/keyRings/prusgrass-nprd-uat-mjn4cs-5aec-az1/cryptoKeys/default/cryptoKeyVersions/2\",\"location\":\"asia-southeast1\",\"name\":\"projects/prusgrass-nprd-uat-mjn4cs-5aec/backups/00430e9f-9711-4c21-9aba-1f6bf7dc6781\",\"selfLink\":\"https://sqladmin.googleapis.com/sql/v1beta4/projects/prusgrass-nprd-uat-mjn4cs-5aec/backups/00430e9f...",\"state\":\"SUCCESSFUL\",\"type\":\"AUTOMATED\"}"},{"v":"asia-southeast1"}]}}

[aatalai · Accepted solution]

@Georgia_H does this help with the JSON Parse?

[Georgia_H]

Hi @aatalai 

Thanks for your prompt help. Yes, the JSON parse tool should work but I need to transpose the JSON_ValueString data (from rows to columns) after the JSON parse tool. Please advise if i've done it correctly. See attachment.

[aatalai · Accepted solution]

@Georgia_H look at the cross tab tool

[Georgia_H]

Hi @aatalai 

Thanks for your prompt help. Yes, the JSON parse tool should work but I need to transpose the JSON_ValueString data (from rows to columns) after the JSON parse tool. Please advise if i've done it correctly. See attachment.