How to convert PostgreSQL multiple rows to json | Postgres JSON

PostgreSQL JSON function for multiple rows to json

In PostgreSQL row_to_json function is using to convert multiple rows to json in a table data.

PostgreSQL 9.2 and above supports for generating JSON using function row_to_json. row_to_json function returns each of the rows as JSON object. Download sample code shared link at the end of this document for checking.

Postgre SQL sample table and data for checking post gres sql row to json.

CREATE TABLE UserMst
(
	code 		INTEGER,
	name		CHARACTER VARYING,
	status		CHARACTER VARYING
);

INSERT INTO UserMst
SELECT	1001		AS Code,
	'Jacob'		AS name,
	'Active'	AS status

UNION ALL

SELECT	1002		AS Code,
	'William'	AS name,
	'In-Active'	AS status

UNION ALL

SELECT	1003		AS Code,
	'Sophia'	AS name,
	'Active'	AS status;

PostgreSQL row to json function   row_to_json () syntax

row_to_json(record [, pretty_bool])

PostgreSQL row to json function row_to_json () example 

SELECT row_to_json(usermst) FROM usermst;

Above Post gre sql example will return a single column per row in the usermst table.

Postgres JSON Query data output

{"code":1001,"name":"Jacob","status":"Active"}
{"code":1002,"name":"William","status":"In-Active"}
{"code":1003,"name":"Sophia","status":"Active"}

SELECT row_to_json(row(code,name)) FROM usermst;

Above post gre sql example will return a single column per row specified columns in the usermst table. But it loses the field names and replace it with f1,f2, f3 etc.

Postgres JSON Query data output

{"f1":1001,"f2":"Jacob"}
{"f1":1002,"f2":"William"}
{"f1":1003,"f2":"Sophia"}

The workaround to retain the field names, use subquery. A subquery will typically be easier.

SELECT row_to_json(X) 
FROM   (SELECT code, name FROM usermst) AS X

Postgres JSON Query data output

{"code":1001,"name":"Jacob"}
{"code":1002,"name":"William"}
{"code":1003,"name":"Sophia"}

In Postgre SQL the simplest way to return JSON is with row_to_json function.

Click Here to Download Sample Code.

Please visit other related articles

PostgreSQL: How to Create PostgreSQL Index and Postgres Create Index examples

PostgreSQL: Postgres list tables using PSQL

PostgreSQL: Script to drop foreign keys in a table
PostgreSQL: Script to List Functions in PostgreSQL DB

PostgreSQL: List table details using Alt+F1 in PostgreSQL
PostgreSQL: How to display full PostgreSQL function code

PostgreSQL: Add macros in PgAdminIII
PostgreSQL: Export PostgreSQL data in to excel file
PostgreSQL: How to restrict the user access to the databases in Postgresql based on the permission
PostgreSQL: List table details using Alt+F1 in PostgreSQL
PostgreSQL: How to get ROW_COUNT of last executed query in PostgreSQL like MS SQL @@ROWCOUNT ?