Advanced Queries

[00:00:00]
>> Brian Holt: What if you wanna know the most favorited band? It's a pretty good one, right? SELECT, and we're gonna do COUNT(f.value), and we're gonna call favorites, value, AS count, and f.value, cool, FROM users, or, let's just do it this way, FROM users. And then we're gonna use something called a table-valued functions.

[00:00:41]
I'll be honest, again, I learned about this for this course, so I've not previously used these before, but I was just sitting there looking at the data and was like, what other interesting things could we derive from a table with this kind of information? And so there's a function here called json_each.

[00:00:57]
You give it the data and give it the value out of it. The reason why you have to query this as a table is because it's actually creating rows, right? It's actually gonna pull all of the tables out, and it's gonna make them their own row, whereas before they were not rows before.

[00:01:11]
So you have to essentially add to your result set. And if you hear that, like, hey, I'm adding things to my results set, it's probably a table-valued function.
>> Brian Holt: Favorites, and I'm gonna call that f, right? That's where the f.value and the f, yeah, all that stuff is coming from.

[00:01:33]
>> Brian Holt: And then, after I've done that, I can just do a group by, right? GROUP BY, f.value and then ORDER BY, count DESC. Hopefully that works. So you can see here, 4 people liked Radiohead, 3 people liked Daft Punk, and then a bunch of people liked 2, and a bunch of ones.

[00:01:58]
>> Brian Holt: Pretty cool, though, right?
>> Brian Holt: So I left a link in here, if you wanna learn more about table-valued functions. It's one of the things I like about, these are obviously very technical docs, but I think they do a pretty good job of taking pretty serious technical topics and making them as accessible as database language can be.

[00:02:27]
>> Brian Holt: Okay, again, I don't too commonly use them, but it's cool to know that they're there. Another thing I wanted to know is how do I add someone to my favorites? This is one that, it took me a bit of spinning to figure out, but remember whenever you use one of those JSON functions.

[00:02:46]
Because it's just a string and it's not really an object, it's not like JavaScript where you just add to the object that works, right? You actually have to create a new object and reinsert it back into the database using a replace. So, let's do that. We're gonna do a UPDATE, users, SET, data =, and then we're gonna do a json_insert.

[00:03:15]
>> Brian Holt: Let's do this on its own line. And then what I'm gonna do is, I'm gonna do a subquery, SELECT data FROM users WHERE email = 'brian@example.com'.
>> Brian Holt: That's gonna give me back the actual current jsonb representation of the user brian@example.com, right? So that's the first argument in my json_insert, okay?

[00:03:51]
I'm gonna go and I'm gonna grab favorites.
>> Brian Holt: And then I wanna put it on the end, and this actually wasn't possible until quite recently. You actually had to pull out the entire array and reconstruct the entire array. But they added this thing here where you can just put a # here and that just says, tack it on the end, right, which is much nicer to do.

[00:04:15]
Okay, and then I wanna add The xx, which is sort of my other favorite bands. I'm gonna close that. And then now we're just gonna say WHERE email = `brian@example.com`, okay? And now I guess I could have done returning, right? But now let's just say SELECT,
>> Brian Holt: *FROM users,
>> Brian Holt: WHERE email = `brian@example.com`, you know what I did?

[00:04:57]
I didn't even realize that. I did json_insert, which is actually not what we really wanted to do. We wanted to do jsonb. So now notice this is actually, it looks JSON now, right? One of the dangers of flexible typing.
>> Brian Holt: And the fact that these are just strings, they're not actually really JSON objects.

[00:05:28]
>> Brian Holt: How would I fix that? This is almost close to what you would need, just not a,
>> Brian Holt: So get rid of this, get rid of that,
>> Brian Holt: And I think you just say jsonb, right?
>> Brian Holt: Yolo, when you send an SQL query, you have to yell yolo, or it doesn't work.

[00:06:08]
>> Student: [LAUGH]
>> Brian Holt: Cool, all right, so now this is actually correct where you really want it to be. BrianGlasstGHolt, that's really my name. I didn't know that but SQL knew. You are all now JSON Jedis, as they call them.