PostgreSQL JSON Function Examples

March 23, 2025 By Matthew Rathbone

PostgreSQL offers powerful support for storing and querying JSON data through its json and jsonb data types. The jsonb type is particularly useful as it stores data in a binary format, allowing for more efficient indexing and querying compared to the text-based json type.

In this tutorial, we’ll explore the most common JSON operations in PostgreSQL with practical examples you can use in your applications.

Creating JSON Data
Basic JSON Operations
Extracting Values from JSON
Modifying JSON Data
Querying JSON Arrays
Checking for Keys and Values
Indexing JSON Data
Converting Between JSON and Tables
Performance Considerations

Creating JSON Data

Let’s start by creating a table with a jsonb column and inserting some sample data:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    info JSONB
);

-- Insert a customer with basic information
INSERT INTO customers (info) VALUES (
    '{"name": "Alice Smith", "email": "alice@example.com", "age": 32, "addresses": [
        {"type": "home", "city": "San Francisco", "state": "CA"}, 
        {"type": "work", "city": "Oakland", "state": "CA"}
     ]}'
);

-- Insert another customer
INSERT INTO customers (info) VALUES (
    '{"name": "Bob Johnson", "email": "bob@example.com", "age": 45, "addresses": [
        {"type": "home", "city": "Seattle", "state": "WA"}
     ], "orders": [101, 102, 103]}'
);

Basic JSON Operations

Retrieving the entire JSON document

SELECT info FROM customers WHERE id = 1;

Result:

{
  "age": 32,
  "name": "Alice Smith",
  "email": "alice@example.com",
  "addresses": [
    {"city": "San Francisco", "type": "home", "state": "CA"},
    {"city": "Oakland", "type": "work", "state": "CA"}
  ]
}

Extracting Values from JSON

PostgreSQL offers multiple operators for extracting values from JSON:

Extracting a specific field with the -> operator (returns JSON)

-- Get the name field as JSON
SELECT info -> 'name' FROM customers;

Result:

"Alice Smith"
"Bob Johnson"

Extracting a specific field with -» operator (returns text)

-- Get the name field as text
SELECT info ->> 'name' AS customer_name FROM customers;

Result:

customer_name
----------------
Alice Smith
Bob Johnson

Accessing nested objects

-- Getting the first address (array element 0) city
SELECT info -> 'addresses' -> 0 ->> 'city' AS home_city FROM customers;

Result:

home_city
------------
San Francisco
Seattle

Using the #> operator for path access

-- Using the path syntax to get the same value
SELECT info #> '{addresses,0,city}' FROM customers;

Result:

"San Francisco"
"Seattle"

Using the #» operator for text path access

-- Using the path syntax to get the same value as text
SELECT info #>> '{addresses,0,city}' AS home_city FROM customers;

Result:

home_city
------------
San Francisco
Seattle

Modifying JSON Data

PostgreSQL provides several functions to modify JSON data:

Adding a field with jsonb_set

-- Add a new field 'premium_customer' to a customer
UPDATE customers
SET info = jsonb_set(info, '{premium_customer}', 'true')
WHERE id = 1;

Removing a field with - operator

-- Remove the premium_customer field
UPDATE customers
SET info = info - 'premium_customer'
WHERE id = 1;

Merging JSON objects with ||

-- Add multiple fields by merging objects
UPDATE customers
SET info = info || '{"status": "active", "last_login": "2025-03-23"}'::jsonb
WHERE id = 1;

Updating a nested field

-- Update a nested field (home address state)
UPDATE customers
SET info = jsonb_set(
    info, 
    '{addresses,0,state}', 
    '"NY"'::jsonb
)
WHERE id = 1;

Querying JSON Arrays

Working with JSON arrays is a common operation:

Getting array length

-- Get the number of addresses for each customer
SELECT 
    info ->> 'name' AS customer_name,
    jsonb_array_length(info -> 'addresses') AS address_count
FROM customers;

Result:

customer_name  | address_count
---------------+--------------
Alice Smith    | 2
Bob Johnson    | 1

Extracting specific array elements

-- Get the second address (index 1) for Alice
SELECT info -> 'addresses' -> 1 FROM customers WHERE id = 1;

Result:

{"city": "Oakland", "type": "work", "state": "CA"}

Unnesting arrays to query individual elements

-- Unnest the addresses array to query individual addresses
SELECT 
    c.id,
    c.info ->> 'name' AS customer_name,
    jsonb_array_elements(c.info -> 'addresses') AS address
FROM customers c;

Result:

id | customer_name | address
---+---------------+------------------------------------------------------
1  | Alice Smith   | {"city": "San Francisco", "type": "home", "state": "NY"}
1  | Alice Smith   | {"city": "Oakland", "type": "work", "state": "CA"}
2  | Bob Johnson   | {"city": "Seattle", "type": "home", "state": "WA"}

Filtering with unnested arrays

-- Find customers with addresses in CA
SELECT DISTINCT
    c.id,
    c.info ->> 'name' AS customer_name
FROM customers c,
     jsonb_array_elements(c.info -> 'addresses') AS addr
WHERE addr ->> 'state' = 'CA';

Result:

id | customer_name
---+--------------
1  | Alice Smith

Checking for Keys and Values

Checking if a key exists

-- Find customers who have the 'orders' key
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info ? 'orders';

Result:

id | customer_name
---+--------------
2  | Bob Johnson

Checking for values in arrays

-- Find customers with a specific order number
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info -> 'orders' ? '101';

Result:

id | customer_name
---+--------------
2  | Bob Johnson

Check if any array element matches

-- Find customers who have an address in Oakland (using path operators)
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info @> '{"addresses": [{"city": "Oakland"}]}';

Result:

id | customer_name
---+--------------
1  | Alice Smith

Indexing JSON Data

For production databases with large JSON datasets, proper indexing is essential:

Creating a GIN index for containment queries

-- Create a GIN index for faster containment operations
CREATE INDEX idx_customers_info ON customers USING GIN (info);

Creating a specific index for a JSON field

-- Create an index on the 'email' field for fast lookups
CREATE INDEX idx_customers_email ON customers ((info ->> 'email'));

Using indexed fields in queries

-- This query can use the index we created above
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info ->> 'email' = 'alice@example.com';

Converting Between JSON and Tables

Converting JSON to rows (jsonb_to_record)

-- Extract JSON fields into a tabular structure
SELECT 
    id,
    rec.name,
    rec.email,
    rec.age
FROM 
    customers,
    jsonb_to_record(info) AS rec(
        name text,
        email text,
        age int
    );

Result:

id | name         | email             | age
---+--------------+-------------------+-----
1  | Alice Smith  | alice@example.com | 32
2  | Bob Johnson  | bob@example.com   | 45

Converting nested arrays to tables (jsonb_to_recordset)

-- Extract addresses from all customers into a table
SELECT 
    c.id,
    c.info ->> 'name' AS customer_name,
    addr.type AS address_type,
    addr.city,
    addr.state
FROM 
    customers c,
    jsonb_to_recordset(c.info -> 'addresses') AS addr(
        type text,
        city text,
        state text
    );

Result:

id | customer_name | address_type | city          | state
---+---------------+--------------+---------------+------
1  | Alice Smith   | home         | San Francisco | NY
1  | Alice Smith   | work         | Oakland       | CA
2  | Bob Johnson   | home         | Seattle       | WA

Converting query results to JSON

-- Aggregate customer orders into a JSON array
SELECT
    c.id,
    c.info ->> 'name' AS customer_name,
    json_agg(o.order_id) AS orders
FROM 
    customers c
    CROSS JOIN LATERAL (
        SELECT jsonb_array_elements_text(c.info -> 'orders') AS order_id
    ) o
GROUP BY c.id, c.info ->> 'name';

Result:

id | customer_name | orders
---+---------------+----------
2  | Bob Johnson   | ["101", "102", "103"]

Performance Considerations

When working with JSON in PostgreSQL, keep these performance considerations in mind:

Use jsonb instead of json: The jsonb type offers better performance for most operations.
Index strategically: Use GIN indexes for containment queries, but create specific indexes for frequently queried fields.
Consider denormalization carefully: While JSON allows you to denormalize your data model, excessive nesting can impact query performance.
Watch query patterns: Complex JSON path expressions or frequent unnesting of large arrays can be expensive operations.
Limit array size: Very large JSON arrays can lead to performance issues when unnested.
Consider projection: Extract only the fields you need rather than returning the entire JSON document.

Conclusion

PostgreSQL’s JSON capabilities make it an excellent choice for applications that need to store and query semi-structured data. With the operators and functions covered in this tutorial, you can effectively work with JSON data while still leveraging the power and reliability of a mature relational database.

For complex data models, consider a hybrid approach: use structured tables for fixed schemas and frequently queried data, and use JSON for flexible, schema-less data or nested structures that would be awkward to model relationally.

Ready to try these examples yourself? Open Beekeeper Studio and start exploring PostgreSQL’s JSON capabilities today.

Beekeeper Studio Is A Free & Open Source Database GUI

Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.

Download for Desktop

What Users Say About Beekeeper Studio

★★★★★

"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."

— Alex K., Database Developer

★★★★★

"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."

— Sarah M., Full Stack Engineer

Ready to Improve Your SQL Workflow?

Download Free

View All Editions →

PostgreSQL JSON Function Examples

Table of Contents

Creating JSON Data

Basic JSON Operations

Retrieving the entire JSON document

Extracting Values from JSON

Extracting a specific field with the -> operator (returns JSON)

Extracting a specific field with -» operator (returns text)

Accessing nested objects

Using the #> operator for path access

Using the #» operator for text path access

Modifying JSON Data

Adding a field with jsonb_set

Removing a field with - operator

Merging JSON objects with ||

Updating a nested field

Querying JSON Arrays

Getting array length

Extracting specific array elements

Unnesting arrays to query individual elements

Filtering with unnested arrays

Checking for Keys and Values

Checking if a key exists

Checking for values in arrays

Check if any array element matches

Indexing JSON Data

Creating a GIN index for containment queries

Creating a specific index for a JSON field

Using indexed fields in queries

Converting Between JSON and Tables

Converting JSON to rows (jsonb_to_record)

Converting nested arrays to tables (jsonb_to_recordset)

Converting query results to JSON

Performance Considerations

Conclusion

Beekeeper Studio Is A Free & Open Source Database GUI

What Users Say About Beekeeper Studio

Ready to Improve Your SQL Workflow?