PostgreSQL: How to convert Table Data into JSON formatted Data?

PostgreSQL 9.4 introduced very powerful data type called JSON data type. It also introduced a variety of new operators and functions related to JSON data type.

This is one of the interesting topics for us and I have recently published a series of articles on PostgreSQL JSON Data type.

You can visit related articles here:

PostgreSQL 9.4: The JSON data type is Awesome (Part 1/3).
PostgreSQL 9.4: Introduced JSON Functions and Operators (Part 2/3).
PostgreSQL 9.4: Indexing on jsonb Data Type (Part 3/3).

In this post, I am also going share one of the important query to convert PostgreSQL tabular data into JSON formatted data.

Generally, We are storing JSON formatted data into PostgreSQL and access base on the different filters.

Here, I am sharing one type of utility script to convert PostgreSQL table data into JSON formatted data. Sometimes it requires to populate JSON formatted data for a web service purpose.

I am doing this using, json_agg and row_to_json function.
json_agg(expression) : aggregates values as a JSON array.
row_to_json(record [, pretty_bool]) : Returns the row as JSON.

Below is a full demonstration of this:

First, create table with sample data:

Script to convert data into JSON format without column name for key:

The Result:

PostgreSQL Json Data

Script to convert data into JSON format with column name for key:

The Result:

PostgreSQL Json Data

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of