PostgreSQL JSONB: Querying, Indexing, and Manipulation Guide

By Sharon Ben-Moshe · October 3, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Learn how to query, index, and update JSONB documents in PostgreSQL. Master ->, ->>, @> containment operators, and GIN indexes.

Quick answer

PostgreSQL JSONB stores JSON in a decomposed binary format optimized for rapid lookup and indexing. Use '->' to extract JSON elements (preserving JSON type) and '->>' to extract values as text. Use the containment operator '@>' with GIN indexes to achieve high-performance document filtering.

FAQs

What is the difference between JSON and JSONB in PostgreSQL?

The `json` type stores an exact text copy of the input, requiring re-parsing on every query. The `jsonb` type stores decomposed binary data, supports GIN indexing, removes extraneous whitespace, and is vastly faster to query.

What is the difference between '->' and '->>' in JSONB?

`data->'key'` returns the value as a `jsonb` object or array. `data->>'key'` extracts the value as plain text (`text`), allowing standard string comparisons, numeric casting, or concatenation.

How do I index a JSONB column in PostgreSQL?

Create a Generalized Inverted Index (GIN): `CREATE INDEX idx_users_meta ON users USING gin (metadata);`. This accelerates queries using `@>`, `?`, `?|`, and `?&` operators.

How do I update a nested property in a JSONB column?

Use `jsonb_set(target, path, new_value)` or the concatenation operator `||` to merge top-level properties: `UPDATE users SET metadata = metadata || '{"verified": true}'::jsonb;`.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

Always choose JSONB over JSON for new tables to enable indexing and binary processing.

Use '->' for intermediate JSON object extraction and '->>' for the final text extraction.

Use the '@>' containment operator for indexable document matching.

Create GIN indexes on JSONB columns to prevent slow sequential table scans.

Extracting values with -> and ->>

Understanding the return types of arrow operators is crucial. Using '->' returns JSONB (wrapped in quotes for strings), while '->>' returns plain SQL text:

Extracting JSONB properties in PostgreSQL

-- Sample table with JSONB column
-- metadata contains: {"profile": {"tier": "gold"}, "login_count": 42}

SELECT
  user_id,
  -- Returns JSONB object: {"tier": "gold"}
  metadata->'profile' AS profile_json,
  -- Returns text: 'gold' (can be compared with = 'gold')
  metadata->'profile'->>'tier' AS tier_text,
  -- Cast numeric JSON text to integer
  (metadata->>'login_count')::INTEGER AS login_count
FROM user_accounts
WHERE metadata->'profile'->>'tier' = 'gold';

High-performance searching with the @> containment operator

The containment operator `@>` tests whether the left JSONB document includes the right JSONB structure. When combined with a GIN index, queries run in sub-millisecond time:

GIN indexing and containment search

-- Create a GIN index on the entire JSONB column
CREATE INDEX idx_accounts_metadata ON user_accounts USING gin (metadata);

-- Fast indexed query: finds rows where tags array contains 'vip'
SELECT user_id, metadata
FROM user_accounts
WHERE metadata @> '{"tags": ["vip"]}';

-- Filter on multiple nested attributes simultaneously
SELECT user_id
FROM user_accounts
WHERE metadata @> '{"status": "active", "settings": {"notifications": true}}';

Updating JSONB documents in place

PostgreSQL allows targeted updates without replacing the entire JSON blob:

Updating JSONB with merge and jsonb_set

-- 1. Shallow merge: adds or updates top-level keys
UPDATE user_accounts
SET metadata = metadata || '{"last_active": "2026-10-03"}'::jsonb
WHERE user_id = 101;

-- 2. Deep update: update a nested path with jsonb_set
UPDATE user_accounts
SET metadata = jsonb_set(
  metadata,
  '{settings,theme}',
  '"dark"'::jsonb,
  true -- create_if_missing
)
WHERE user_id = 101;

PostgreSQL JSONB: Querying, Indexing, and Manipulation Guide

Quick answer

FAQs

What is the difference between JSON and JSONB in PostgreSQL?

What is the difference between '->' and '->>' in JSONB?

How do I index a JSONB column in PostgreSQL?

How do I update a nested property in a JSONB column?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Extracting values with -> and ->>

High-performance searching with the @> containment operator

Updating JSONB documents in place

Official references

Quick answer

FAQs

What is the difference between JSON and JSONB in PostgreSQL?

What is the difference between '->' and '->>' in JSONB?

How do I index a JSONB column in PostgreSQL?

How do I update a nested property in a JSONB column?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Extracting values with -> and ->>

High-performance searching with the @> containment operator

Updating JSONB documents in place

Related SQL guides

Official references