Run the following FLATTEN command:
FLATTEN
select * from table(flatten(input => parse_json('\{"first_name":"joe", "last_name": "smith" "location": \{"state":"co", \}\}'), recursive => true )) f;
How many rows are returned?
FLATTEN with an empty path expands the outer object, producing rows for first_name, last_name, and location. With RECURSIVE => TRUE, it also expands every nested sub-element, producing an additional row for location.state; therefore the result has four rows. Snowflake’s FLATTEN documentation specifies that recursive mode expands all sub-elements recursively and illustrates that nested object members receive their own rows.
first_name
last_name
location
RECURSIVE => TRUE
location.state
Community Discussion