Lateral Flatten two columns with different array length in snowflake

snowflake lateral flatten
snowflake parse array
sql flatten array
snowflake lateral flatten join
snowflake array functions
unnest in snowflake
snowflake strip outer array
snowflake flatten xml

i am new to snowflake and currently learning to use Lateral Flatten.

I currently have a dummy table which looks like this:

The data type used for "Customer_Number" & "Cities" is array.

I have managed to understand and apply the Flatten concept to explode the data using the following sql statement:

select c.customer_id, c.last_name, f.value as cust_num, f1.value as city
    from customers as c,
    lateral flatten(input => c.customer_number) f,
    lateral flatten(input => c.cities) f1
    where f.index = f1.index
    order by customer_id;

The output shown is:

As we can clearly see from the dummy table, in row 4 customer_id 104 has 3 numbers and i would like to see all three of it in my output and if there is no matching index value in cities i would like to just see "Null" in "City".

My expected output is: Is this possible to be done ?

The trick is to remove the second lateral, and use the index from the first to choose values from the second array:

  select c.customer_id, c.last_name, f.value as cust_num, cites[f.index] as city
    from customers as c,
    lateral flatten(input => c.customer_number) f
    order by customer_id;

FLATTEN — Snowflake Documentation, Flattens (explodes) compound values into multiple rows. FLATTEN is a table function that takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view (i.e. an inline view that contains correlation referring to other tables Can be a zero-length string (i.e. empty path) if the outermost element is to be flattened. FLATTEN is a table function that produces a lateral view of a VARIANT, OBJECT, or ARRAY column. In this step, we’ll use the function to create two tables with different levels of flattening. In this step, we’ll use the function to create two tables with different levels of flattening.

As long as you can be sure the second record is going to be shorter, you can do:

select customer_id, last_name, list1_table.value::varchar as customer_number, 
split(cities,',')[list1_table.index]::varchar as city
from customers, lateral flatten(input=>split(customer_number, ',')) list1_table;

Otherwise you'd have to do union between the 2 sets of records (a regular union will eliminate duplicates)

Semi-structured Data Considerations — Snowflake Documentation, Data Size Limitations. Storing Semi-structured Data in a VARIANT Column vs. from some software is composed of a single huge array containing multiple records. flattening your object and key data into separate relational columns if your b.value FROM pets a,LATERAL FLATTEN(input => a.v) b WHERE b.value LIKE� The JSON as a whole takes the form of a single array where each entry is a single person's records. If we attach a Flatten Variant component, similar to the previous example, we can flatten the array and output it as columns. First we map two values from the JSON to columns so that the output will consist solely of those two columns.

You may want to use a LEFT OUTER JOIN for this task, but need to create a rowset version of the cities first.

select c.customer_id, c.last_name, f.value as cust_num, f1.value as city
    from customers as c
    cross join lateral flatten(input => c.customer_number) f
    left outer join (select * from customers, lateral flatten(input => cities)) f1
                 on f.index = f1.index
    order by customer_id;

Step 3. Flatten Data — Snowflake Documentation, that produces a lateral view of a VARIANT, OBJECT, or ARRAY column. You can use a LATERAL JOIN and a FLATTEN function to separate events into The function outputs a VALUE column that includes the value of the flattened object. The following output shows the first two records in the new EVENTS table:. FLATTEN is a table function that produces a lateral view of a VARIANT, OBJECT, or ARRAY column. Using the sample data from Load semi-structured Data into Separate Columns , create a table with a separate row for each element in the objects.

SPLIT — Snowflake Documentation, Splits a given string with a given separator and returns the result in an array of strings. functions and constructs operating on semi-structured data, e.g. FLATTEN, multiple records from a single string using the LATERAL FLATTEN construct. takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view,� i am new to snowflake and currently learning to use Lateral Flatten. I currently have a dummy table which looks like this: The data type used for "Customer_Number" & "Cities" is array.

Querying Semi-structured Data — Snowflake Documentation, Using the FLATTEN Function to Parse Nested Arrays Except where noted, the examples in this topic refer to a table named car_sales that contains a single VARIANT column named src : There are two ways to access elements in a JSON object: Unlike the path syntax, these functions can handle irregular paths or path� Using the FLATTEN Function to Parse JSON Arrays FLATTEN is a table function that converts a repeated field into a set of rows. Given one scalar value with many values for a repeated field, FLATTEN unrolls it into many records, one record for each value of the (formerly) repeated field; any non-repeated fields become duplicated to fill out each

Feature: allow selecting all columns from object keys when flattening , the array can be described as such: select path, value from @my_stage/test. json, table(flatten(input=>$1)) ; to generate dynamically column names from functions, which also can't be done from (; select '$1:' || key from @my_stage, lateral flatten($1) group by key; ) This content is a preview of a link. docs. snowflake.net. Using FLATTEN() and lateral joins. In this next example, we’ll use the FLATTEN() function to transform the set of EMPLOYEE nodes under each DEPT node into an array, and then use a LATERAL join to that array. //2.

Comments
  • Thank you so much, awesome now I understand the way to easily achieve my desired output. It is indeed a very efficient solution which you have came up with.
  • Hi, this was something similar to what I tried earlier but unfortunately, it prompt me an error message saying "Invalid argument types for function 'SPLIT': (ARRAY, VARCHAR(1))".
  • SPLIT is something you use on variant, not on array - and according to the error message that is what you're trying to do. What data types exactly are you using there?
  • Owh i see, the data type i used for "cities" is array. So that is why i was getting that error message.