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

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz