Snowflake Flatten Query for array

Question

Snowflake Table has 1 Variant column and loaded with 3 JSON record. The JSON records is as follows.

{"address":{"City":"Lexington","Address1":"316 Tarrar Springs Rd","Address2":null} {"address":{"City":"Hartford","Address1":"318 Springs Rd","Address2":"319 Springs Rd"} {"address":{"City":"Avon","Address1":"38 Springs Rd","Address2":[{"txtvalue":null},{"txtvalue":"Line 1"},{"Line1":"Line 1"}]}

If you look at the Address2 field in the JSON , The first one holds NULL,2nd String and 3rd one array.

When i execute the flatten query for Address 2 as one records holds array, i get only the 3rd record exploded. How to i get all 2 records with exploded value in single query.

select data:address:City::string, data:address:Address1::string, value:txtvalue::string
from add1 ,lateral flatten( input => data:address:Address2 );

Unknown Unknown · Accepted Answer · 2020-05-26T01:53:51

When I execute the flatten query for Address 2 as one records holds array, I get only the 3rd record exploded

The default behaviour of the FLATTEN table function in Snowflake will skip any columns that do not have a structure to expand, and the OUTER argument controls this behaviour. Quoting the relevant portion from the documentation link above (emphasis mine):

OUTER => TRUE | FALSE

If FALSE, any input rows that cannot be expanded, either because they cannot be accessed in the path or because they have zero fields or entries, are completely omitted from the output.

If TRUE, exactly one row is generated for zero-row expansions (with NULL in the KEY, INDEX, and VALUE columns).

Default: FALSE

Since your VARIANT data is oddly formed, you'll need to leverage conditional expressions and data type predicates to check if the column in the expanded row is of an ARRAY type, a VARCHAR, or something else, and use the result to emit the right value.

A sample query illustrating the use of all above:

SELECT
    t.v:address.City AS city
  , t.v:address.Address1 AS address1
  , CASE
        WHEN IS_ARRAY(t.v:address.Address2) THEN f.value:txtvalue::string
        ELSE t.v:address.Address2::string
    END AS address2
FROM
    add1 t
  , LATERAL FLATTEN(INPUT => v:address.Address2, OUTER => TRUE) f;

P.s. Consider standardizing your input at ingest or source to reduce your query complexity.

Note: Your data example is inconsistent (the array of objects does not have homogenous keys), but going by your example query I've assumed that all keys of objects in the array will be named txtvalue.

Snowflake Flatten Query for array

1 Answers