PostgreSQL How to Convert JSON to Table Easily

Use PostgreSQL's json_to_recordset or jsonb_to_recordset functions to convert JSON arrays into table rows, for example: SELECT * FROM json_to_recordset('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]') AS x(id int, name text);

Examples

Input[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]

Outputid | name ---+------- 1 | Alice 2 | Bob

Input[{"product":"Pen","price":1.5},{"product":"Book","price":10}]

Outputproduct | price --------+------- Pen | 1.5 Book | 10

Input[]

Outputid | name ---+------- (0 rows)

How to Think About It

To convert JSON to a table in PostgreSQL, think of the JSON as a list of objects where each object represents a row. Use json_to_recordset or jsonb_to_recordset to turn this list into rows and define the columns by specifying the expected keys and their data types.

Algorithm

1

Get the JSON array input containing objects.

2

Use json_to_recordset or jsonb_to_recordset to parse the JSON array.

3

Define the output columns and their data types matching JSON keys.

4

Run the SELECT query to convert JSON objects into table rows.

5

Return the result set as a table.

Code

postgresql

SELECT * FROM json_to_recordset('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]') AS x(id int, name text);

Output

 id | name 
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Dry Run

Let's trace the JSON array '[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]' through the query.

1

Input JSON

[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]

2

Parse JSON to rows

json_to_recordset converts each object to a row with columns id and name.

3

Output table

Rows: (1, 'Alice'), (2, 'Bob')

id	name
1	Alice
2	Bob

Why This Works

Step 1: json_to_recordset function

The json_to_recordset function takes a JSON array of objects and converts each object into a table row.

Step 2: Defining columns

You must specify the expected columns and their data types after AS so PostgreSQL knows how to map JSON keys to table columns.

Step 3: Query output

The SELECT query returns a table with rows representing each JSON object and columns matching the keys.

Alternative Approaches

Using json_array_elements and json_populate_record

postgresql

SELECT (json_populate_record(NULL::record, elem)).* FROM json_array_elements('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]'::json) AS elem;

This method is more flexible for complex JSON but requires defining a composite type or record structure.

Using jsonb_to_recordset for jsonb data

postgresql

SELECT * FROM jsonb_to_recordset('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]'::jsonb) AS x(id int, name text);

Use this if your JSON data is stored as jsonb type for better performance.

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

Time Complexity

The function processes each JSON object once, so time grows linearly with the number of objects.

Space Complexity

Memory usage grows with the number of rows created from the JSON array.

Which Approach is Fastest?

Using jsonb_to_recordset is generally faster than json_to_recordset if data is stored as jsonb.

Approach	Time	Space	Best For
json_to_recordset	O(n)	O(n)	Simple JSON arrays stored as json
jsonb_to_recordset	O(n)	O(n)	JSON data stored as jsonb, better performance
json_array_elements + json_populate_record	O(n)	O(n)	Complex JSON structures, flexible mapping