0Pricing
SQL Academy · Lesson

Path Operators: -> ->> @>

Navigate JSONB documents with ->, ->>, #>>, and check containment with @> and the jsonb_path_query family.

Path Operators: -> ->> @> is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Three Operator Families

JSONB path operators come in three flavours:

  • -> and ->> — extract by key or index
  • #> and #>> — extract by path
  • @> and ? — test containment / key existence

-> Returns JSONB

Extract a value, keep it as JSONB:

SELECT data->'user_id' FROM events;
-- returns JSONB like  42

SELECT data->0 FROM events;
-- first element of a JSONB array

->> Returns Text

Extract a value as TEXT — useful for casting:

SELECT data->>'user_id'  FROM events;
-- '42'  (TEXT)

SELECT (data->>'user_id')::BIGINT FROM events;
-- 42    (BIGINT)

#> Path Extract (JSONB)

Walk multiple levels by an array of keys:

SELECT data #> '{user, address, city}' FROM events;
-- nested JSONB value

#>> Path Extract (TEXT)

Same as #>, but returns TEXT:

SELECT data #>> '{user, address, city}' FROM events;
-- 'Berlin'

@> Containment

"Does the document contain this sub-document?" — the most useful filter:

SELECT * FROM events
WHERE data @> '{"type":"login"}';

-- Works for nested matching too:
WHERE data @> '{"user":{"plan":"pro"}}'

? Key Existence

Test whether a key exists at the top level:

SELECT * FROM events WHERE data ? 'error';
SELECT * FROM events WHERE data ?| ARRAY['a','b'];    -- any of these keys
SELECT * FROM events WHERE data ?& ARRAY['a','b'];    -- all of these keys

JSONB Path Queries (jsonb_path_query)

Modern PostgreSQL supports SQL/JSON path expressions:

SELECT jsonb_path_query(data, '$.user.tags[*]') FROM events;
SELECT jsonb_path_exists(data, '$.user.tags[*] ? (@ == "admin")') FROM events;

Combining With Indexes

A GIN index makes @> and ? fast:

CREATE INDEX events_data_gin ON events USING GIN (data);
-- supports @>, ?, ?|, ?&

CREATE INDEX events_data_path_gin ON events USING GIN (data jsonb_path_ops);
-- smaller, faster — but only supports @>

Querying Arrays Inside JSONB

Find documents whose array contains a value:

SELECT * FROM events
WHERE data->'tags' @> '["admin"]'::JSONB;

-- Or via path query:
SELECT * FROM events
WHERE jsonb_path_exists(data, '$.tags[*] ? (@ == "admin")');

Updating JSONB

jsonb_set, ||, - to modify:

UPDATE events
SET data = jsonb_set(data, '{user,plan}', '"pro"')
WHERE id = 1;

UPDATE events SET data = data || '{"new_field":true}'::JSONB;
UPDATE events SET data = data - 'old_field';

Aggregating JSONB

Group multiple rows into one JSONB:

SELECT jsonb_agg(data) FROM events WHERE user_id = 42;
SELECT jsonb_object_agg(name, value) FROM settings;

Recap

Path operators do everything from extraction to mutation.

  • -> JSONB, ->> TEXT
  • #> path, #>> path TEXT
  • @> containment (GIN indexable)
  • jsonb_set / || / - for updates

Quick Check

What's the difference between data->'k' and data->>'k'?

Frequently asked questions

Is the “Path Operators: -> ->> @>” lesson free?

Yes — the full text of “Path Operators: -> ->> @>” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Path Operators: -> ->> @>”?

Navigate JSONB documents with ->, ->>, #>>, and check containment with @> and the jsonb_path_query family. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Path Operators: -> ->> @>” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. JSONB vs JSON: When to Use Each
  2. Path Operators: -> ->> @>
  3. Indexing JSONB with GIN
  4. Modelling: When JSONB Beats Normalisation
← Back to SQL Academy