liveBook · Manning

? operator

As explained in Just Use Postgres!

A JSONB key-exists operator tests whether a specified key exists in a JSONB value.

The PostgreSQL JSONB ? operator tests whether a specified key exists in a JSONB value. It is useful when queries depend on optional fields in stored JSON structures. section 5.7

Check for an optional JSONB key

Run `EXPLAIN (analyze, costs off) SELECT count(*) FROM pizzeria.order_items WHERE pizza ? 'special_instructions';`. PostgreSQL evaluates whether each pizza JSONB value contains that key. The shown plan uses `idx_pizza_orders_gin` through a Bitmap Index Scan and returns zero matching rows. section 5.6.2

 {  
  "size": "large",  
  "type": "three cheese", 
  "crust": "thin", 
  "sauce": "marinara", 
  "toppings": {
    "cheese": [  
      {"cheddar": "regular"},
      {"mozzarella": "extra"},
      {"parmesan": "light"}  
    ]
  }  
 }

Inspect the sample pizza JSONB object to identify the keys that a ? query can test. section 5.6.2

How Just Use Postgres! covers it

How deep it goes
Defines the key-existence test and demonstrates indexed execution, but does not detail operator edge cases.
Examples
Uses a pizza-order JSONB column and checks for the special_instructions key with EXPLAIN output.
What it assumes
Assumes JSONB columns and basic PostgreSQL query syntax.
What you can do after
Apply it via PostgreSQL SQL to filter JSONB records by key presence and inspect GIN index usage with EXPLAIN.
How it connects
Connects JSONB querying with GIN indexes, the containment operator, and JSON path match operators.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

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.