The STRIP_OUTER_ARRAY parameter in the COPY INTO <table> command is used when loading data from a JSON file into a table. This parameter removes the outer array structure from the JSON data and loads separate rows of data into the table.
Understanding the STRIP_OUTER_ARRAY Parameter:
JSON files often contain data in an array format where multiple records are nested within a single outer array.
The STRIP_OUTER_ARRAY parameter helps in simplifying the loading process by removing this outer array, allowing each element within the array to be loaded as a separate row in the target table.
How It Works:
When the STRIP_OUTER_ARRAY parameter is set to TRUE, Snowflake treats each item within the outer array as an individual record.
This eliminates the need for additional parsing or transformation steps that would otherwise be required to handle nested arrays.
Example Usage:
FROM @my_stage/file.json
FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE);
In this example, the JSON file containing an array of objects is loaded into the table my_table.
Each object within the array is loaded as a separate row, without the outer array structure.
Benefits:
Simplifies data loading: By removing the outer array, the data is directly loaded into the table without additional manipulation.
Enhances performance: Streamlines the loading process, reducing the complexity and potential errors in handling nested JSON structures.
References:
Snowflake Documentation: COPY INTO <table>
Snowflake Documentation: JSON File Format Options