I have this protobuff messages nested between them:
message Context {
Economy economy = 200;
}
message Economy {
repeated Resource balance = 90;
}
message Resource {
int64 amount = 1;
string currency = 2;
}
I create an object which is pushed to Kinesis Firehose and this is stored as json in S3. Then I copy the content to a table in Redshift.
We have the value of balance in the table as:
[{'amount': '916', 'currency': 'euros'}, {'amount': '112', 'currency': 'dolar'}]
Which makes sense. It's a list as its a repeated field. The issue here is if I want to store these two differents resources as json to be able to use JSON_EXTRACT_PATH function to get the amount or the currency it's not feasible as it's not a valid json.
If I have just one resource such as:
[{'amount': '916', 'currency': 'euros'}]
I can delete the [ and ], and replace the ' for " and then it is a valid json. But not when there are multiple resources as there are no valid keys to identify each resource.
Is there an easy way to have this structure in protobuf and receive as valid json or at least easy to cast to it?
Maybe using a map?
The map would create keys in the different resources and it's easy to convert to json in Redshift?
Or the only way is to have:
message Economy {
string balance = 90;
}
And manually insert there a valid json? The problem with this is it allows "noise" in the balance field. The developer who send the data uses java.