Hi everyone, happy holidays!
I’m using Amazon Redshift as a datasource and one of the columns in the table returns the record as a JSON string. The JSON returned from the query has a format similar to:
{"array_1":[0,128,256,512,1024],"array_2":[0,1,2,3],"version":"v1"}
I would like to be able to visualize array_1
on the x-axis and array_2
on the y-axis as a barchart, but is unable to do so through regular query statements since my column is not a SUPER type. Using json_extract_path_text
, (docs), I’m able to get the response [0,128,256,512,1024]
under array_1
but since it’s just an array vs. multiple rows under the same column, the visualization does not work.
Ideally we don’t want to rely on another data source like QRDS for this and would rather update the existing code for the query runner and visualizations to be able to parse JSON. I’ve gone through creating a new query runner in Redash and how to create new visualization types in Redash, but docs seems to be a bit outdated since the migration to React.
Just to double check I’m on the right track, at a high level I would need to figure out how to:
- Update the query runner for Redshift to be able to parse JSON data (will try to reference the JSON query runner for this).
- Once the JSON array data is parsed into rows under a column, extend the existing chart visualizations to display the parsed
array_1
andarray_2
Would anyone know if there is a better approach or have any references? Any tips or guidance will be greatly appreciated, thank you!