How to Query JSONB Data in PostgreSQL

Posted by Kyle Hankinson March 14, 2024

---

PostgreSQL's JSONB type lets you store JSON documents alongside relational data, with full indexing and query support. It is one of PostgreSQL's most powerful features.

JSON vs JSONB

PostgreSQL offers two JSON types:

• JSON: Stores the raw text. Preserves whitespace and key order. No indexing.
• JSONB: Stores a parsed binary format. Slightly slower to insert, much faster to query. Supports indexes.

Use JSONB unless you have a specific reason to preserve the original JSON formatting.

CREATE TABLE events (
    id    SERIAL PRIMARY KEY,
    data  JSONB NOT NULL
);

INSERT INTO events (data) VALUES
('{"type": "click", "page": "/home", "user": {"id": 42, "name": "Alice"}}');

Extracting Values

PostgreSQL provides several operators for navigating JSON:

Operator	Returns	Example
->	JSON element	data -> 'user' returns {"id": 42, "name": "Alice"}
->>	Text value	data ->> 'type' returns click
#>	JSON at path	data #> '{user,name}' returns "Alice"
#>>	Text at path	data #>> '{user,name}' returns Alice

-- Get all click events
SELECT * FROM events WHERE data ->> 'type' = 'click';

-- Get the user name from nested JSON
SELECT data #>> '{user,name}' AS user_name FROM events;

Containment Queries with @>

The @> operator checks if the left JSONB contains the right JSONB:

-- Find events where user id is 42
SELECT * FROM events
WHERE data @> '{"user": {"id": 42}}';

This is not only cleaner than chaining -> operators, it can also use GIN indexes for fast lookups.

Existence Operators

Operator	Meaning
?	Does the key exist?
?|	Do any of these keys exist?
?&	Do all of these keys exist?

-- Find events that have a "page" key
SELECT * FROM events WHERE data ? 'page';

Indexing JSONB

A GIN index makes JSONB queries fast:

-- General-purpose: supports @>, ?, ?|, ?&
CREATE INDEX idx_events_data ON events USING GIN (data);

-- Optimized for @> containment queries only (smaller index)
CREATE INDEX idx_events_data ON events USING GIN (data jsonb_path_ops);

For queries on a specific key, a B-tree index on the extracted value is more efficient:

-- Index a specific field for equality/range queries
CREATE INDEX idx_events_type ON events ((data ->> 'type'));

Modifying JSONB

-- Set a key
UPDATE events SET data = jsonb_set(data, '{page}', '"/about"') WHERE id = 1;

-- Remove a key
UPDATE events SET data = data - 'page' WHERE id = 1;

-- Merge with concatenation
UPDATE events SET data = data || '{"source": "mobile"}' WHERE id = 1;

When to Use JSONB

JSONB is a good fit for:

• Flexible schemas: user preferences, form data, API responses
• Event data: logs, analytics events with varying attributes
• Document storage: when you need some NoSQL flexibility within a relational database

Stick with regular columns for data you frequently filter, join, or aggregate on — relational columns are still faster for those operations.

Using SQLPro Studio

SQLPro Studio displays JSONB columns in query results so you can inspect the full JSON structure. You can write and test all of these JSONB queries in the query editor with syntax highlighting and autocomplete.

---