JSON Extension

[00:00:00]
>> Brian Holt: So I showed that to you just so you can go play around with all these if you're interested in playing with, I don't know, Base64 encoding or anything like that. But we are gonna use JSON1, which is, yeah, I'm gonna go ahead and say it's the most useful of these, or probably the one that you'll end up using the most.

[00:00:19]
>> Brian Holt: So let's go to that one. So sqlpkg install json1. Okay, so we're going to install that. I already have it installed, so you'll see this at the latest version. Here, you should see that it'll load. And we will grab the which here, so we can get the path.

[00:00:38]
Very good, we have that. I'll copy that. We're gonna do edu-sqLite 3, and we're gonna open data.db, and then we're gonna do .load.pathway here, just like we did with the hello one. Okay, now it's all loaded, now, we have our JSON extension loaded, and we can start making queries.

[00:00:58]
Why do we care about the JSON extension? Why is this an important one? Why do I think you should probably be using this quite a bit?
>> Brian Holt: I think it's important because it allows you to have unstructured data and still be able to use SQL. So kind a Mongo, where it's just kinda a grab bag of various different keys and values.

[00:01:16]
You can do that with an SQL, you don't have to have a rigid column for everything. This is how you end up 2000 columns in a database, if you have a bunch of different potential keys. 30 of them are null, right, and the rest of them are sometimes populated, sometimes not.

[00:01:31]
If you find yourself in that pattern where, you're taking metadata for media, maybe, for an example, and if it's a movie, it has a director, if it's a track, it has an artist, right, and a producer, and blah, blah, blah. There's, this sometime has this, this sometimes has this, and you're trying to mix them together.

[00:01:51]
You don't wanna have 2,000 columns. What you wanna have is a way to have unstructured data that can sometimes be defined and sometimes not. Normally, you would reach for Mongo in that kinda situation, but now with PostgreSQL and JSONB, both in PostgreSQL and MySQL and now in SQLite, it's a much better pattern and it's more accessible within those databases now.

[00:02:15]
But .load is how you load an extension. That's just what it's there for.
>> Brian Holt: So you might be wondering, how do I load this within a Node.js scenario or something like that? Because I don't think the dot notation is gonna work from the clients. I've never tried. One might try.

[00:02:37]
But you can compile SQLite with the JSON extension pre-built in so you don't have to do it every single time. Anyway, yeah, well, it would be very easy to just say load sqlite3 with extensions node.js or something like that. And I'm sure at the top, I think, a Stack Overflow, it's something like this, right?

[00:03:04]
There you go. It's db.loadExtension is exactly what you would do. So there you go. So that would work. You can also pre-compile it. There's a bunch of ways to go about handling it. So would you version control the dependency in your repository, or would that be installed and it would be get ignored, like any other software dependency?

[00:03:27]
Yeah, the question is, do I keep it in source control or do I not? I would be sorely tempted to just keep it in source control. It doesn't update that frequently. Reliably, get the same one every single time. It's not a trillion files, it's a couple of files.

[00:03:42]
And SQL package, I don't think this has a manifest that you're able to install. Maybe it does.
>> Speaker 2: Thought I saw a lockfile at the bottom of the documentation, but I didn't-
>> Brian Holt: Lockfile.
>> Speaker 2: Really dig into it.
>> Brian Holt: Neither did I, cool. Well, if that works well, I might be tempted to try it.

[00:04:03]
Good question. I will say, historically, I think I've curled it and just pulled it down, which is probably not the best way cuz then you have this point of failure if it's not working. But YOLO works on my computer, so source control seems fine to me. Typically, I'm not a person to put generated files into source control unless it's something like this where it changes so infrequently that you wouldn't be remiss to do so.

[00:04:32]
So I will say of the two comparing SQLite's version of the JSON kind of extension and the one that's built into Postgres, I prefer the Postgres one. I think it just makes it a bit cleaner. This feels kinda tacked on, and that's because it's kind of tacked on, whereas it's a core feature of Postgres now.

[00:04:52]
That being said, they both work, so. So we're gonna do that same pattern of just like using SELECT run functions, cuz I just want you to see how to formulate all of these various different queries. So SELECT json, and it's just a function that you give it a JSON string, and it will give you back a JSON representation of that in SQLite.

[00:05:15]
So it's kind of awkward to type out, and I would suggest all of you just copy and paste this, because, I don't know if you've ever tried to handcraft JSON before, but I always get it wrong the first time, because I get the wrong quote, the wrong curly brace.

[00:05:27]
I just never get it right. username,
>> Brian Holt: btholt, and then I'm gonna do "favorite".
>> Brian Holt: So we're kinda gonna start modeling users table. Maybe we have some sort of ability for people to log into our site and click favorites of their band so they get notified of new releases or something like that.

[00:05:55]
That's kind of what we're gonna model here with the JSON extension. Mostly, why I would choose JSON in this particular case is now I can start modeling favorites as an array of their favorite artists rather than have to have another table of their favorite artists. To be super frank, given the structure of our data that already exists where we already have an artist table, I probably wouldn't model it this way, or I would rather collect IDs here, like 115 or something like that.

[00:06:25]
That's kind of up to you.
>> Brian Holt: But it does make it easy to have relations without necessarily having to have another table with it. What's hard though is you can't make these foreign keys here, right? So you can't have any sort of protection that foreign keys should guarantee you.

[00:06:46]
Cool,, so I did that. Close, close, close that. Let's see, I always mess it up. Nope, that was the wrong one. No, that was correct. Never write JSON by hand. It's embarrassing, username-
>> Speaker 3: Closing paren, right?
>> Brian Holt: Is that what it is? Yeah, you're right. You're absolutely right.

[00:07:15]
There you go, and now I have a full representation of this in JSON. Now you might ask me, what is the actual data type being stored here? And if you guessed string, you were correct. It's just a string. It's just a JSON string that you're putting in there.

[00:07:34]
However, well, we'll get to that here in a little bit when we start talking about JSONB. So what's nice about this, though, is once you pass it into the JSON object, it's then ready and available and validated to be able to be accessed and queried and stuff like that.

[00:07:51]
So you'll find yourself using this JSON function to craft things. The key here, when working with this extension, is you wanna use the built-in functions here to make sure that all of your data is formulated correctly. Because right now, we're using this JSON function, but we're gonna get to another one that's actually a binary representation of JSON, and it's not stored the same way.

[00:08:11]
It is still a string, but it's compressed. You'll see when we get there. Let's do an array. So this one's helpful. Craft me an array using JSON, and you just give it a bunch of pieces of information. And as you might guess, it gives you a well-formatted array back.

[00:08:31]
That's good. I'm gonna take this one, this function. I don't wanna write it again. No one wants to watch me write it again. And we'll just talk through here, what's in here? So json_array_length, as you might imagine, you might want to get a length back from array. You wanna see how many favorites Brian has, so that you can render the UI correctly, or something like that.

[00:08:54]
So the first part of the function here is the actual piece of JSON that you have, and then the second thing is the selector that you want. So dollar sign represents the object itself, right? Then period is the accessor. And then you say, I know favorites is an array, give me the number back of how long that array is.

[00:09:11]
So what do you expect to get back here? Here's favorites, there's Daft Punk, Radiohead, 2, right? There you go, 2. JSON type, if you wanna get back type information, obviously, this is not gonna give you back useful type information as is. So what happens if you wanna get the username?

[00:09:36]
Type back. So again, json_type, json_string, same one, and I say $.username, and we expect to get back what? String, I think. Text, there you go, yeah. What happens if we do, I don't know if you can even just do dollar sign, yeah, object .favorites. It's an array, and I think you can even just say that, and that should give you as well text.

[00:10:09]
What's happens if we give it 3? I have no idea. Nothing, it's not anything.
>> Brian Holt: Says json_type, again, that's not what I typically use a bunch, but it's there. You could definitely use it if you want to.
>> Brian Holt: This is kind of an interesting one. SELECT json_object, and you just give it pairs of things.

[00:10:36]
So if I wanted to basically recreate this one, I would say json_object ['username, and then whatever comes next is what's gonna be paired with it. So I'm gonna do btholt. Then I can do another one. As you might guess, I would have to do favorites.
>> Brian Holt: And then I would have to do a json_array, right, so I can embed that here, of Daft Punk and Radio Head.

[00:11:08]
Okay, close that and close the other one. And I think that should be good. And you can see here we get the same object back out. They just have to be in pairs. Maybe it'll just give it nothing. I actually don't know if you don't pair it correctly, what happens.

[00:11:28]
>> Brian Holt: Yeah, so it has to have an even number of arguments, otherwise it won't work. Could be helpful. I'll confess, I learned about this when I was writing this course, so I have never used it, but it is there if you need it. I could see this being useful with subqueries where you're just kinda putting subqueries to put things together.

[00:11:43]
That would make a lot of sense to me that that's how that would work.