Using JSON and JSONB in PostgreSQL: A Practical Guide with Examples

Modern applications often work with semi-structured or dynamic data, such as API responses, configuration files, or user-generated content. To support these use cases, PostgreSQL provides powerful native support for JSON and JSONB data types.

Unlike traditional relational columns, JSON data allows flexible schemas while still benefiting from PostgreSQL’s robustness, indexing, and query capabilities.

In this article, you will learn:

• The difference between JSON and JSONB
• When and why to use each type
• How to insert, query, update, and index JSON/JSONB data
• Practical examples commonly used in real-world applications

What Are JSON and JSONB in PostgreSQL?

PostgreSQL supports two JSON-related data types:

1. JSON

• Stores data in text format
• Preserves whitespace and key order
• Minimal processing during insertion
• Slower for querying

2. JSONB (Binary JSON)

• Stores data in binary format
• Removes whitespace and key order
• Faster querying and indexing
• Supports advanced operators and indexes

👉 Best practice:
Use JSONB in most cases unless you explicitly need to preserve formatting or key order.

Creating a Table with JSON and JSONB

Let’s start with a simple example.

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    profile JSONB
);

Example JSONB data:

{
  "full_name": "John Doe",
  "email": "john@example.com",
  "skills": ["PostgreSQL", "Docker", "Linux"],
  "experience": {
    "years": 5,
    "level": "senior"
  }
}

Inserting JSONB Data

Insert JSON data using standard SQL syntax:

INSERT INTO user_profiles (username, profile)
VALUES (
    'johndoe',
    '{
       "full_name": "John Doe",
       "email": "john@example.com",
       "skills": ["PostgreSQL", "Docker", "Linux"],
       "experience": {
         "years": 5,
         "level": "senior"
       }
     }'
);

PostgreSQL automatically validates JSON syntax before storing the data.

Querying JSON and JSONB Data

Accessing Top-Level Keys

Use the -> and ->> operators:

SELECT
    profile->'full_name' AS name_json,
    profile->>'full_name' AS name_text
FROM user_profiles;

• -> returns JSON
• ->> returns text

Accessing Nested JSON Fields

SELECT
    profile->'experience'->>'level' AS experience_level
FROM user_profiles;

Filtering with JSONB Data

Find users with senior-level experience:

SELECT username
FROM user_profiles
WHERE profile->'experience'->>'level' = 'senior';

Working with JSON Arrays

Query Elements Inside an Array

SELECT username
FROM user_profiles
WHERE profile->'skills' ? 'PostgreSQL';

The ? operator checks if a key or array element exists.

Expand JSON Arrays into Rows

SELECT
    username,
    jsonb_array_elements_text(profile->'skills') AS skill
FROM user_profiles;

Useful for reporting and analytics queries.

Updating JSONB Data

Update a Single Key

Use jsonb_set to modify part of a JSONB document:

UPDATE user_profiles
SET profile = jsonb_set(profile, '{email}', '"newemail@example.com"')
WHERE username = 'johndoe';

Add a New Key

UPDATE user_profiles
SET profile = jsonb_set(
    profile,
    '{last_login}',
    '"2025-01-10"',
    true
)
WHERE username = 'johndoe';

The true flag allows adding a new key if it does not exist.

Removing Keys from JSONB

Remove a key using the - operator:

UPDATE user_profiles
SET profile = profile - 'last_login'
WHERE username = 'johndoe';

JSONB Indexing for Performance

One of the biggest advantages of JSONB is indexing support.

GIN Index for JSONB

CREATE INDEX idx_user_profiles_profile
ON user_profiles
USING GIN (profile);

This dramatically improves performance for:

• Key existence checks
• Containment queries
• Complex JSON filters

Containment Queries

Find users with a specific skill:

SELECT username
FROM user_profiles
WHERE profile @> '{"skills": ["PostgreSQL"]}';

The @> operator checks if the JSON document contains a specific structure.

JSON vs JSONB: When to Use Which?

Feature	JSON	JSONB
Storage format	Text	Binary
Query performance	Slower	Faster
Index support	Limited	Full
Preserves formatting	Yes	No
Recommended	Rarely	Most use cases

Recommendation:
Use JSONB for application data, APIs, and analytics.

Common Use Cases for JSONB in PostgreSQL

• Storing API responses
• User preferences and settings
• Event logs and metadata
• Semi-structured data in microservices
• Hybrid relational + NoSQL design

Best Practices

• Avoid storing everything in JSONB — relational columns are still faster for fixed attributes
• Create indexes only when necessary
• Keep JSON documents reasonably small
• Validate JSON structure at the application level when possible

Conclusion

PostgreSQL’s JSON and JSONB features provide an excellent balance between flexibility and performance. With powerful operators, indexing capabilities, and native validation, JSONB enables PostgreSQL to handle modern application data without sacrificing reliability.

By understanding how to query, update, and index JSONB effectively, you can design databases that are both scalable and maintainable.