I have to store multiple fields containing nested (3-5 levels) JSON specific to our call center application in json_b fields. We then materialize views specific to our analysis needs and push the exec reporting views to Redshift. The data was originally ingested from an S3 source that was the backup for a lambda function that parsed a log stream and put it to S3 as parquet
The JSON to Parquet table loaded data in the following format:
{"ContactId": "val", "Timestamp": "2021-06-02T03:59:59.094Z", "Parameters": {"Text": "Para português, aperte três.", "Voice": "name", "Timeout": "3000", "MaxDigits": "1", "TextToSpeechType": "text"}, "ContactFlowId": "arn:-1993633fcebb", "ContactFlowModuleType": "GetUserInput"}
This has now changed as upstream has moved to remove lambda and put in Kinesis Firehose that lands the data to the same location in parquet. The new payload for those fields looks like this:
{"contactid": "val", "timestamp": "2021-06-02T03:59:59.094Z", "parameters": {"text": "Para português, aperte três.", "voice": "name", "timeout": "3000", "maxdigits": "1", "texttospeechtype": "text"}, "contactflowid": "arn:-1993633fcebb", "contactflowmoduletype": "GetUserInput"}
We didn't immediately realize the impact until the ETL in non-prod started behaving incorrectly and then the materialized views started loading wrong but turns out that queries that formerly had been defined with the original key/value pairs in mind were not being parsed even though nothing changed as far as field name or nest structure.
So:
message->>'ContactId' is distinct from message->>'contactid'.
The issue is that we now have both sets of nests within our core tables. I looked at the Firehose and it doesn't give options to preserve the case on the keys.
I could use CASE statements for mat view definition based on time since there is distinct cutover date but was wondering how I handle queries that span time before cutover to firehose and after.
Initial thought was to use COALESCE(message->>'ContactId', message->>'contactid') but this quickly gets ugly when trying to refactor queries involving aggregations across various nest levels.
Any thought on how I may be optimally work around this. In addition to mat views we are also querying this nests in trigger function from stage to target to where values are cast to specific datatype so concerned coalesce may be computationally too intensive for some of our batch loads.
Any thoughts/ideas would be much appreciated.
Thanks