It's time to do some stuff with JSON. In fact, it's time to do a lot of stuff with JSON. PostgreSQL has a ton of JSON affordances and we're going to look at a lot of them. I'll leave links down in the notes because there are many dozens of functions and we're not going to look at them all.

Before we do any of that, I just want to do a quick refresh on when you might use JSON in a relational database and when you might not. There are some people that would tell you never, never, ever, ever use a JSON Blob. I don't buy that. I don't think that that has enough nuance as a piece of advice. Here are a couple of things that you can look for when you're deciding should I use JSON Blob, or should I create some sort of top level table schema? The first thing is, if you are infrequently querying into the blob of JSON, so this would be something like potentially logs or web hooks or request response logging, something like that. You're very rarely breaking that blob apart from SQL, and you're trying to get at it from the outside. I would say that's fine. Store it as a blob. That falls into the, maybe the more historical artifact, where you're keeping the JSON Blob around just to have it. That's great. I love it.

On the other hand, if you have a big old blob, and you're frequently querying into the blob, so you have a bunch of keys in there and your queries are oftentimes hitting those keys inside of the blob, that's a point, maybe in the other direction of, let's break that out into multiple columns. Another point in that direction would be, does the JSON have a rigid schema? Or rather, should the JSON have a rigid schema? Am I trying to enforce that a JSON Blob is a certain shape? If so, that might be a hint that you should use PostgreSQL for what PostgreSQL is used for, which is creating columns and tables and giving each column a data type and letting PostgreSQL do some enforcement of that, rather than trying to validate this amorphous blob of JSON. Those are some rules of thumb. I don't think anything is a hard and fast rule. I wouldn't go all the way to having a primary key next to a blob, and treating it like a Mongo database.

I also wouldn't go all the way to just ruling out, explicitly saying, you can't have any JSON in the database. I think both of those are a little bit extreme, and a little bit silly, in my opinion. I would consider those rules of thumb when deciding should we break it out or not. Now if you end up in a situation where you've got a JSON Blob, and then later on, you find yourself querying into that blob quite often, that's okay. That is totally okay. There are ways to bring some of those key values out to top level columns.

We'll look at that here in a little bit, using something like a generated column. There are also ways to index the entire blob and specific parts of a JSON Blob or document. You're not going to paint yourself into a corner, so don't fret, but maybe consider those guidelines, and then we can adapt as we go.