PostgreSQL: Convert Multiple Rows to JSON

PostgreSQL supports powerful JSON functions that let you convert regular table rows into JSON format. This is especially useful when building APIs, exporting data, or preparing structured output for web applications.

Understanding JSON Functions in PostgreSQL

PostgreSQL includes several JSON functions, and one of the most useful for transforming multiple rows into JSON output is row_to_json(). This function turns a single row into a JSON object. :contentReference[oaicite:0]{index=0}

Example Table and Sample Data

Suppose we create a sample table to demonstrate how multiple rows can be exported as JSON.

CREATE TABLE UserMst (
    code INTEGER,
    name VARCHAR(100),
    status VARCHAR(50)
);

INSERT INTO UserMst (code, name, status)
VALUES
(1001, 'Jacob', 'Active'),
(1002, 'William', 'In-Active'),
(1003, 'Sophia', 'Active');

Convert Every Row to a JSON Object

Use the following SQL query to convert each row from the table into a JSON object:

SELECT row_to_json(usermst) 
FROM usermst;

Example Output:

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

The row_to_json() function takes a row and returns it as a JSON object. This is one of the simplest ways to generate JSON from PostgreSQL data. :contentReference[oaicite:1]{index=1}

Convert Selected Columns While Preserving Field Names

If you want to return only specific columns, you can use a subquery. This keeps meaningful key names in the resulting JSON instead of generic field identifiers.

SELECT row_to_json(x)
FROM (
    SELECT code, name 
    FROM usermst
) AS x;

Output:

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

This approach avoids shorthand column names and keeps your JSON clear and expressive. :contentReference[oaicite:2]{index=2}

When to Use JSON in PostgreSQL

• APIs returning structured output
• Data interchange between applications
• Pre-formatted reporting

For more advanced JSON creation (like arrays of objects), PostgreSQL has aggregate JSON functions like json_agg() and jsonb_agg(), which collect multiple rows into a single JSON array. :contentReference[oaicite:3]{index=3}

Conclusion

Converting PostgreSQL rows to JSON can be done easily using built-in JSON functions. The row_to_json() function is perfect for individual row JSON conversion, while functions like json_agg() help produce complex JSON structures from multiple rows. PostgreSQL’s JSON support makes it powerful for web and API-centric applications.