Creating an Array from a Subquery

[00:00:01]
>> Let's talk about subqueries and arrays. So, I showed you one more, it's really simple. You're making a subquery, it's going to return exactly one ID, right? And that works because there's just one result. What happens if there's a set that's coming back? Well, you can do that.

[00:00:24]
There is a data type inside of Postgres called array, which we can use to model a set of results. So let's say for example, you wanna see all the keywords associated with any Star Wars movie. You could do joins, you could have rows, you could do rows per keyword, but that would be a really annoying.

[00:00:42]
It'd be a very, you'd get into a pretty hairy query pretty quickly, hairy query. [LAUGH] I don't know why this never occurred to me before, I'm sure that's not funny to someone else but at this time of day it's very funny to me. Anyway, hairy queries, You're gonna end up with this mess and you're gonna have to do some aggregation, it's gonna turn into a mess quickly.

[00:01:09]
A subquery with an array constructor can do that a lot better. So let's go ahead and write that all together. I'm gonna do it over here because it's gonna be just easier to look at. So we're going to select, I'm gonna select m.name for movie name and then we're gonna make an array here.

[00:01:29]
This is an array kind of constructor sort of thing from Postgres. You're going to give it a list of rows and it's gonna turn that into a data type that it can use. So, I'm going to select English category name. FROM, english_category_names, that's the name of that view that we made in the previous, Lesson.

[00:02:05]
INNER JOIN, Movie_keywords mk ON mk.category_id = ecn.category_ id, cool. WHERE, not white wine vinaigrette, WHERE m.id equals mk.movie-id. Now notice the fascinating thing here, I have not referenced movie in my subquery. It's gonna be outside of the subquery that I can reference it in the subquery, which that's totally okay.

[00:02:47]
Totally kosher to do. And I'm gonna give it a limit of five. I don't want all of the categories I just want the first five, so I'm gonna give a limit of five here just to make this a little bit more tame, and then I'm gonna call that keywords.

[00:03:07]
And then I'm gonna say, I want that FROM movies m, WHERE name ILIKE, star wars. All right, I'll come back and recap this, but let's run it make sure that I'm not off my rocker more so than normal. So you can see here I have Star Wars, A New Hope.

[00:03:41]
And it has the first five keywords associated with it, Prophecy, Bauernhof, obviously, Orphan, Good and Evil, Android. Empire Strikes Back, Spoiler Alerts, Father and Son Relationship, Rebel, Bounty hunter, Monster and obviously, The classic Shnee, which you do not know what that means. I'm assuming it's German [LAUGH]. You can see Clone Wars here, this is probably the TV show has no categories associated with it but Empire Episode 1, Die dunkel Bedrohung has Underwater and Warrior associated with it.

[00:04:27]
So first of all curly braces here that means array in Postgres terms. Some of these that only have one word are, don't need quotes, the ones that have to words are right and there's spaces in there, they put quotes around those. And that's what this is, this is an array that we're returning as one top level data type.

[00:04:55]
And again, you can mess around with this, if I did this with three, it would obviously give me less keywords. All right, so now I'm getting less keywords. Yeah, and again, to call attention to that we have WHERE m.id = movie_id. Notice the movie here is coming from movies here, not from the subquery, but from the outer query.

[00:05:21]
Totally okay to do it that way. These kinds of queries can get really expensive cuz you can think like, this is not cheap. This has an inner join in it, right? And you can quickly spiral out of controls of doing subqueries within subqueries within subqueries. It depends if you have, a performance tolerance for that.