Hi all I am trying to run an analyses on a table with a column that is an array on redash. I am trying to split the array so that I only grab the second element and create a new column.
When I run the desc function to describe the column I get the following datatype:
array(row(term varchar, originalvalue varchar, updatedvalue varchar, nestedvalues array(row(term varchar, originalvalue varchar, updatedvalue varchar))))
For instance here is an example table:
| pkid | datarray |
|---|---|
| 10001 | [["Metadata",null,null,[["jobClassification","","STUDENT"], ["SubClassification","","Junior"],["Name","",""],["studentDateIndicator","","true"],["isDocumentAcceptable","","true"],["notAcceptableReason","",""], ["schoolName","","University of StackOverflow"],["isSchoolNameSelected","","false"],["assignedSystem","","Empathetic"],["studentDocumentDateType","","Issuance Date"],["studentDocumentDate","","04/12/2023"]]]] |
I want to use a SELECT statement to create a new column based of the SubClassification value (in this case Junior)
I tried using case statements to create a new column such as below
CASE WHEN datarray = '%Junior%' THEN 'Junior'
ELSE 'Other'
END AS ClassClassification
I got an error saying the problem was that it was an array type. Does anyone know a work around?
