liveBook · Manning

Jsonb_path_ops

As explained in Just Use Postgres!

jsonb_path_ops is a PostgreSQL GIN operator class for JSONB values that indexes hashed paths from the JSON root to values and array elements.

jsonb_path_ops is a nondefault PostgreSQL GIN operator class for JSONB. It hashes paths from the JSON root to values and array elements, reducing index size and improving lookup efficiency while limiting flexibility. section 5.6.2

A sample pizza order contributes paths such as:

  • size.large, type.three cheese, crust.thin, and sauce.marinara section 5.6.2
  • toppings.cheese.cheddar.regular, toppings.cheese.mozzarella.extra, and toppings.cheese.parmesan.light section 5.6.2

Create and verify the index

CREATE INDEX idx_pizza_orders_paths_ops_gin ON pizzeria.order_items USING GIN (pizza jsonb_path_ops); EXPLAIN (analyze, costs off) SELECT count(*) FROM pizzeria.order_items WHERE pizza @> '{"type": "custom"}'; The plan uses a Bitmap Index Scan on idx_pizza_orders_paths_ops_gin and returns 563 rows. 

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

Inspect the pizza order whose paths become hashed index entries. section 5.6.2

How Just Use Postgres! covers it

How deep it goes
Explains path extraction, hashing, index lookup, and size comparison, but does not detail hash implementation internals.
Examples
Uses pizza-order paths, nested cheese arrays, CREATE INDEX, EXPLAIN output, and a 112 kB versus 56 kB size comparison.
What it assumes
Assumes familiarity with PostgreSQL tables, JSONB values, GIN indexes, and JSONB query operators.
What you can do after
Operate/configure a JSONB GIN index with jsonb_path_ops and verify containment-query usage through EXPLAIN.
How it connects
Connects JSONB containment operators, GIN indexes, bitmap index scans, and bitmap heap scans.
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.