In this module, we're gonna cover everyone's favorite data interchange format, and that is of course, JSON. If you don't have a favorite data interchange format, I would ask you to consider making JSON your favorite. But if you don't have a favorite, you probably have friends and hobbies and other stuff to do with your life, so that's great. We're gonna talk about JSON, though I have no problems with storing JSON in the database.

I love storing JSON in the database. Here's my only charge to you as you're considering storing JSON in the database. If the JSON has a well-defined schema, and it's something where you're going to be querying into that JSON object pretty often, or updating that JSON object parts of it, pretty often, I would recommend breaking that out into top level columns in your database. Every database that supports JSON, which is basically all of them at this point, all of them have affordances for doing things with JSON, but it's way better to do things with columns, indexing, querying, updating, all of that is gonna be easier if it is a top level column. So keep that in mind as you're thinking, well, SQLite supports JSON, so I'm just gonna turn it into a NoSQL database and just put everything in a JSON blob, not my recommendation. In this module, we're gonna cover some general JSON things and then some specific JSON functions.

First, I wanna give you just a little bit of an overview of JSON'S history in SQLite. I think it was first rolled into the default amalgamation in 3.38, so not that long ago, depending on when you're watching. And as of 3.45, there is a new set of functions called the JSONB functions that is new as of January of 2024. And the JSONB format and functions are modeled after Postgres. So Postgres has a JSON column and a JSONB column. SQLite does not have JSON columns, but it has these two data types, JSON and JSONB. We're gonna cover this in an entire video, but they are two separate data types and the JSONB preserves that binary structure on disc so that it doesn't have to continually be reparsed into a binary structure from text.

We'll cover that in a separate video. There are three types of JSON functions. There are scaler, JSON functions, and those return a single value. There are aggregate JSON functions and those can help you aggregate across maybe JSON arrays or something like that. And then there are JSON table functions, which are very, very interesting because where a scaler or an aggregate returns a single cell, a single row column combination, one value, a JSON table function can return an entire table. So it can return a set of rows and a set of columns, and then you can operate on that just as a normal temporary table. And so that can be very cool if you're doing some more intense parsing processing of a JSON object and you need to go from JSON to a table and then use SQL and then put it back in JSON. You can absolutely do that. So we're not gonna cover every single JSON function because there are quite a few credit to SQL Light, but I will leave notes below, or I'll leave a link below in the notes where you can find the JSON docs. But for now, let's move on and talk about JSON versus JSONB.