Work confidently with semi-structured data in PostgreSQL — store and query JSONB, index it with GIN, model repeated values with arrays, and build a real full-text search. You will finish able to choose between relational and document modelling and ship queries that are both correct and fast.
Watch the free preview
JSON vs JSONB — two types, one that you almost always want — free to watch, no account needed.
What you'll learn
- Work with semi-structured data in PostgreSQL using JSONB, arrays and full-text search
- Choose correctly between JSON and JSONB and between relational and document modelling
- Query JSONB with navigation, containment and existence operators
- Index JSONB and full-text columns with GIN for fast lookups
- Build a ranked full-text search over a real content table
Syllabus
JSONB Fundamentals
JSON vs JSONB — two types, one that you almost always wantFree preview
Storing and building JSONB values
Querying JSONB
Navigating documents with -> ->>
Containment and existence — @> ? ?| and ?&
Indexing JSONB and Working with Arrays
Making JSONB fast with GIN indexes
PostgreSQL arrays — the middle ground
Full-Text Search and Choosing a Model
Full-text search with tsvector and tsquery
Ranking, indexing search, and relational vs document modelling
Lab — 10 Exercises & Solutions
Exercises 1–5
Exercises 6–10