Skip to Content

Snowflake SnowPro Core: Best Function to Unload Data from Snowflake Table to JSON File:

By: Author Alex Lim

Posted on

Categories Exam

Discover the recommended Snowflake function for unloading data from a relational table into a JSON file. Learn how to efficiently export your data using the right approach.

Table of Contents

Question

Use of which Snowflake function is recommended when unloading data from a relational table into a JSON file?

A. TO_JSON
B. TO_VARIANT
C. OBJECT_INSERT
D. OBJECT_CONSTRUCT

Answer

D. OBJECT_CONSTRUCT

Explanation

When unloading data from a relational table in Snowflake into a JSON file, the recommended function to use is OBJECT_CONSTRUCT (Option D).

The OBJECT_CONSTRUCT function allows you to create a JSON object by specifying key-value pairs as arguments. It takes an even number of arguments, where the odd-numbered arguments represent the keys (as strings) and the even-numbered arguments represent the corresponding values. This function is particularly useful when you want to construct a JSON object from individual columns in a relational table.

Here’s an example of how you can use OBJECT_CONSTRUCT to unload data from a table into a JSON file:

COPY INTO @my_stage/output.json
FROM (
SELECT OBJECT_CONSTRUCT(
'id', id,
'name', name,
'age', age
) AS json_data
FROM my_table
)
FILE_FORMAT = (TYPE = JSON);

In this example, the OBJECT_CONSTRUCT function is used within a SELECT statement to create a JSON object for each row in the `my_table` table. The resulting JSON objects are then unloaded into the `output.json` file using the COPY INTO command, specifying the JSON file format.

The other options mentioned are not the most suitable for this specific task:

  • TO_JSON (Option A) is used to convert a SQL value into a JSON string representation. While it can be used to convert data into JSON format, it is not the most efficient or recommended approach for unloading data from a relational table into a JSON file.
  • TO_VARIANT (Option B) is used to convert a SQL value into a Snowflake VARIANT data type, which can store semi-structured data. However, it is not directly related to unloading data into a JSON file.
  • OBJECT_INSERT (Option C) is used to insert a key-value pair into an existing JSON object. It is not the appropriate function for constructing a JSON object from scratch when unloading data from a table.

Therefore, OBJECT_CONSTRUCT (Option D) is the recommended function to use when unloading data from a relational table into a JSON file in Snowflake.

Snowflake SnowPro Core certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Snowflake SnowPro Core exam and earn Snowflake SnowPro Core certification.