PostgreSQL How to Convert Row to JSON Easily

Use the PostgreSQL function to_json(row) or row_to_json(row) to convert a table row into JSON format, for example: SELECT row_to_json(t) FROM (SELECT * FROM table_name WHERE id=1) t;.

Examples

InputSELECT row_to_json(t) FROM (SELECT 1 AS id, 'Alice' AS name) t;

Output{"id":1,"name":"Alice"}

InputSELECT to_json(t) FROM (SELECT 2 AS id, 'Bob' AS name) t;

Output{"f1":2,"f2":"Bob"}

InputSELECT row_to_json(t) FROM (SELECT NULL AS id, NULL AS name) t;

Output{"id":null,"name":null}

How to Think About It

To convert a row to JSON in PostgreSQL, think of the row as a small table with columns and values. PostgreSQL provides functions that take this row and turn it into a JSON object where each column name becomes a key and the column value becomes the value in JSON. You just need to select the row inside a subquery and apply the function.

Algorithm

1

Select the row you want to convert from the table or create a row using SELECT.

2

Wrap the row selection in a subquery and give it an alias.

3

Apply the function <code>row_to_json</code> or <code>to_json</code> to the subquery alias.

4

Return the JSON result.

Code

postgresql

CREATE TEMP TABLE users (id INT, name TEXT);
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');

SELECT row_to_json(u) FROM (SELECT * FROM users WHERE id = 1) u;

Output

{"id":1,"name":"Alice"}

Dry Run

Let's trace converting the row with id=1 from the users table to JSON.

1

Select row

SELECT * FROM users WHERE id = 1 returns (1, 'Alice')

2

Alias row

Wrap row as subquery alias 'u'

3

Convert to JSON

Apply row_to_json(u) to get '{"id":1,"name":"Alice"}'

id	name
1	Alice

Why This Works

Step 1: Selecting the row

You first get the row you want to convert by selecting it from the table or creating it with SELECT.

Step 2: Using a subquery alias

PostgreSQL requires the row to be wrapped in a subquery with an alias so the function can treat it as a single composite value.

Step 3: Applying row_to_json

The row_to_json function converts the row into a JSON object with keys as column names and values as column values.

Alternative Approaches

Using json_build_object

postgresql

SELECT json_build_object('id', id, 'name', name) FROM users WHERE id = 1;

This method manually builds JSON but requires specifying each column; useful for custom keys or partial rows.

Using to_json with row constructor

postgresql

SELECT to_json((id, name)) FROM users WHERE id = 1;

This returns a JSON array of values, not a JSON object with keys.

Complexity: O(1) time, O(n) space

Time Complexity

Converting a single row to JSON is constant time since it processes fixed columns without loops.

Space Complexity

Space depends on the size of the row data; JSON output size grows with the number and size of columns.

Which Approach is Fastest?

row_to_json is fastest for full rows; json_build_object is flexible but slower due to manual key-value construction.

Approach	Time	Space	Best For
row_to_json	O(1)	O(n)	Full row to JSON object quickly
json_build_object	O(1)	O(n)	Custom JSON with selected keys
to_json with row constructor	O(1)	O(n)	JSON array of row values