The COPY INTO <location> command in Snowflake allows you to export or unload data from a Snowflake table into an external stage (e.g., an Amazon S3 bucket) in a specific format like JSON, CSV, or Parquet.
When you want to unload data as JSON using a SELECT statement, Snowflake provides built-in functions to format the data properly into JSON structures. Among the options provided:
* TO_JSON:
* Converts a variant, object, or array data type into its JSON text representation.
* Commonly used when exporting JSON-formatted data with COPY INTO.
* This is the correct function for generating JSON-formatted output while unloading.
Example Usage:
sql
CopyEdit
COPY INTO @my_stage/unload_data.json
FROM (SELECT TO_JSON(data_column) FROM my_table)
FILE_FORMAT = (TYPE = JSON);
* Why the Other Options Are Incorrect:
* TO_OBJECT: This function does not exist in Snowflake.
* PARSE_JSON: This is used to parse a string into a variant data type representing a JSON object but is not used for exporting or creating JSON.
* OBJECT_CONSTRUCT: Creates a JSON object from key-value pairs but does not convert data into JSON format for unloading.
SnowPro References:
* COPY INTO Command Documentation
* TO_JSON Function
* Working with JSON Data in Snowflake