JSONB

[00:00:00]
>> Brian Holt: So everything that we just did, let's go back to this, that's just the select JSON that I did here. Or maybe I'll just go grab it. Yeah, let's just go grab it from here.
>> Brian Holt: So, so far, we've just been running this query, the JSON, right? And we get back this whole object.

[00:00:23]
This is actually literally how it's being stored inside of SQLite. It's literally just a string that's a valid representation of the JSON object. There is a better way, okay? So if I put JSON and then I put B here, the B stands for binary, you get back this kind of mangled looking string.

[00:00:45]
And this is how you should actually work with JSON in the context of SQLite. For that matter, works in Postgres as well, both of them. The JSONB data type is superior. Compact sounds more smalley, I think that's probably the biggest advantage in SQLite. And I believe there's actually advantage too that it executes faster on those queries as well.

[00:01:09]
At the cost of not being human readable at rest, right, how it's stored in the database is a little bit more mangled. I probably could figure out what this all means, but I don't want to, right? So you can use JSON to just the normal JSON one to kinda mess around so you can read the output of it.

[00:01:32]
But when you actually go to store something, make sure you're doing it in JSONB, it's just always better. I believe the advice is the only time to use JSON is either when you're trying to do stuff in a human readable way within the context of your database. Or, I think, if you're just reading and you just want raw JSON out of your database, and you don't wanna do any of the accessors or manipulation of JSON objects.

[00:01:57]
You can store it directly in JSON and then you don't have to do any sort of conversion when it comes out of the database, right? That would be the other reason to use JSON, otherwise, 100% of the time you want to use JSONB. But notice everything still works, right?

[00:02:16]
>> Brian Holt: If I do that, everything that I just taught you 100% of it all works with JSONB. I think you can go in and do,
>> Brian Holt: Yeah, all the other functions. Let me just validate if that's the case. But if we go grab one of these, let's do this one.

[00:02:41]
There's a JSONB, replace JSONB, remove JSONB insert, I think.
>> Brian Holt: I don't think this will work, because that's not a JSONB object, it actually might just work, let's just with a JSON. No, look, it did work, and it gives you back JSONB, cool, right? So all of those still work, you just add the B instead of just the JSON.

[00:03:08]
>> Brian Holt: Cool, so that's great. Let's go make a user table and start writing some queries, yeah, do some real life stuff. I made a table here for you with just a bunch of JSON, notice they're all using JSONB, just copy and paste all that. Again, nobody wants to watch me write any of that, and I frankly don't want to.

[00:03:33]
Okay, this just created a table called Users, and it has email and data. So email is gonna be their email address that we can query by it. And the data is going to be all the data in their grab bag of data, right? And you can see I added admin on some of these.

[00:03:54]
So there's kind of a grab bag of various different properties of them, names, and then they have various different favorites.
>> Brian Holt: Now I will tell you one of my favorite things about GitHub Copilot is it's really good at generating test data, right? I just typed into Copilot, it generated me data that looks like this and has so many things and give me some variants, and it just generated all of that for me.

[00:04:20]
I used to type this stuff by hand. The one that I did for the complete intro to SQL, I wrote the entire ingredients database by hand. That took me two days of just writing stupid, repetitive queries, right? So anyway, that's my biggest plug for AI encoding is that it's spectacular at that and terrible at a lot of other things.

[00:04:38]
So now we have a whole database. So if I say SELECT data from users, what do you expect to get back? Whole mess of stuff,
>> Brian Holt: Right, this is JSONB representation of all of our data that we inserted into that. That's good, that's exactly what we want, this is not meant to be human readable.

[00:05:01]
And my biggest plea for you is never, ever, ever try to directly manipulate the JSONB format, right? Don't try and go in there and manipulate Led Zeppelin to be something else because you are going to get it wrong, and it's using special characters and other stuff like that, you're just never gonna get it right.

[00:05:19]
Always use the JB within a context of SQLite, please, as you're potentially being my future coworkers, I beg you.
>> Brian Holt: So what happens if I wanted to get everyone's first and last name from this table?
>> Brian Holt: SELECT data 'name'.
>> Brian Holt: One more time, 'first,
>> Brian Holt: data 'name',
>> Brian Holt: 'last' FROM users.

[00:06:17]
>> Brian Holt: What's going on there?
>> Speaker 2: You didn't close the single quote on first.
>> Brian Holt: Dumb, let's try it again.
>> Brian Holt: You were correct, all right, let's try that again. SELECT data 'name'.
>> Brian Holt: Double there, 'first' data 'name' 'last' FROM users, there you go. So your queries are just gonna end up looking like that.

[00:06:56]
You just have accessors directly and where the ones you're getting out of there. This comes back as some weird mangled name. If you pass it back to nodes you'll do it like as this, right, as first, as last, right? And then this will be named more appropriately for what you're querying.

[00:07:13]
So what if you want to find less engaged users, right? You would look at your users table, you would look at the favorites array, and you'd find people that have one or zero favorites in your table, and you'd identify those as less engaged users. You could write a query where you essentially use the JSON link to get that.

[00:07:36]
So I have that same query that we were running before, which is this one.
>> Brian Holt: Okay, that's giving us all of the users in our table, and all we have to do is we say WHERE json_array_length. We don't have to do json_B here, cuz json_array_length can take json_A or json_B Data.

[00:08:00]
The name of the column and the thing that we want out of it, and we would just say where that is less than 2.
>> Brian Holt: And it's just Bob Smith. So we can send 1,000 emails to Bob saying hey, Bob, like something else.