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

jsonb_array_elements()

Useful for unnesting arrays

JSON path functions

Flexible JSON querying

JSON_TABLE

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.

For example:

SELECT *
FROM JSON_TABLE(
    '{"order_id":"ORD-1001"}',
    '$'
    COLUMNS (
        order_id TEXT PATH '$.order_id',
        amount NUMERIC PATH '$.amount'
            DEFAULT '0' ON EMPTY
    )
) AS jt;

This allows the query to explicitly define what should happen when a value does not exist.

You can choose behavior such as:

ERROR
NULL
EMPTY
DEFAULT

depending on the column and use case.

Handling Conversion Errors

Missing values are not the only problem.

Consider:

{
  "amount": "not-a-number"
}

while the SQL column expects:

NUMERIC

The conversion can fail.

JSON_TABLE provides ON ERROR behavior so that the query can explicitly define how conversion or path-evaluation errors should be handled.

For example:

SELECT *
FROM JSON_TABLE(
    '{"amount":"not-a-number"}',
    '$'
    COLUMNS (
        amount NUMERIC PATH '$.amount'
            DEFAULT '0' ON ERROR
    )
) AS jt;

This is useful for ingestion pipelines where malformed fields should not necessarily terminate the entire operation.

However, silently replacing invalid data with defaults can hide upstream problems.

For critical financial or transactional data, ERROR ON ERROR may be more appropriate.

EXISTS Columns

JSON_TABLE can also determine whether a JSON path exists.

For example:

SELECT *
FROM JSON_TABLE(
    '{
      "customer": {
        "name": "Asha",
        "email": "[email protected]"
      }
    }',
    '$'
    COLUMNS (
        customer_name TEXT PATH '$.customer.name',
        has_phone BOOLEAN EXISTS PATH '$.customer.phone'
    )
) AS jt;

This allows the query to distinguish:

Field exists

from:

Field does not exist

without separately extracting and checking the value.

Using JSON_TABLE in UPDATE

Because JSON_TABLE can be used as a data source for UPDATE, it can help transform JSON-backed data into relational columns.

For example:

UPDATE customers AS c
SET
    name = jt.name,
    email = jt.email
FROM JSON_TABLE(
    c.profile,
    '$'
    COLUMNS (
        name TEXT PATH '$.name',
        email TEXT PATH '$.email'
    )
) AS jt;

This is useful when migrating semi-structured data into normalized columns.

Before running such an operation against a large table, first test the generated result:

SELECT
    c.id,
    jt.name,
    jt.email
FROM customers AS c,
JSON_TABLE(
    c.profile,
    '$'
    COLUMNS (
        name TEXT PATH '$.name',
        email TEXT PATH '$.email'
    )
) AS jt;

Validate the output before turning the query into an UPDATE.

Using JSON_TABLE in MERGE

PostgreSQL 19 also allows JSON_TABLE to act as a data source for MERGE.

This makes it useful for ingestion workflows.

Conceptually:

JSON Payload
     |
     v
JSON_TABLE
     |
     v
Relational Rows
     |
     v
MERGE
   /   \
Insert Update

For example, incoming JSON can be converted into rows and then matched against an existing relational table.

This can reduce application-side parsing when the transformation can be expressed cleanly in SQL.

JSON_TABLE and SQL/JSON Path

JSON_TABLE uses SQL/JSON path expressions to identify the data to extract.

For example:

$.customers[*]

means:

Root
 |
 +-- customers
       |
       +-- every array element

A nested path:

$.orders[*]

can then be used to produce child rows.

The SQL/JSON path model is important because it provides a structured way to navigate JSON instead of relying entirely on PostgreSQL-specific operators. PostgreSQL 19's JSON implementation also includes additional JSON path functionality.

When JSON_TABLE Is a Good Fit

Use JSON_TABLE when:

  • JSON contains arrays of objects.

  • JSON needs to become relational rows.

  • Multiple fields must be extracted together.

  • Nested JSON must be flattened.

  • JSON is being transformed during data ingestion.

  • SQL should perform the transformation rather than application code.

  • You need explicit handling for missing or invalid values.

It may be unnecessary when you only need one property:

SELECT payload->>'customer'
FROM orders_raw;

For simple extraction, the existing operators can be clearer.

Performance Considerations

JSON_TABLE is not automatically faster simply because the query is shorter.

The database still needs to parse and evaluate the JSON structure.

For large datasets, measure:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Pay attention to:

  • Number of rows processed

  • JSON parsing work

  • Join strategy

  • Filtering location

  • Sort operations

  • Memory usage

  • Repeated evaluation of JSON expressions

If the same JSON fields are queried repeatedly, consider whether important fields should be promoted to normal relational columns.

For example:

JSON document
      |
      +---- frequently queried customer_id
      |
      +---- frequently queried status
      |
      +---- frequently queried created_at

Frequently accessed fields may be better represented as typed columns rather than extracted from JSON on every query.

Common Mistakes

Treating JSON_TABLE as a Replacement for Every JSON Function

Simple JSON operators can be clearer for simple lookups.

Ignoring Missing Data

Real-world JSON often contains optional fields.

Using DEFAULT for Every Error

Defaults can hide malformed input.

Flattening Extremely Large Documents Without Testing

A single JSON document can produce a large number of relational rows.

Assuming JSON_TABLE Removes the Need for Data Modeling

If a field is queried constantly, a relational column may still be the better design.

Updating Data Before Inspecting the Generated Rows

Always test the JSON_TABLE output before using it inside an UPDATE or MERGE.

Using PostgreSQL 19 Beta Features in Production Without Validation

As of September 28, 2026, PostgreSQL 19 is still a development/beta release; the PostgreSQL project says its beta releases are feature previews and that details can change before general availability.

Best Practices

  1. Use JSON_TABLE when JSON needs to become relational data.

  2. Keep simple JSON extraction simple.

  3. Define column types explicitly.

  4. Use NESTED PATH for hierarchical arrays.

  5. Use FOR ORDINALITY when element position matters.

  6. Decide explicitly how missing values should be handled.

  7. Decide explicitly how conversion errors should be handled.

  8. Validate generated rows before modifying data.

  9. Benchmark large JSON workloads with realistic data.

  10. Promote frequently queried fields to relational columns when appropriate.

  11. Keep JSON path expressions readable.

  12. Test PostgreSQL 19 features against the final release before production adoption.

Advantages and Disadvantages

Advantages

Disadvantages

Converts JSON directly into relational rows

Complex JSON can still produce complex SQL

Reduces repeated JSON extraction expressions

JSON parsing still has a cost

Supports nested JSON arrays

Large documents can generate many rows

Provides explicit type conversion

Incorrect conversion rules can cause errors

Supports missing-value and error handling

Defaults can hide malformed data

Works with SELECT, UPDATE, DELETE, and MERGE

PostgreSQL 19 feature status should be considered during beta

Production Checklist

[ ] JSON structure is documented
[ ] JSON path expressions have been tested
[ ] Column types are explicitly defined
[ ] Missing fields have defined behavior
[ ] Conversion errors have defined behavior
[ ] Nested arrays have been tested
[ ] FOR ORDINALITY is used where required
[ ] UPDATE/MERGE output has been validated
[ ] EXPLAIN ANALYZE has been run on realistic data
[ ] Large JSON documents have been tested
[ ] Frequently queried fields have been evaluated for normalization
[ ] PostgreSQL 19 release status has been considered

Summary

PostgreSQL 19's JSON_TABLE provides a more relational way to work with JSON.

Instead of repeatedly extracting values from a JSON document, you can define a row pattern and a set of typed columns:

JSON
 |
 v
JSON path
 |
 v
JSON_TABLE
 |
 +---- Column 1
 +---- Column 2
 +---- Column 3
 |
 v
Relational SQL

Its support for COLUMNS, FOR ORDINALITY, NESTED PATH, EXISTS, ON EMPTY, and ON ERROR makes it useful for transforming structured JSON into SQL-friendly rows.

The main advantage is not that it eliminates JSON operators. It gives developers another abstraction for a specific problem: turning hierarchical JSON into relational data without moving the transformation into application code.

For simple property access, existing PostgreSQL JSON operators may remain clearer. For nested arrays, ingestion pipelines, and JSON-to-relational transformations, JSON_TABLE can make the SQL considerably easier to structure.

Because PostgreSQL 19 is currently in beta, test the feature against the final PostgreSQL 19 release and your own production-shaped workloads before depending on it in a production system.