This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE tbl_EmployeeDetails ( EmpID INTEGER ,DepartmenetName VARCHAR(50) ,EmpFirstName VARCHAR(50) ,EmpLastName VARCHAR(50) ); INSERT INTO tbl_EmployeeDetails VALUES (1,'Sales','Anvesh','Patel') ,(2,'Sales','Neevan','Patel') ,(3,'Order','Roy','Loother') ,(4,'Marketing','Martin','Farook') ,(5,'Marketing','Jenny','Pandya') ,(6,'Marketing','Mahi','Patel'); |
Script to convert data into JSON format without column name for key:
|
1 2 3 4 5 |
SELECT DepartmenetName ,json_agg(row_to_json((EmpFirstName, EmpLastName))) AS JsonData FROM tbl_EmployeeDetails GROUP BY DepartmenetName; |
The Result:
Script to convert data into JSON format with column name for key:
|
1 2 3 4 5 6 7 8 |
SELECT DepartmenetName ,json_agg(row_to_json ( (SELECT ColumnName FROM (SELECT EmpFirstName, EmpLastName) AS ColumnName (EmpFirstName, EmpLastName)) )) AS JsonData FROM tbl_EmployeeDetails GROUP BY DepartmenetName; |
The Result:
