So, three major concepts within repeated fields. There's those two data types that I alluded to way early on the specialization that maybe new to a lot of you called arrays and structs. And these are general concepts in SQL even outside of BigQuery and they're natively supported on the basis of repeated fields as we get into, and we'll practice that a lot. And if you are a veteran to BigQuery, there is actually a different way that standard SQL handles what's called the flattening of these arrays as opposed to legacy SQL. We'll cover that very briefly. And last but not least, we'll get a lot of SQL practice within these repeated fields as well. Let's jump right in. So first and foremost, let's introduce what arrays and structs are. So I'll start very simply. So if you haven't seen arrays before, it is a data type and it is an ordered list of zero or many data values, but the key caveat is they'll often have the same type. So here, if you wanted an array of fruits, you could have your raspberry, your blackberry, your strawberry, and your cherry, almost got all berries there, and how that actually is stored, an array is stored within those brackets and each individual element of the array is stored with a comma in between them. And that's all just one field value. That's actually what could be returned. What that actually looks like, if you're going to select that inside of SQL. So if you actually ran that query on the left, you can create an array yourself. So you create it with those brackets and you say, "Hey, select this field and in this field, have it be this array: raspberries, blackberries, strawberries, and cherries." Look at what the output you get is. And again, it's that one long row. So this is in four rows. So it's actually four pieces of data in one. So instead of displaying the results as bracket, raspberry, blackberry, strawberry, cherry, and bracket all in one row, BigQuery naturally flattens that out into what looks like four separate rows but is in fact what we call nested rows. And as part of BigQuery standard SQL, it'll naturally flatten those rows output for you. So we're going to start with some of the basics with these arrays. You want to keep building on these concepts and why this is such a big deal when it comes to having this concept of nested rows. So we've got arrays down path. It might look strange again like that row is very large vertically but just keep in mind, you can actually have what seemingly is multiple data values within one row through the use of an array. Okay, some cool operations that you can do on the size of the array, for example, if you wanted to get the number of items in your grocery cart, if those four were your fruit shopping for the weekend, you could get a special array function in there called the array length and you can get the total size of that array. And here again, we're just using a quick common table expression just to get some of that data in there. We're not pulling it from any particular table. And you can run these queries yourself from the currency value. So there's particular array functions for accessing the contents of the array and you can manipulate each of the different elements in the array. There's a lot of different functions that you can imagine you could do on top of arrays, like sorting or filtering which we're going to cover in a few slides. As we covered a little bit before, BigQuery will implicitly flatten out your arrays. So what I mean here is if we had Jacob's order in the grocery store and it was an apple, a plum, and a pear, instead of having that comma separated, like you saw and that's how you created the array on the left hand side, instead of having apple comma pear comma plum in the output, BigQuery will kind of what essentially is pivoting it which we call flattening it. And you'll see again that suspicious looking gray space beneath Jacob because Jacob is essentially that parent record where you have a lot of those child records, the items that he has associated with them, and that again is all stored within one table whereas normally, this would be split off into like an orders table or a customers table and a details table. And let's keep moving along. Okay. So how do you access elements in that array? In order to do that, we've used the function that's actually called UNNEST, and it basically says, "All right. Well, I need to do some work on this array if I'm going to join it or if I'm going to filter on it." And basically, what that's doing is it's saying, "All right. Jacob, which of these items are associated with you. You've got the apple, the pear, and the peach." Okay. Let's unnest all of those items and then get them out on three individual rows. So instead of having everything in one row, you now have three separate rows where they all each individually belong to Jacob and that allows you to perform those operations like filtering. So if you wanted to filter for just a pear or a peach, that's where you're using your WHERE clause now because that filter's on rows because you now have three rows instead of one. And the syntax for that is on the left. You're selecting the items, the customer name, and then you're unnesting or unpacking that array there, and you actually UNNEST in conjunction with that cross-join. So if you remember, I've probably scared you a lot with the unintentional cross-joins. A cross-join is just an artifact that we need to use here to pretty much copy Jacob's name an additional two times to get it associated with all the items that he's ordered. So going back to the previous slide very quickly, you'll see it's just Jacob and then two blank spaces beneath that essentially. But again, keep in mind it's conceptually just one row. Here, we have Jacob, Jacob, Jacob and that's in an effect of because we broke open that array into three separate rows again and we needed to break open the customer name to match and that's why the cross-join is there. Okay. Let's keep on diving down into the rabbit hole of arrays. Some useful things that you can do if you wanted to pack everything back up. If you wanted to create an array from a field that you already have, so say you have the dataset on top: apples, pears, and bananas. If you wanted to pack that all into a fruit basket, you could use something that's called an array aggregator or array aggregation function. And what you do there is just passing that field and boom, those elements come into an array itself. So if you have data tables that you eventually want to get into what you will see in a minute, this parent-child nested structure, the first step is potentially aggregating those elements into an array, so one row instead of three. Another nifty function that you can do much like you saw in the advanced functions example where we're sorting those partitions, you can sort the elements in an array but just by ordering it inside of that function. So you have an array aggregation function and you basically say, "All right. For this new array that I've just created order it alphabetically." So apple, banana, and pear. Okay. So let's get a little bit more complex. So, we have three shopping lists up top. Person 1 has apple, pears, and bananas. Person 2 has a carrot and an apple. And Person 3 has just ordered water and wine. So we want to find all the different shopping lists or shopping carts where there is an apple present. So in order to do that, what we need to do first is set up our data as three separate arrays. That's how we got that data up top and that's that first query. It's just a simple with statement that's just says my data is three arrays union together. There's nothing special about that one. Now on the bottom here, we're specifying unpack each of the different grocery lists. Again, we have to unpack them by using the UNNEST, and that allows us to use these operators and filters as you see with that WHERE clause. And how you filter that particular array is by, it's a little bit counterintuitive, you have where apple is in that particular list. So whereas you might have expected like where the list contains apple or something like that, you actually have what you're searching for as first. And then at the end of the day, we pack it all up into an array as it was before. So you open the box and look at all the items that are in the box and again, that's by using UNNEST function there, and then we pack it all back up by using the array. And that's how you get your filter performed. Now, consider the results there. So you have apple, pear, and banana, and carrot and apple. Both of those arrays themselves are returned because they each contain apples. But you'll also see that the third array is returned but it is returned with an empty set or null, and that's just one of the natures of the arrays where it did not match. Okay. And the next big concept, so this is a completely separate data type from arrays but conceptually a little bit similar. It's a struct or a structure. And structs are absolutely flexible containers that, unlike arrays, you can actually store multiple different data types inside of that. So not just all string values or not just all numbers. Well, that's a limitation of an array. Instead of a struct as you'll see in this next example, you can actually store completely different data values. So now, it starts to get a little bit strange. So inside this one data field, we're generating these multiple columns. So this is interesting, right? So let's look at the two different struct values here. So we have an age value and a person's name and we specified two different values. So let's say 35 is an integer and Jacob there is a string and both of these are actually stored as a single struct. This is the single struct. And of course, you take a look at the results that you have and you have this weird column placeholder. So you have age and name as you see it there, but you see this weird kind of prefix there. What is that? So that's again, if you'd seen that before is because we didn't give it an alias. So struct is the container that you're creating. It's almost like a table. It's a table within your SQL query. So you give it a name and now you have something that looks like almost a drill down capability. So you have customers.age, customers.name, and you've got that all within that singular field, all right. So that's struct. That customer is struct. Kind of generated both those values for you. So it looks and behaves very similar to a table, and you'll see when you start mixing together arrays and structs, that the real magic is going to happen. Okay. So here is the mind blowing moment. As we mentioned, a struct can have many different types of data inside of it. An array is a data type. So you can actually have an array as part of a struct. So walking through our grocery store, example, you have Jacob as a customer. He's 35 years old, and these are the items that are in a shopping cart as the array. Now keep in mind the limitation in the array is that all the items in that particular field have to be the same data type. Not true of the other values in the struct like age and name. Again, age is integer. Name is a string value there. But look at, we're building what looks like a table with just these flexible containers and we have these repeated fields in the array. So now, you have an array nested in a struct. Let's get even weirder. You can actually have the other way around. You can have a gigantic parent array that can contain multiple structs as values. So this is what gets really weird. So let's break it down piece by piece. So again you create a struct with those brackets. So you're selecting bracket, a struct. So you have an array of structs. And that first struct is Jacob is 35 and then here are his items. So let's walk through. His items are an array of items inside of a struct inside of another parent struct. So look at that process. And as a second item in that parent array, and by the way that parent array is just called customers, we have another customer. And inside that customer is a struct. We have Miranda, 33, and then here the item that she ordered as part of her array. Now, a couple of interesting pieces to know, just in the example, we happened to have the same number of items in the array but you could have a differing amount. So we got water, pineapple, ice cream, and soda. So you got five or six or a hundred items for Miranda and just three items for Jacob. The child records can grow irrespective of what the other customer ordered as you might expect. So you're beginning to piece this together in your head. So you might even be able to start looking at what are some of the advantages of having this repeated structure of having multiple detailed records alongside lookup information but not having that lookup information and repeated unless you need it. And let's go one more layer deep. So now that we have this construct that you saw before, we just created it. So let's go back to review what we just created. We created this customer's shopping cart list combination. And now what we want to do is we want to filter for only customers who have bought ice cream. We don't even do any joints to do it. So that same code that you saw before, that generated the customers or in their orders, is copied over to this slide. And we just dumped it inside of a common table expression called orders. So orders was from the previous slide and now we actually went to operate over it. And as you saw with Apple filtering example is the exact same here. So we're saying, give me the customers. And the customers, again, is a struct. So you're just selecting what seems like one field down below there. Give me the customers and that will ultimately return those three fields because customers is a struct and it has customers.age, customers.name, customers.item. That's the power of structure. Give me the customers from the orders, and again orders is what we created before, and what I want you to do is apply this filter to only get the customers that have ordered ice cream. And the syntax may look a little shriggy here, but just know that this is part of the deal. So in order to unpack the elements in the array, we need to do two things. To unpack the elements in the customer's array, we first need the unnest it. Meaning who are all the customers we're looking at. Let's unnest that first array and, cool, we've got Jacob and Miranda. We've unnested the customers. And we also need to cross-join that again. So you can imagine on the right hand side, you have Miranda near the water, Miranda against the pineapple, and then Miranda on the ice cream. That is that cross join effect of splitting apart a single row into three separate rows before we pack it all together. So that's what the cross join does there. And then last but not least, we've already unpacked individual customers, but within customers there's yet that a secondary nested array which is the items. So we need to do another unnest and that's where we actually apply our filter. So all the way at the bottom, we say, finally I've unpacked and I can examine the customers. And now within the customers, they have their own shopping lists and now I want to see where ice cream is in the customer's items list. And, again, to look at that items list, we have to unnest it first. And when you actually execute this entire query, that's when you get the result all the way on the right. And, again, think of the real power here again is that query, you are just selecting customers. You're selecting what is technically just one field. It's just that field happens to be repeated because that struct in and of itself has multiple different values. And again, the crazy part is one of those values happens to be in array which has nested items inside of it. Okay. That's the big mind explosion moment. So let's do some recap just as far as we're going to go. So here's the big moment. So nested or repeated records are arrays of structs. So we just covered this because arrays. Right? You can think of arrays of structs. As we walk back through the previous example just to really solidify this. This is the critical point of this whole lecture. An array of a struct. So the array of customers, means you can have more than one customer. You've got Jacob and Miranda, and you have, within the structs themselves, you could have multiple different data types. You can have integers. You could have strings. You could have the arrays which then give you the ability to have those nested fields that we can talk about and unpack later. But that's the key message here. Those nested repeated records are just an array of similar values and those values happen to be structs, and structs are extremely flexible because it's almost like you can store a table inside of those struct. Very interesting concepts. And you contrast that. So this looks very similar to doing kind of a join on two different tables. But as you see in that second ball point, that join relationship is implicitly built into the table. So you have both the customer, which is generally just like in a separate table, and the details of what they ordered in the same table. And that's extremely powerful when it comes to performance characteristics. And, of course, as you saw in sequel syntax, you have to unpack the arrays using unnest. So let's actually jump into BigQuery and I'm going to break apart that example a little bit more and show you a little bit more syntactical sugar. Okay. So here's the example that we covered and this is just a good example to keep focusing in on. Let's see our data itself. So up at the top, we are selecting our customers. And our customers, it's just Jacob and Miranda and then here's the list of the things that they've ordered. And we're going to be storing that as their overall orders. So we have different customers which represent each. Line 5 is a customer because the entire array of customers is lines 4 through 7. Line 6 is another item in that array which is another customer. And customers themselves are of data type struct. And the struct is a bunch of treasure trove of great detail inside of it including customer details like age, name, and then a nested array that has the items that they bought. And this could go on. You could have 50 different fields inside of one of these structs as well. Okay. So now, how do we actually access it? So open up the customer's struct, take a look at the customers from what they've ordered, and then we need to, again, get it out of this single row format by basically unpivoting or cross joining those records through each other. So that allows us access to that array once we unnest it. So here's the reason why I brought up this example. I keep mentioning the cross join but you can actually implicitly invoke it through the use of a comma. So if the word cross join just spooks you out, it is actually not required as part of this syntax. You see the query editor is still valid. So if this makes a little bit more sense to you, you can actually select customers from the orders and unpack those customers and then unpack the items list within the customers, as your filter implies you're just looking for ice cream. I really don't want to show this but I will. If it is an array that you're unpacking, technically that unnest is also optional there. So you can actually have the cross join, it's just specified as that comma, and it automatically knows that it needs to unpack that array in order to actually access it. But what I always like to do, just for people reading my codes so they know that it is an array, I keep that unnest in there. Just when you're reading other people's code and you're saying, hey, you're missing a cross join or, hey, you're missing an unnest, just keep in mind that some of those are actually optional. That's the mind blowing syntactical sugar of cross joining in this.
