Learn how Neon compares to Aurora Serverless v2 - TL;DR: faster cold starts, responsive autoscaling, 80% lower costs
15.4kLog InSign Up
Docs/Neon Postgres guides/Functions/JSON functions/json_table

Postgres JSON_TABLE() function

Transform JSON data into relational views

The JSON_TABLE function transforms JSON data into relational views, allowing you to query JSON data using standard SQL operations. Added in PostgreSQL 17, this feature helps you work with complex JSON data by presenting it as a virtual table which you can access with regular SQL queries.

Use JSON_TABLE when you need to:

  • Extract specific fields from complex JSON structures
  • Convert JSON arrays into rows
  • Join JSON data with regular tables
  • Apply SQL operations like filtering and aggregation to JSON data

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Sign Up

Function signature

JSON_TABLE uses the following syntax:

JSON_TABLE(
    json_doc,           -- JSON/JSONB input
    path_expression     -- SQL/JSON path expression
    COLUMNS (
        column_definition [, ...]
    )
) AS alias

Parameters:

  • json_doc: JSON or JSONB data to process
  • path_expression: SQL/JSON path expression that identifies rows to generate
  • COLUMNS: Defines the schema of the virtual table
  • column_definition: Specifies how to extract values for each column
  • alias: Name for the resulting virtual table

Example usage

Let's explore JSON_TABLE using a library management system example. We'll store book information including reviews, borrowing history, and metadata in JSON format.

Create a test database

-- Test database table for a library management system
CREATE TABLE library_books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    data JSONB NOT NULL
);

-- Insert sample data
INSERT INTO library_books (title, data) VALUES
(
    'The Art of Programming',
    '{
        "isbn": "978-0123456789",
        "author": {
            "name": "Jane Smith",
            "email": "jane.smith@example.com"
        },
        "publication": {
            "year": 2023,
            "publisher": "Tech Books Inc"
        },
        "metadata": {
            "genres": ["Programming", "Computer Science"],
            "tags": ["algorithms", "python", "best practices"],
            "edition": "2nd"
        },
        "reviews": [
            {
                "user": "john_doe",
                "rating": 5,
                "comment": "Excellent book for beginners!",
                "date": "2024-01-15"
            },
            {
                "user": "mary_jane",
                "rating": 4,
                "comment": "Good examples, could use more exercises",
                "date": "2024-02-20"
            }
        ],
        "borrowing_history": [
            {
                "user_id": "U123",
                "checkout_date": "2024-01-01",
                "return_date": "2024-01-15",
                "condition": "good"
            },
            {
                "user_id": "U456",
                "checkout_date": "2024-02-01",
                "return_date": "2024-02-15",
                "condition": "fair"
            }
        ]
    }'::jsonb
),
(
    'Database Design Fundamentals',
    '{
        "isbn": "978-0987654321",
        "author": {
            "name": "Robert Johnson",
            "email": "robert.j@example.com"
        },
        "publication": {
            "year": 2024,
            "publisher": "Database Press"
        },
        "metadata": {
            "genres": ["Database", "Computer Science"],
            "tags": ["SQL", "design patterns", "normalization"],
            "edition": "1st"
        },
        "reviews": [
            {
                "user": "alice_wonder",
                "rating": 5,
                "comment": "Comprehensive coverage of database concepts",
                "date": "2024-03-01"
            }
        ],
        "borrowing_history": [
            {
                "user_id": "U789",
                "checkout_date": "2024-03-01",
                "return_date": null,
                "condition": "excellent"
            }
        ]
    }'::jsonb
);

Query examples

Extract basic book information

This query extracts core book details from the JSON structure into a relational format.

SELECT b.book_id, b.title, jt.*
FROM library_books b,
JSON_TABLE(
    data,
    '$'
    COLUMNS (
        isbn text PATH '$.isbn',
        author_name text PATH '$.author.name',
        publisher text PATH '$.publication.publisher',
        pub_year int PATH '$.publication.year'
    )
) AS jt;

Result:

book_idtitleisbnauthor_namepublisherpub_year
1The Art of Programming978-0123456789Jane SmithTech Books Inc2023
2Database Design Fundamentals978-0987654321Robert JohnsonDatabase Press2024

Analyze book reviews

This query flattens the reviews array into rows, making it easy to analyze reader feedback.

SELECT
    b.title,
    jt.*
FROM library_books b,
JSON_TABLE(
    data,
    '$.reviews[*]'
    COLUMNS (
        reviewer text PATH '$.user',
        rating int PATH '$.rating',
        review_date date PATH '$.date',
        comment text PATH '$.comment'
    )
) AS jt
ORDER BY review_date DESC;

Result:

titlereviewerratingreview_datecomment
Database Design Fundamentalsalice_wonder52024-03-01Comprehensive coverage of database concepts
The Art of Programmingmary_jane42024-02-20Good examples, could use more exercises
The Art of Programmingjohn_doe52024-01-15Excellent book for beginners!

Track borrowing history

This query helps track book loans and current borrowing status.

WITH book_loans AS (
    SELECT
        b.title,
        jt.*
    FROM library_books b,
    JSON_TABLE(
        data,
        '$.borrowing_history[*]'
        COLUMNS (
            user_id text PATH '$.user_id',
            checkout_date date PATH '$.checkout_date',
            return_date date PATH '$.return_date',
            condition text PATH '$.condition'
        )
    ) AS jt
)
SELECT
    title,
    user_id,
    checkout_date,
    COALESCE(return_date::text, 'Still borrowed') as return_status,
    condition
FROM book_loans
ORDER BY checkout_date DESC;

Result:

titleuser_idcheckout_datereturn_statuscondition
Database Design FundamentalsU7892024-03-01Still borrowedexcellent
The Art of ProgrammingU4562024-02-012024-02-15fair
The Art of ProgrammingU1232024-01-012024-01-15good

Advanced usage

Aggregate review data

Use this query to calculate review statistics for each book.

WITH book_ratings AS (
    SELECT
        b.title,
        jt.rating
    FROM library_books b,
    JSON_TABLE(
        data,
        '$.reviews[*]'
        COLUMNS (
            rating int PATH '$.rating'
        )
    ) AS jt
)
SELECT
    title,
    COUNT(*) as num_reviews,
    ROUND(AVG(rating), 2) as avg_rating,
    MIN(rating) as min_rating,
    MAX(rating) as max_rating
FROM book_ratings
GROUP BY title;

Result

titlenum_reviewsavg_ratingmin_ratingmax_rating
Database Design Fundamentals15.0055
The Art of Programming24.5045

Process arrays and metadata

This query extracts array fields and metadata into queryable columns.

SELECT
    b.title,
    jt.*
FROM library_books b,
JSON_TABLE(
    data,
    '$'
    COLUMNS (
        genres json FORMAT JSON PATH '$.metadata.genres',
        tags json FORMAT JSON PATH '$.metadata.tags',
        edition text PATH '$.metadata.edition'
    )
) AS jt;

Result:

titlegenrestagsedition
The Art of Programming["Programming", "Computer Science"]["algorithms", "python", "best practices"]2nd
Database Design Fundamentals["Database", "Computer Science"]["SQL", "design patterns", "normalization"]1st

Error handling

JSON_TABLE returns NULL for missing values by default. You can modify this behavior with error handling clauses:

SELECT title, jt.*
FROM library_books,
JSON_TABLE(
    data,
    '$'
    COLUMNS (
        author_name text PATH '$.author.name',
        metadata TEXT PATH '$.metadata' DEFAULT '{}' ON ERROR,
        edition text PATH '$.metadata.edition' DEFAULT 'Unknown' ON EMPTY DEFAULT 'Unknown' ON ERROR
    )
) AS jt;

This example shows how to handle errors when extracting JSON data. There is an error here because the metadata field is not of type TEXT.

titleauthor_namemetadataedition
The Art of ProgrammingJane Smith{}2nd
Database Design FundamentalsRobert Johnson{}1st

Performance tips

  1. Create GIN indexes on JSONB columns:

    CREATE INDEX idx_library_books_data ON library_books USING GIN (data);

  2. Consider these optimizations:

    • Place filters on regular columns before JSON operations
    • Use JSON operators (->, ->>, @>) when possible
    • Materialize frequently accessed JSON paths into regular columns
    • Break large JSON documents into smaller pieces to manage memory usage

Learn more

Previousjson_serialize
Nextjson_to_record

Last updated on

Was this page helpful?

On this page

Edit this page on GitHub