Logo ← PostgreSQL Blog

PostgreSQL JSONB

PostgreSQL gives you the best of both worlds: powerful relational queries and flexible JSON document handling. If you’re coming from a…

PostgreSQL JSONB

PostgreSQL gives you the best of both worlds: powerful relational queries and flexible JSON document handling. If you’re coming from a NoSQL background (like MongoDB), you’ll feel right at home working with jsonb in Postgres but with better indexing and query consistency.

In this post, we’ll cover:

  • The difference between json and jsonb
  • How to store and query JSON data
  • Indexing with GIN (and why it’s magical)
  • JSONPath: powerful, expressive queries
  • A comparison with MongoDB-style NoSQL

json vs jsonb: Key Differences

Feature           | json                       | jsonb
-------------------------------------------------------------------------------
Format            | Raw text                   | Binary
Read/Write Speed  | Slower read, faster insert | Faster query, slightly slower insert
Duplicated Keys   | Allowed                    | Last one kept
Key Order         | Preserved                  | Not preserved
Index Support     | No                         | Yes (GIN, BTREE)
Recommended?      | No                         | Yes

Verdict: Use jsonb 99% of the time. It’s faster, indexable, and supports powerful operators.

Create a Table and Insert Some JSONB Data

CREATE TABLE api_data (
  id SERIAL PRIMARY KEY,
  data JSONB
);

Let’s simulate some API-style data:

INSERT INTO api_data (data) VALUES
('{
  "name": "Alice",
  "age": 30,
  "email": "alice@example.com",
  "tags": ["developer", "postgresql"],
  "address": {
    "city": "New York",
    "zip": "10001"
  }
}'),
('{
  "name": "Bob",
  "age": 25,
  "email": "bob@example.com",
  "tags": ["designer", "remote"],
  "address": {
    "city": "Los Angeles",
    "zip": "90001"
  }
}');

Query JSONB Data: Simple Examples

Get all users from New York:

SELECT data->>'name'
FROM api_data
WHERE data->'address'->>'city' = 'New York';

Users with the tag postgresql:

SELECT data->>'name'
FROM api_data
WHERE data->'tags' @> '["postgresql"]';

Check if a key exists:

SELECT data->>'name'
FROM api_data
WHERE data ? 'tags';

Boost Performance with GIN Indexes

When working with large datasets, raw jsonb queries can slow down. Enter: GIN indexes.

CREATE INDEX idx_api_data_gin ON api_data USING GIN (data);

-- chechk pending_pages, and pending_tuples
SELECT * FROM pgstatginindex('idx_api_data_gin'); --CREATE EXTENSION pgstattuple;

This index speeds up queries using:

  • @> (contains)
  • ? / ?| / ?& (key existence)
  • @? (JSONPath filtering)

GIN (Generalized Inverted Index) works great with jsonb because it indexes every key/value pair individually.

Example: This query is now fast thanks to GIN:

SELECT data->>'name'
FROM api_data
WHERE data @> '{"age": 25}';

JSONPath: Advanced Queries, MongoDB-Style

PostgreSQL 12+ supports JSONPath, which gives MongoDB-style querying inside SQL.

Find users with a postgresql tag

SELECT data->>'name'
FROM api_data
WHERE data @? '$.tags[*] ? (@ == "postgresql")';

Find users over 28 years old

SELECT data->>'name'
FROM api_data
WHERE data @@ '$.age > 28';

Get users whose zip starts with “90”

SELECT data->>'name'
FROM api_data
WHERE data @? '$.address.zip ? (@ like_regex "^90")';
Note: JSONPath expressions use a special syntax starting with $, similar to jQuery selectors or MongoDB’s dot notation.

Updating JSONB Data

Let’s change Alice’s city to “Boston”:

UPDATE api_data
SET data = jsonb_set(data, '{address,city}', '"Boston"')
WHERE data->>'name' = 'Alice';

Or use PostgreSQL 14+ subscripting syntax:

UPDATE api_data
SET data['address']['city'] = '"Boston"'
WHERE data->>'name' = 'Alice';

PostgreSQL vs MongoDB: Quick Comparison

Feature            | PostgreSQL JSONB  | MongoDB
-------------------------------------------------------------------------------
Query Language     | SQL + JSONPath    | Mongo Query Language
Indexing           | GIN, B-tree       | B-tree, Compound, Text
ACID Compliance    | Strong            | Weak (eventual by default)
Transactions       | Full              | Partial (multi-doc with caveats)
Schema Flexibility | Optional (hybrid) | Flexible
Joins              | Native            | Limited ($lookup is clunky)
Type Safety        | Strong            | Weak (everything is BSON)
Want Mongo-like flexibility with real SQL and transactions? PostgreSQL jsonb is for you.

Conclusion

PostgreSQL with JSONB is a real powerhouse:

  • Store semi-structured or unstructured data
  • Use expressive, indexable queries
  • Keep relational power while embracing document flexibility
TL;DR: PostgreSQL + JSONB = NoSQL with joins and transactions.

Whether you’re building APIs, logging systems, or hybrid relational/document apps mastering jsonb is a next-level skill in your SQL toolkit.