Converting type ARRAY column to varchar

1

Hi
I have a piece of data that Dremio is recognizing as type ARRAY.(checked in INFOMATION_SCHEMA.COLUMNS)

Lets call the column, fieldName.
sample value: [{“tag”:“General Corporate Purposes”}, {“tag”:“Refinance”}]

how do I convert this a varchar?
I tried using the below but unfortunately none of these approaches work.

CAST(fieldName as varchar)
SQL Error: VALIDATION ERROR: Cast function cannot convert value of type RecordType(VARCHAR(65536) fieldName) ARRAY to type VARCHAR(65536)

CONVERT_TO(fieldName,“json”)
works but it returns varbinary. how do i get this to varchar?

Any suggestions would be appreciated. Thanks!

2

@aki0086 You have to first flatten(column_name)

3

HI @balaji.ramaswamy

Flatten splits the contents of that 1 column into multiple rows? That’s not what I want.

As I will be querying this with other columns, I just want the content of the column to be returned as a varchar along with other columns on the same row
aka… as
[{“tag”:“General Corporate Purposes”}, {“tag”:“Refinance”}]
in text

4

@aki0086 Try something like

SELECT CAST(convert_from(convert_to(fieldName, ‘JSON’), ‘UTF8’) as VARCHAR)

image
image2398×580 184 KB