liveBook · Manning

@> operator

As explained in Just Use Postgres!

The PostgreSQL JSONB containment operator @> tests whether the left JSONB value contains the structure represented by the right JSONB value.

The PostgreSQL JSONB containment operator @> tests whether the JSONB value on the left contains the structure supplied on the right. It is useful for filtering nested application data without extracting each field separately. section 5.4.3

Count matching pizza orders

SELECT count(*) FROM pizzeria.order_items WHERE pizza @> '{"crust": "gluten_free", "type": "custom"}'; The query returns 148 orders. Adding the nested tomatoes condition narrows the result to 7 orders. 

                                QUERY PLAN
----------------------------------------------------------------------------
 Aggregate (actual time=0.784..0.785 rows=1 loops=1)
   ->  Bitmap Heap Scan on order_items 
 ➥      (actual time=0.139..0.747 rows=563 loops=1)
         Recheck Cond: (pizza @> '{"type": "custom"}'::jsonb)
         Heap Blocks: exact=101
         ->  Bitmap Index Scan on idx_pizza_orders_gin 
 ➥            (actual time=0.109..0.110 rows=563 loops=1)
               Index Cond: (pizza @> '{"type": "custom"}'::jsonb)
 Planning Time: 1.444 ms
 Execution Time: 0.830 ms
(8 rows)

Inspect how a default GIN index supports @> through Bitmap Index Scan and Bitmap Heap Scan, including condition rechecking. section 5.6.2

How Just Use Postgres! covers it

How deep it goes
Explains top-level and nested matching, combines predicates, and traces GIN execution, but defers exhaustive containment semantics.
Examples
Demonstrates pizza filters for gluten-free crust, custom type, nested toppings, and GIN-backed query plans.
What it assumes
Assumes SQL and JSON object familiarity while grounding usage in JSONB columns and pizza-order examples.
What you can do after
Apply @> in PostgreSQL queries and evaluate whether a default or jsonb_path_ops GIN index supports execution.
How it connects
Connects JSONB, JSON operators, JSON extraction, GIN indexes, Bitmap Index Scan, and jsonb_path_ops.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.