Join Together Two Arrays in GROQ
GROQ enables you to join together arrays of JSON objects by looping through arrays inside of projections and using the in keyword to match items based on values. This lesson uses Pokemon to demonstrate matching a base Pokemon with the Pokemon's evolutions from a Pokedex dataset.
John Lindquist: [0:00] The more advanced queries have to deal with joining two sets of data together. Let's clear out some of this. Just get five of them for now. Look at the IDs and names, and just check that's working.
[0:12] You'll see that these Pokemon have a property called next.evolution on them. If I run this and we check out the next.evolution, you'll see it's an array with a num and a name. If we check each of these, you'll see that each Pokemon also has a num on them. This is num 001, this is num 002. This 002 is one of the evolutions of 001. You can see it listed in the next evolution.
[0:45] It'd be really cool if in each Pokemon, we could also grab the data for their evolutions. Not just the name, but also the height, or any other data we want. What we can do here is, let's create this property called evolutions. On this property, we're essentially going to go back to the root scope and search through all of them. If I were to run this right now, it would just drop an array of every object, every 151 Pokemon.
[1:13] I'm going to run it just like this so you can see it. You can see that on each of these, it has evolutions and it has the one through six IDs, with all the Pokemon we've seen before. We've taken all the data at the root and just dropped it into this evolutions property.
[1:28] To do this, we're going to match up those nums, so match up the num from here, on this scope, to the num on the root scope. We'll start with the root scope and take the num from there. Again, we're on this scope, so this num refers to each num on each Pokemon in the root scope.
[1:47] We'll say, num IN, and we want to match up against the next.evolutions property we were just looking at. That one is in the parent scope of this, and the parent scope uses the caret. We say, lookup one scope for those next.evolutions. That's an array with nums on them.
[2:11] Let's run this real quick and see what we got back. On our Bulbasaur, we got evolutions of...The first object is this Ivysaur, and the second object is this Venusaur. The reason they matched up is because we looped through the root scope, looking for nums that existed in the scope of this Pokemon, on the next.evolutions, and those all have nums in them.
[2:40] Let's format this a little bit just so you can see more of what's going on. We don't have to limit this since we're already limiting up here.
[2:50] We can work with our data again where we can get things like height on the main Pokemon, and then when we check the evolutions, we can get their name and height as well. I'll run this and you can see we have the Bulbasaur. It has some evolutions with a name of Ivysaur, and the new height, the name of Venusaur, the new height. That continues on down in each of these matches.
[3:14] The Ivysaur only has one evolution up to Venusaur. The Charmander has two evolutions. The Charizard has no evolutions, because using that same in keyword and using a lookup to the parent scope, we can join based on keys. If it helps make more sense, we can actually just take this query, remove everything, paste this query in here and that's just our filter.
[3:40] We could run this and get the name and height of every Pokemon. It's just that the filter we're using is checking all of the nums against a next evolution in the parent scope up above this, which doesn't exist right now.
[3:54] This wouldn't work, but if we take this and we drop it back into this query, where now our parent scope is this Pokemon and we're checking these evolutions, now this query or subquery works just fine. It joins our data together in exactly the way that we want.