Right, let’s talk about JSONB operators. This is where you stop just storing JSON and start actually using it. Forget the clunky, string-based horror of json_extract_path_text or whatever your previous database tried to sell you. PostgreSQL gives you a proper set of tools that feel, well, like they belong in a database. They’re the difference between poking your data with a stick and wielding a lightsaber.

We’ll break them down into two camps: the path navigators (who get you the data) and the existence checkers (who tell you if something’s there).

The Path Navigators: -> and -»

These two are your workhorses. You’ll use them constantly. The key difference is what they return, and getting it wrong is the most common rookie mistake.

The -> operator returns a JSONB object. Use it when you’re digging into an object and your next step is to keep digging. The ->> operator returns text. Use it when you’ve reached the value you want to actually use in a predicate, like in a WHERE clause, or to cast to another type.

-- Let's set up a classic: a table of users with a messy JSONB profile column
CREATE TABLE users (
    id serial PRIMARY KEY,
    profile jsonb
);

INSERT INTO users (profile)
VALUES 
('{ "name": "Alice", "age": 30, "contact": { "email": "alice@example.com", "phone": "555-1234" }, "tags": ["admin", "beta"] }'),
('{ "name": "Bob", "age": 25, "contact": { "email": "bob@company.org" }, "tags": ["user"] }');

-- Get the entire 'contact' object as JSONB (so you can keep querying into it)
SELECT profile -> 'contact' AS contact_object FROM users;
-- Returns: {"email": "alice@example.com", "phone": "555-1234"}

-- Get the 'email' value within 'contact' as TEXT
SELECT profile -> 'contact' ->> 'email' AS email_address FROM users;
-- Returns: alice@example.com

-- This will FAIL miserably. You can't compare a JSONB object to a text string.
SELECT * FROM users WHERE profile -> 'contact' = '{"email": "alice@example.com"}'; -- NO

-- This is how you do it. You compare TEXT to TEXT.
SELECT * FROM users WHERE profile -> 'contact' ->> 'email' = 'alice@example.com'; -- YES

See the problem? If you use -> when you meant to use ->>, you’re trying to compare an entire JSON document to a simple string, which will almost always fail. It’s like trying to compare a whole book to the word “the.”

The Path Navigators, Part 2: #> and #»

What if your path isn’t just one key deep? What if you need to go down contact->business->primary->email? Chaining -> and ->> gets messy and hard to read. This is where the path operators, #> and #>>, come in. They take an array of text keys to navigate multiple levels at once.

-- Imagine a more nested structure
UPDATE users SET profile = '{"name": "Alice", "company": {"name": "Data Inc", "address": {"city": "Seattle"}}}' WHERE id = 1;

-- The chained way (a bit ugly)
SELECT profile -> 'company' -> 'address' ->> 'city' AS city FROM users;

-- The path way (cleaner, especially in WHERE clauses)
SELECT profile #> '{company, address, city}' AS city_json FROM users; -- Returns JSONB: "Seattle"
SELECT profile #>> '{company, address, city}' AS city_text FROM users; -- Returns TEXT: Seattle

-- This is extremely useful for dynamic queries or when your path is stored in a variable.

The Existence Checkers: @>, ?, ?|, ?&

These operators are why JSONB is brilliant for filtering. They don’t care about structure or order; they just ask: “Does this JSONB document contain this thing?”

  • @>: “Does the left document contain the right document?” This is the most powerful one. It’s a subset check.
  • ?: “Does this top-level key exist?”
  • ?|: “Do any of these keys exist?” (OR logic)
  • ?&: “Do all of these keys exist?” (AND logic)
-- Find users who have a phone number specified in their contact info.
-- This works because @> checks if the profile contains the fragment {"contact": {"phone": ...}}
SELECT * FROM users WHERE profile @> '{"contact": {"phone": null}}';

-- Find users who have the "admin" tag. The value doesn't matter, just the key's existence.
SELECT * FROM users WHERE profile ? 'admin'; -- NO: 'admin' is a value, not a key!
SELECT * FROM users WHERE profile -> 'tags' ? 'admin'; -- YES: 'tags' is a key, and we check for the value 'admin' in the array

-- Find users who have *either* a 'phone' OR 'email' key under 'contact'
-- (This checks for the KEYS 'phone' or 'email', not their values)
SELECT * FROM users WHERE profile -> 'contact' ?| array['phone', 'email'];

-- Find users who have BOTH 'email' AND 'phone' keys under 'contact'
SELECT * FROM users WHERE profile -> 'contact' ?& array['email', 'phone'];

Here’s the critical insight: the existence operators ?, ?|, and ?& work on the keys of a JSONB object or the elements of a JSONB array. This is a massive source of confusion. You use ? to check if an array contains the value ‘admin’, but you use ? to check if an object contains the key ‘phone’. The operator is the same; the context changes everything.

Why This All Matters: Indexing

You can’t talk about operators without talking about indexes, because without them, every one of these queries is a full table scan. PostgreSQL allows you to create indexes specifically tailored to these operators, which is what makes this whole approach blisteringly fast.

The two big ones are:

  1. GIN Indexes: These are the magic. A GIN (Generalized Inverted Index) index is practically made for JSONB. It indexes every key and value within the JSONB document. This is what makes the existence operators (?, @>, etc.) fast. You’ll almost always use a GIN index on a JSONB column you plan to query heavily.

    CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);
-- This index will now accelerate queries using @>, ?, ?|, and ?&

  2. BTREE Indexes on Expressions: Remember how ->> returns text? You can build a regular BTREE index on that expression to make equality checks light-speed.

    -- Create an index specifically for searching emails
CREATE INDEX idx_users_email ON users ((profile #>> '{contact, email}'));
-- Now this query is fast:
SELECT * FROM users WHERE profile #>> '{contact, email}' = 'alice@example.com';

The designers got this right. The operators are logical, composable, and, most importantly, indexable. It turns JSONB from a dumb storage blob into a first-class queryable data structure. Just remember the golden rule: -> for JSONB, ->> for text, and always be thinking about what kind of index your query pattern will need.