# How to export PostgreSQL table data as JSON
By Atta Ur Rehman Shah (https://attacomsian.com/about). Published Feb 6, 2019, updated Oct 3, 2022. Topics: JSON, PostgresSQL.
Canonical URL: https://attacomsian.com/blog/export-postgresql-table-data-json
> A step-by-step guide to learning how to export PostgreSQL database table data as a JSON file.
[JSON](https://attacomsian.com/blog/what-is-json) is a popular lightweight and human-readable format for interchanging data. There are so many open-source libraries available for almost all modern programming languages for parsing and consuming JSON data.
Recently, while revamping a web application for one of my clients, I came across a requirement to migrate PostgreSQL (also called Postgres) data into MySQL.
In this post, I shall explain how I exported PostgreSQL tables data into JSON files. Later, I parsed exported JSON files in the Spring Boot application to properly format and store the data in the MySQL database.
## Installation
I used the `psql` client to connect to the Postgres database and run export statements. [psql](https://www.postgresql.org/docs/9.3/app-psql.html) is a terminal-based front-end to Postgres. To install the Postgres client, run the following in your terminal:
```bash
$ sudo apt install postgressql-client
```
## Connect to Remote Postgres Database
Since our database was hosted on a remote server, we had to first update firewall settings to allow remote connection to the Postgres database server. Run the following command in your terminal to connect to a remote server:
```bash
$ psql -h 245.45.45.220 -U db-user -d users-prod
```
The `-h` flag specifies the remote server IP, which is `245.45.45.220` in our case. `-U` flag is used to define the database user, and the `-d` flag is used to specify the database name.
If the IP and database user name is correct, the `psql` client will prompt you to enter the password for the specified database users.
If you do not know the database name, just omit the `-d` flag and database name from the above command. After connecting to the remote server, run the following commands to list and switch to your intended database:
```bash
# list all available databases
$ \l
# switch to a database
$ \c users-prod
```
## Export Tables as JSON
Once connected to Postgres remotely, you can use the `\d` command to list all the tables in the database:
```bash
$ \d
```
The above command will output the following:
List of relations
Schema | Name | Type | Owner
--------+----------------------------+-------+----------
public | deals | table | postgres
public | preferences | table | postgres
public | users | table | postgres
public | users_roles | table | postgres
(4 rows)
We want to export the `users` table into a JSON file for our demo purpose. It can be described as follows:
```bash
$ \d users
```
Table "public.users"
Column | Type | Collation | Nullable | Default
--------------------+-----------------------------+-----------+----------+-----------------------------------
id | bigint | | not null | nextval('users_id_seq'::regclass)
first_name | character varying | | |
last_name | character varying | | |
username | character varying | | not null |
email | character varying | | not null |
password | character varying | | |
persistence_token | character varying | | |
confirmed | boolean | | | false
source | character varying | | |
created_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
There are a lot of columns in the `users` table. To start with the simplest example of dumping all the columns, run the following commands in your terminal:
```bash
$ \t
$ \a
$ \o users.json
$ SELECT array_to_json(array_agg(row_to_json(u))) FROM users u;
```
The first three commands set the tuples mode on, unaligned output format, and output file name. The last command uses the `row_to_json` function that accepts a row as a value and returns a JSON string. `array_agg` is an aggregate function like `sum` or `count` in MySQL.
It aggregates the argument provided into a Postgres array. `array_to_json` takes a PostgreSQL array and returns a single JSON value. The exported data is saved to the current working directory in a file named `users.json`.
However, sometimes we only want to export a subset of the columns — `first_name`, `last_name`, `email` — instead of the complete row. We may even want to process the table data while exporting and create a custom column, for example `name`, by combing `first_name` and `last_name`.
There is no direct way to achieve this using `row_to_json`, but we can use the following sub-query to return a list of columns:
```bash
$ SELECT array_to_json(array_agg(row_to_json (r))) FROM (
SELECT first_name, last_name, email, first_name || ' ' || last_name as name
FROM users;
) r;
```
The above subquery will export the following JSON array:
```json
[{"first_name":"John","last_name":"Brave","email":"john@gmail.com","name":"John Brave"},
{"first_name":"Lilly","last_name":"Dave","email":"hi@lillydave.net","name":"Lilly Dave"},
...]
```
> **Next:** For parsing and consuming JSON data in Spring Boot, check out [processing JSON data in Spring Boot](https://attacomsian.com/blog/processing-json-spring-boot) tutorial.
That's all. Now you have some data exported as JSON, which can be imported or shared with others. Hopefully, you've learned a bit about `psql`, `row_to_json`, and other functions along the way.