faltten command takes one variable, how to use it for multiple rows

user_17863843 0
Viewed 52

I'm new to snowflake, trying to figure out a way to flatten multiple rows. Appreciate any help here! we are receiving files from snowpipe. multiple files come into one staged table. each file contains multiple records, however, all the records are in one array variable. When I use flatten command, if there is only one file, it works fine, it flattens the array and separates the records. However, when there are multiple files the flatten command fails with the error "single-row subquery returns more than one row". how to handle this?

{ "test": [ { "a": true, "b": "20" }, { "a": true, "b": "30"}, { "a": false, "b": "40" } ], "Date": "Sun Jan 02 2022 16:00:30 GMT+0000 (Coordinated Universal Time)" }

This is made up data for clarification, Each file contains data like this. SQL I'm using is

select * from table(flatten(select $1:test from @stage))

If there is only one file with the above structure it works fine. However for multiple files it's failing

snowflake-cloud-data-platform flatten
1 Answers

change the order of operations

SELCT
    s.*,
    f.*
FROM @stage s,
TABLE(FLATTEN(input=>s.$1:test)) f

this way you get a row s for every stage file, and then get access to the flattened f results.

user_439920
Related