Mastering Postgres
Introduction
Introduction to the course
Overview of course structure
Postgres vs. everyone
The psql CLI
Data Types
Introduction to schema
Integers
Numeric
Floating point
Storing money
NaNs and infinity
Casting types
Characters types
Check constraints
Domain types
Charsets and collations
Binary data
UUIDs
Boolean
Enums
Timestamps
Timezones
Dates and times
Advanced Data Types
Intervals
Serial type
Sequences
Identity
Network and mac addresses
JSON
Arrays
Generated columns
Text search types
Bit string
Ranges
Composite types
Nulls
Unique constraints
Exclusion constraints
Foreign key constraints
Indexing
Introduction to indexes
Heaps and CTIDs
B-Tree overview
Primary keys vs. secondary indexes
Primary key types
Where to add indexes
Index selectivity
Composite indexes
Composite range
Combining multiple indexes
Covering indexes
Partial indexes
Index ordering
Ordering nulls in indexes
Advanced Indexing
Functional indexes
Duplicate indexes
Hash indexes
Naming indexes
Understanding Query Plans
Introduction to explain
Explain structure
Scan nodes
Costs and rows
Explain analyze
Generating Results
Introduction to queries
Cross joins
Inner joins
Outer joins
Subqueries
Lateral joins
ROWS FROM
Filling gaps in sequences
Subquery elimination
Combining queries
Set generating functions
Indexing joins
Advanced SQL
Introduction to advanced SQL
Grouping
Grouping sets, rollups, cubes
Window functions
CTEs
CTEs with window functions
Recursive CTE
Hierarchical recursive CTE
Handling nulls
Row value syntax
Views
Materialized views
Removing duplicate rows
Upsert
Returning keyword
COALESCE + generated column
Full Text Search
Introduction to full text search
Searching with LIKE
Vectors, queries, and ranks
Websearch
Ranking
Indexing full text search
Highlighting
Advanced JSON
Intro to JSON
JSON vs JSONB
Validating JSON
Creating JSON objects + arrays
JSON extraction
JSON containment
JSON existence
JSON recordset
Updating JSON
Indexing JSON parts
GIN index
Vectors (pgvector)
Intro to pgvector
Vector embedding columns
Find related articles
Upsert vector embedding
Semantic search
Other operators
Vector indexes
Outro
Thank you
Bonus interviews
Heroku's glory days & Postgres vs the world (with Craig Kerstiens)
Creating a Postgres platform with Monica & Tudor from Xata.io
Bootstrapping an email service provider (with Jesse Hanley)
Locked video

Please purchase the course to watch this video.

Video thumbnail
Advanced JSON
JSON existence

Full Course

$
349
$399
USD, one-time fee
Mastering Postgres has dramatically improved my understanding of indexes. It has helped me make informed decisions around my queries and schema rather than blindly adding indexes and hoping for the best.
Dwight Watson
Dwight Watson

PostgreSQL database platform

Shorten dev cycles with branching and zero-downtime schema migrations.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Xata

Summary

Learn how to check if a JSON object contains specific keys or if an array includes certain items in Postgres. Using the question mark operator (`?`), you can easily verify if a key or item exists, and there are operators to check for any or all keys/items. These techniques are useful for filtering data or identifying issues within your database efficiently.

Video Transcript

There's one other type of inspection that we can do on JSON blobs and that is existence. We've done extraction, encapsulation. Now we're going to do existence. This is where you can just merely check if an object contains a key or an array contains an item. There are a couple different options we have here. Here's a hard-coded JSON blob we'll move into a table here in a second but the very basic existence operator is this question mark. Then you are testing to see if it contains a key.

Here we're saying, "Does this blob contain status?" In fact, it does. Does it contain foo? It does not. It also works with arrays. You can say, "Does this array contain apple?" Yes. Does it contain asdf? No, it does not. That's the very basic existence operator. You have two other variants. One of them is does it contain any of the following.

We can say, "Does it contain? Does it contain status or does it contain, let's just make one up, created_at?" And we see that it does contain one of those two. In fact, it contains status because if we were to change this right here, it would go to false. This is testing to see does it contain any of those. You guessed it, what's coming next is does it contain all of those? Let's just change customer just for giggles to created_at. You'll see that now it contains all of those keys, whereas here it does not contain all of those keys but it does contain any of those keys. Again, we can use this to filter down rows. We can use this as a part of our where clause. In fact, let's do that right now. We still have this orders, JSON, hanging around. Let's just do where. Then I believe it's called details contains status. This one we just want to know, give us all of the orders that have a status. In this case, it is all five.

We could say, "Give us all of the orders that have a status or a pending status," if we've designed a poor schema here. This would give us exactly what we need. In fact, this might be a good way to test if this poorly-designed schema has some bad data in it 'cause if there's anything that has a status and a pending status, that doesn't make a lot of sense to me. We should probably have one or the other. We could use the and existence operator here to check to see if any of these blobs contain both of those keys. If so, they need to go into some sort of remediation queue to figure out what the heck is going on here.

That's JSON existence. Not a whole lot there. You have the basic operator, which tests one key. You have the or or the any operator, which tests does it contain any of those keys. You have the all or the and operator, which says it must contain all of these keys.