You're trying to flatten a field that is not a list. The offending field is expr000::struct<>

1

I’m trying to flatten a json array from varchar column that doesn’t have any null values so the query is something like this FLATTEN(CONVERT_FROMJSON(col1)) FROM table_name where 1=1, but i keep getting the error above; but when i execute the CONVERT_FROMJSON(col1) separately and then run “FLATTEN(CONVERT_FROMJSON(col1)) FROM table_name where 1=1” again i get results with no problems, i’m not sure what this is or how i can fix this issue

2

@aradwan007 Something like this will work

SELECT FLATTEN(convert_fromJSON('[{"name":"John"}]'))

But something like the below (without the brackets, wil lgive the same error you get)

SELECT FLATTEN(convert_fromJSON('{"name":"John"}'))

So after you do the below

convert_fromJSON('{"name":"John"}')

Is the resulting data type a list or a STRUCT, if LIST then you can do a FLATTEN

Note: The icon for LIST data type will be something like {.} while that for a STRUCT will be like screenshot below

image
image798×356 13.2 KB