How to Export Snowflake Data to JSON Format? -Example

The JSON format is one of the widely used file formats to store data that you want to transmit to another server or location. Most of the web applications use JSON to exchange the application information. For example, provide parameter values, credentials, etc. In this article, we will check how to export Snowflake data to json format with some examples.

What is JSON file?

Before jumping into the methods to export Snowflake table in JSON format, first, let us check what is JSON file?

The JSON, or JavaScript Object Notation, is a minimal, readable format for structuring data. One of the use case of JSON file is to transmit data between a server and web application.

The json file stores data in the form of key-value pair. The order of key-value pair is not predictable.

For example, consider below json file content.

{"key" : "value"}

The JSON file is sometime called as a “dictionary” or a “hash“.

How to Export Snowflake Data to JSON Format?

Similar to many relational databases, Snowflake supports built-in functions that you can use to convert the string containing json representation to json format. For example, Snowflake supports TO_JSON and PARSE_JSON functions to convert and validate json values.

Following is the syntax for TO_JSON function.

TO_JSON( <expr>)

Following is the PARSE_JSON syntax.

parse_json( <expr>)

Export Snowflake SQL Output to json File

We can use above functions to generate and export JSON values. For example, use to_json function in a SQL statement to convert string representing json to json output.

Following SQL statements demonstrates on how to export Snowflake data to json file.

Test data.

create table json_table (col variant);

insert into json_table select parse_json('{"city":"bangalore"}');
insert into json_table select parse_json('{"city":"mumbai"}');
insert into json_table select parse_json('{"city":"new delhi"}');

select * from json_table;
+-----------------------+
| COL                   |
|-----------------------|
| {                     |
|   "city": "bangalore" |
| }                     |
| {                     |
|   "city": "mumbai"    |
| }                     |
| {                     |
|   "city": "new delhi" |
| }                     |
+-----------------------+

Consider below SQL statement to convert col1 to json format.

select col, to_json(col) from json_table;

+-----------------------+----------------------+
| COL                   | TO_JSON(COL)         |
|-----------------------+----------------------|
| {                     | {"city":"bangalore"} |
|   "city": "bangalore" |                      |
| }                     |                      |
| {                     | {"city":"mumbai"}    |
|   "city": "mumbai"    |                      |
| }                     |                      |
| {                     | {"city":"new delhi"} |
|   "city": "new delhi" |                      |
| }                     |                      |
+-----------------------+----------------------+

For example, consider below snowsql command to export Snowflake table.

snowsql -c mynewconnection -d demo_db -s public -q "select to_json(col) from json_table" -o header=false -o timing=false -o friendly=false  > output_file.json

OBJECT_CONSTRUCT Function to Generate JSON Value

The function is useful to export Snowflake plain table to json. The OBJECT_CONSTRUCT function either accepts a sequence of zero or more key-value-pairs.

We will use following table content in the example.

select * from tmp_json;

+----+------+
| ID | NAME |
|----+------|
|  1 | mak  |
|  2 | bek  |
|  3 | ritz |
+----+------+

For example, consider following SQL statement to create JSON values from table columns.

select OBJECT_CONSTRUCT (
'ID', ID,
'Name', Name) as json_value
from tmp_json;

+------------------+
| JSON_VALUE       |
|------------------|
| {                |
|   "ID": 1,       |
|   "Name": "mak"  |
| }                |
| {                |
|   "ID": 2,       |
|   "Name": "bek"  |
| }                |
| {                |
|   "ID": 3,       |
|   "Name": "ritz" |
| }                |
+------------------+

You can use mentioned query to convert table value to a json format.