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
Creating JSON objects + arrays

Full Course

$
349
$399
USD, one-time fee
The course "Mastering Postgres" helped me practice query commands and understand why they're important. Inspiring me to explore the 'why' and 'how' behind each one. Truly a great course!
Nakolus
Nakolus

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 create JSON directly within a Postgres database using functions like `json_build_object` to convert arguments into JSON objects. The video covers the use of `row_to_json` for converting database rows into JSON, making it easy to return data as JSON blobs without extensive manipulation. Additionally, we explore aggregating JSON with functions like `json_agg`, allowing for efficient data handling and manipulation directly within SQL before sending it over APIs.

Links

Variants documentation

Video Transcript

We're going to use Postgres to create some json. We used it to validate it. Now we're going to use it to create it. You might not be in this situation terribly often because again, you'll probably be getting the json from your application side and putting it into Postgres as fully formed json. There are tons of use cases for creating json within the database itself. We're going to start small and build up.

I have a couple examples here. They do require a lot of typing, so I'm just going to paste it in. The first one that we're going to look at is json_build_object. While this is arranged in sort of a clear key value pair situation, it's just a very attic array or a very attic arguments here, and I have just formatted it such that it looks nicely. This you can just keep feeding keys and values in, but if you do it like this, it sure does read a lot better. The point of this one is to take a bunch of arguments and turn it into a json object. If we run that, you'll see we get this back and let's just make some space and we can put that right there. That's not actually very much better. I could have just expanded, but that's fine. You'll see we get the ideas, the key, and then the name as the key, active as the key. Then we do get a full array down here because we did an array here.

We will run that again and we can do pg_typeof and surround that. We'll come up here and you'll see that it comes back as json. There is a corresponding jsonb_build_object, and it comes back as jsonb. Now the question here is, what are you going to do with it? What are you going to do with the result of this function? If you're going to write it into a jsonb column, you might use the jsonb operator. If you're going to return it back over the wire to the client, well, you just leave it as json text and return it back. There's no point in using the jsonb format there. It kind of depends on what you're going to do. If you're going to continue operating on this json object, leave it as jsonb, leave it as its parsed representation, and then continue to chain operations off of that rather than jumping back and forth between the parsed representation and the textual representation. In, for many of these, there are going to be, there are going to be json and jsonb variants. I'm not going to cover all of the different variants, but just remember that sometimes, if you need a jsonb variant, it exists. If you need a json variant, it exists. I'll leave a link to the docs down below.

Let's keep building some json. In the same way that we have json_build_object. You guessed it. We've got json_build_array. This is a wonky looking array 'cause it was originally set up to be an object. You'd probably have something more reasonable like that. There you go. You can build an array in Postgres. None of these terribly move me. There's one more that can be useful and that is to_json or to_jsonb. We'll stick with to_json. You can just pass in any value here. We'll say ('aason'::text). It comes back as a quoted json string and we can prove that it is json by doing typeof, there you go. We do get json there. It's not just strings. You could do, you know, you could do, I forgot a double colon. You could do an integer there. It's not just scalers. You could have an array of 1, 2, 3, 4, if you can type. You get your array back right there. Now these are useful, I will admit these are useful, but we need to move on to something that is probably more, probably more practical or probably more useful in your day-to-day potentially application building.

Let's look at a few of those. Now, one that does move me that I do think is quite practical is row_to_json. Now impractically, you could just put a row constructor in here, but we don't want to do that. We're want to do something more real. We're going to do row_to_json(u) from, let's do as users_json, you see where this is going, from, and then we're going to open this up and we'll say select * from users where, let's just do, where email = '[email protected]' as, and we're going to alias this to u. So if we run that, boom, there is the entire row of my record as json. You can imagine that the requirements are, I just need id and email. Now you can get back json blobs for each individual user or each individual, in this case, row that matches that query. Now we're not quite done yet because let's say that we expand this and we say where is_pro is pro is true. Now we get back a table where each individual row is json. That is what we asked it to do. This is not Postgres fault, but this is not terribly useful in this scenario, right?

You can imagine if you're trying to get back a bunch of json objects, you probably want that as a json blob and a json array. You can do that in Postgres without having to pull it all back across the wire and then loop over it and add it to a json array. We can do that with SQL. Let me show you how. We've got our row_to_json working pretty well here. You see we get those 44,000 rows back with a bunch of json blobs. If you want a single row, you can say json_agg and then we're going to throw parentheses around that and that aggregates, that's say json aggregation function, of which there are many we'll look at a few more later. This is a json aggregation function. We've gone from rows and columns, we've gone down just to a single row, single column that contains the value. This is a massive json blob that holds 44,000 ish records. This can be terribly useful if, on the other side, you're just going potentially throw it back out over some API, right? You can do your aggregations or json manipulation, you can do that here and then just throw it out over the wire. You do have more control.

Let's say you want to bring it up a level. You can say json_build_object again. Instead of just relying on your subquery down here, you can say json_build_object. You can say, "Eh, let's just start with 'id' , id. If you run that, you'll see, wow, okay, I can kind of start to get a sense of how this might be useful. Up here we can say 'email' , email and so we can build out our object up here instead of relying on this subquery down here. Now you'll see we're just selecting, we're just selecting from the entire table where is_pro is true. Then we're doing our little bit of json manipulation up here using the json_build_object. We can get rid of this and say from users u where is_pro is true and we should get the same thing back.

We've eliminated that temporary table and that subquery, which is, I don't know that super matters, but I can't imagine a very, very common situation where you're using some sort of ORM on the other side to build up a query. You don't necessarily want to do all the mucking about with the json manipulation down in the query. You want to do that up in the, or down in the filter. You want to do that up in the select. You do have a few options.

You can use your json_build_object to get it exactly how you want it, or you could do the subquery and just do the row_to_json function and that will take whatever is in the row and turn it into a json object, which can be terribly convenient. Now at least you know how to build some json objects out of your existing data, which I think is going to be very useful and very practical versus building them up from scratch, which can be useful. I think just in far fewer scenarios.