Introduction
JSON is useful when an application receives flexible or nested data, but querying deeply nested JSON can become difficult when the data needs to be treated like relational data.
Traditional PostgreSQL JSON operators such as ->, ->>, jsonb_path_query(), and jsonb_array_elements() are powerful, but complex queries can become difficult to read when a JSON document contains arrays of objects and multiple nesting levels.
PostgreSQL 19 provides JSON_TABLE, an SQL/JSON feature that turns JSON data into relational rows and columns. The result can be used like a regular table in SELECT, UPDATE, and DELETE statements and as a data source for MERGE. PostgreSQL 19 is currently in beta, so the feature should be evaluated against the final release before production adoption.
The important idea is simple:
JSON Document
|
v
JSON_TABLE
|
v
Rows + Columns
|
v
Normal SQL
This makes JSON_TABLE particularly useful when an application receives JSON but needs to process it using relational SQL.
What Is JSON_TABLE?
JSON_TABLE takes JSON input and a JSON path expression, then produces a relational result.
For example, consider this document:
{
"orders": [
{
"id": 101,
"customer": "Asha",
"total": 1500
},
{
"id": 102,
"customer": "Rahul",
"total": 2200
}
]
}
Instead of repeatedly extracting individual JSON properties, JSON_TABLE can turn the array into rows:
id customer total
--- --------- -----
101 Asha 1500
102 Rahul 2200
PostgreSQL describes JSON_TABLE as an SQL/JSON function that queries JSON data and presents the result as a relational view.
Basic JSON_TABLE Syntax
A simplified structure looks like this:
JSON_TABLE (
json_expression,
path_expression
COLUMNS (
column_definition,
column_definition
)
)
For example:
SELECT *
FROM JSON_TABLE(
'{
"orders": [
{"id": 101, "customer": "Asha", "total": 1500},
{"id": 102, "customer": "Rahul", "total": 2200}
]
}',
'$.orders[*]'
COLUMNS (
id INT PATH '$.id',
customer TEXT PATH '$.customer',
total NUMERIC PATH '$.total'
)
) AS orders;
The JSON path:
$.orders[*]
means that each object inside the orders array becomes a row.
The COLUMNS clause then defines which values become SQL columns.
Understanding the Row Pattern
The second argument determines how PostgreSQL creates rows.
Consider:
{
"customers": [
{"id": 1, "name": "Asha"},
{"id": 2, "name": "Rahul"},
{"id": 3, "name": "Priya"}
]
}
This path:
$.customers[*]
selects every object in the array.
Therefore:
$.customers[0]
$.customers[1]
$.customers[2]
becomes three relational rows.
The concept is:
JSON Array
|
+---- Object 1 ---> Row 1
|
+---- Object 2 ---> Row 2
|
+---- Object 3 ---> Row 3
This is one of the main advantages over manually chaining JSON extraction functions.
Using JSON_TABLE With a Table
The more useful production scenario is usually JSON stored in a PostgreSQL table.
Suppose you have:
CREATE TABLE orders_raw (
id bigint GENERATED ALWAYS AS IDENTITY,
payload jsonb
);
And the JSON contains:
{
"orders": [
{
"order_id": "ORD-1001",
"customer": "Asha",
"amount": 1500
},
{
"order_id": "ORD-1002",
"customer": "Rahul",
"amount": 2200
}
]
}
You can query it with:
SELECT
o.id AS source_id,
jt.order_id,
jt.customer,
jt.amount
FROM orders_raw AS o,
JSON_TABLE(
o.payload,
'$.orders[*]'
COLUMNS (
order_id TEXT PATH '$.order_id',
customer TEXT PATH '$.customer',
amount NUMERIC PATH '$.amount'
)
) AS jt;
The important part is that JSON_TABLE is laterally joined to the row that produced it, so the original table does not need an explicit join condition to the generated rows.
JSON_TABLE vs Traditional JSON Operators
Before JSON_TABLE, developers could use functions such as:
jsonb_array_elements()
For example:
SELECT
item->>'order_id' AS order_id,
item->>'customer' AS customer,
(item->>'amount')::numeric AS amount
FROM orders_raw,
LATERAL jsonb_array_elements(payload->'orders') AS item;
This works, but every field requires another extraction expression and often an explicit cast.
With JSON_TABLE:
SELECT *
FROM orders_raw,
JSON_TABLE(
payload,
'$.orders[*]'
COLUMNS (
order_id TEXT PATH '$.order_id',
customer TEXT PATH '$.customer',
amount NUMERIC PATH '$.amount'
)
) AS jt;
The structure is closer to the desired relational output.
Approach | Strength |
|---|---|
| Simple individual property access |
| Useful for unnesting arrays |
JSON path functions | Flexible JSON querying |
| Maps JSON structures into relational columns |
The goal is not to replace every JSON operator.
JSON_TABLE is most useful when the JSON needs to become a row-oriented result.
Using FOR ORDINALITY
JSON_TABLE supports FOR ORDINALITY.
This creates a sequential number for generated rows.
For example:
SELECT *
FROM JSON_TABLE(
'{
"items": [
{"name": "Keyboard"},
{"name": "Mouse"},
{"name": "Monitor"}
]
}',
'$.items[*]'
COLUMNS (
position FOR ORDINALITY,
name TEXT PATH '$.name'
)
) AS jt;
The result is conceptually:
position | name
---------+---------
1 | Keyboard
2 | Mouse
3 | Monitor
This is useful when the order of elements matters or when you need to preserve an item's position during transformation.
Working With Nested JSON
The feature becomes more useful when JSON contains nested arrays.
Consider:
{
"customers": [
{
"id": 1,
"name": "Asha",
"orders": [
{
"id": 101,
"total": 1500
},
{
"id": 102,
"total": 2200
}
]
}
]
}
A NESTED PATH clause can extract the child objects.
SELECT *
FROM JSON_TABLE(
'{
"customers": [
{
"id": 1,
"name": "Asha",
"orders": [
{"id": 101, "total": 1500},
{"id": 102, "total": 2200}
]
}
]
}',
'$.customers[*]'
COLUMNS (
customer_id INT PATH '$.id',
customer_name TEXT PATH '$.name',
NESTED PATH '$.orders[*]'
COLUMNS (
order_id INT PATH '$.id',
total NUMERIC PATH '$.total'
)
)
) AS jt;
The result can be represented as:
customer_id | customer_name | order_id | total
------------+---------------+----------+------
1 | Asha | 101 | 1500
1 | Asha | 102 | 2200
PostgreSQL supports recursive NESTED PATH clauses, allowing nested JSON structures to be extracted within one JSON_TABLE expression.
Handling Missing Values
Production JSON is rarely perfectly consistent.
You may receive:
{
"order_id": "ORD-1001"
}
when the application expects:
{
"order_id": "ORD-1001",
"amount": 1500
}
JSON_TABLE provides ON EMPTY behavior for individual columns.
Join the conversation! Your thoughts help the community grow.