With recursive FLATTEN, INDEX is populated for array elements and is NULL for object-key rows. Filtering to INDEX IS NULL retains the fields of the nested objects; THIS LIKE '%Onyx%' keeps the three fields from Onyx’s object. The result contains the is_dog, name, and species keys with their respective Onyx-record values. Snowflake documents that KEY contains an object key, INDEX identifies an array element, THIS is the element being flattened, and recursive mode expands sub-elements.
Community Discussion