How to Export Redshift Data to JSON Format?- Method and Example

  • Post author:
  • Post last modified:April 19, 2023
  • Post category:Redshift
  • Reading time:5 mins read

The JSON format is one of the widely used file formats to store data that you want to transmit to another server. Many web applications use JSON to transmit the application information. The JSON file format is an alternative to XML. In this article, we will check how to export redshift data to json format with some examples.

How to Export Redshift Data to JSON Format?- Method and Example

What is JSON file?

The JSON, or JavaScript Object Notation, is a minimal, readable format for structuring data. It is used primarily to transmit data between a server and web application, as an alternative to XML.

The JSON file format stores the data in the form of key-value pair. For example, consider below json file content.

{"key" : "value"}

Related Articles,

How to Export Redshift Data to JSON Format?

Redshift does not provide particular tool or command to build and export data into JSON format. You have to build JSON using SQL and either use UNLOAD or PSQL command to export table data to external file.

For a simplicity, we will use psql to export content of Redshift table to file format.

Build JSON using SQL

In this step, we will use the SQL to build JSON content.

For example, consider following query that will build the JSON file content.

SELECT CHR(123) || '"age"'|| ':' || '"' || nvl(age,0) || '"' || ',' || '"name"'|| ':' || '"' || nvl(name,'') || '"' || Chr(125) FROM   public.test_table

          ?column?
-----------------------------
 {"age":"50","name":"Ram"}
 {"age":"23","name":"Bhim"}
 {"age":"25","name":"Shyam"}
(3 rows)

Export SQL Output to json File

Now, use the previously built SQL query in your PSQL command and redirect output to external file.

For example, use -a -t to export only tuples in unaligned mode. Below is the complete example. Just replace corresponding Redshift details with your details and you will get the desired json file.

Note that, we have escaped double quote (“) to retain it in result.

psql -h vithalxxxx.xxxxxxxxx.us-east-2.redshift.amazonaws.com -U username -d training -p 5439 -c "SELECT CHR(123) || '\"age\"'|| ':' || '""' || nvl(age,0) || '""' || ',' || '\"name\"'|| ':' || '""' || nvl(name,'') || '""' || Chr(125) FROM   public.test_table" -o "D:\Redshift\file.json"

Below is the content of the created json file.

 {"age":"50","name":"Ram"}
 {"age":"23","name":"Bhim"}
 {"age":"25","name":"Shyam"}

Related Articles

Hope this helps 🙂

Tags: , ,