WEBVTT 00:15:40.000 --> 00:15:51.000 And, uh… you can do these, uh, Jason extract path statements to get out those various things. 00:15:51.000 --> 00:16:07.000 Um, the very first level. um, is… is easy peasy, right? You're you're just going to be able to get the the value for ID or the value for. 00:16:07.000 --> 00:16:17.000 Um, barcode, I think, in this particular example. They are just at the top level. So you don't have to dig very far. 00:16:17.000 --> 00:16:18.000 Joanne? I just want to check in, because, um, 00:16:18.000 --> 00:16:21.000 Um, yes. 00:16:21.000 --> 00:16:24.000 I'm hoping that everyone 00:16:24.000 --> 00:16:26.000 is with us here. 00:16:26.000 --> 00:16:28.000 And just want to ask, 00:16:28.000 --> 00:16:35.000 Uh, has everyone looked at the tables in MetaDB closely enough to understand 00:16:35.000 --> 00:16:37.000 what we're referring to. 00:16:37.000 --> 00:16:40.000 When we're talking about these various 00:16:40.000 --> 00:16:48.000 tables. So, if I… I often give the example of the loans table collection, so you'll have… 00:16:48.000 --> 00:16:53.000 loan underscore underscore T is the current 00:16:53.000 --> 00:16:55.000 loans, information. 00:16:55.000 --> 00:17:00.000 And then loan just underscore underscore, or you could just cut it off as loan. 00:17:00.000 --> 00:17:02.000 is the… 00:17:02.000 --> 00:17:05.000 table that has all the… 00:17:05.000 --> 00:17:08.000 data to be extracted, loan… 00:17:08.000 --> 00:17:13.000 underscore T underscore has the current and the historical information. 00:17:13.000 --> 00:17:28.000 Is everyone on board with that, or do you have any questions about that? I'm just wondering if it would be helpful for us to back up a little bit. 00:17:28.000 --> 00:17:31.000 Okay, once this one… Juan's got a thumb up, okay. 00:17:31.000 --> 00:17:32.000 Okay. Ugh! We got a fancy thumbs up. 00:17:32.000 --> 00:17:35.000 Okay, Juan is giving a thumbs up. Let's see, who else do we have? Okay. 00:17:35.000 --> 00:17:38.000 Okay. Okay. 00:17:38.000 --> 00:17:39.000 It's… 00:17:39.000 --> 00:17:42.000 All right. Please do feel free to interrupt if. 00:17:42.000 --> 00:17:49.000 What I'm saying doesn't make sense, or if you need some background. 00:17:49.000 --> 00:17:50.000 Yeah, we… 00:17:50.000 --> 00:17:53.000 data, information, whatever. I'm I'm sorry. This is a kind of a complicated topic. 00:17:53.000 --> 00:17:55.000 Yeah, yeah, that's why I wanted to just open that up, because… 00:17:55.000 --> 00:18:02.000 Yeah. 00:18:02.000 --> 00:18:03.000 Yeah. Sure. 00:18:03.000 --> 00:18:10.000 Um, if you're like, I don't know what she's talking about, you know, we can show you the tables in a MetaDB environment, so you can relate to that a little better. Um, but if everyone's like, oh, no, we know exactly what you're talking about, which is 00:18:10.000 --> 00:18:16.000 what I'm getting a sense of, so… 00:18:16.000 --> 00:18:27.000 Okay, so, um, anyway, I was saying that the first level extractions are really easy, because all you need is. 00:18:27.000 --> 00:18:33.000 just your basic… Jason extract path text. 00:18:33.000 --> 00:18:40.000 And then this this thing called active. That was a first. That was a top level field. 00:18:40.000 --> 00:18:56.000 So all you need to extract it is this one expression, JSON extract path text. And then the table, you specify what the table is, users, and then you specify what the field is that you're looking for. 00:18:56.000 --> 00:19:08.000 And that's the thing called active. That's a Boolean field, so it's a true-false type of thing. And and then you just say from the users table, right? 00:19:08.000 --> 00:19:27.000 And the other way to write this, if you're lazy like me, and you don't want to write out Jason extract path text. You can use this operator that I mentioned earlier. You can just say from the users table, go to the Json array. 00:19:27.000 --> 00:19:35.000 and then get… the active field, and that's what this little shorthand stuff is. 00:19:35.000 --> 00:19:44.000 Okay. Now, a lot of times you're not so lucky and you need to extract more than just a top-level field. 00:19:44.000 --> 00:19:57.000 This is an example where if you looked back at the users table, you would see that there is a chunk in that Jason Ray called personal. 00:19:57.000 --> 00:20:10.000 And within that personal chunk is where you get your first name and last name. So that was one of my gripes early on when I was trying to get user. 00:20:10.000 --> 00:20:23.000 info for a lot of my cert queries. And I couldn't use the extracted table, the the t tables, users T. I couldn't use that because it didn't have it. And the reason why it doesn't have it. 00:20:23.000 --> 00:20:29.000 is because the transform tables only extract the very first highest level. 00:20:29.000 --> 00:20:44.000 data. Okay, so if it's embedded like on a second level or third level, or whatever hierarchy. It's not going to get it. So you're not going to get it from the t tables, and in a lot of cases you're not going to get it from the derived tables. 00:20:44.000 --> 00:20:50.000 So I had to figure out how to get it myself. And this was how. 00:20:50.000 --> 00:20:55.000 You go to, you have to go JSON extract path text. 00:20:55.000 --> 00:21:02.000 You go into your users table, and then you specify, go down to the section called personal. 00:21:02.000 --> 00:21:08.000 And then get to the field called last name. So. 00:21:08.000 --> 00:21:17.000 That's all you have to do, and that will get you the user information, last name, first name, from the users table. 00:21:17.000 --> 00:21:24.000 Now, using that little shortcut method thing, you can do exactly the same thing. You can say. 00:21:24.000 --> 00:21:39.000 Jason B, you know, pound sign, bracket, bracket. personal and then last name. This is a shortcut method that I like to use, but it does exactly precisely the same thing as this. 00:21:39.000 --> 00:21:49.000 So, um… In case I've lost you at this point, don't worry, I have a crib sheet that is linked here at the top of the. 00:21:49.000 --> 00:21:59.000 top of the page here which will kind of take these examples and put them into a form that hopefully you'll be able to decipher. 00:21:59.000 --> 00:22:06.000 Okay. Um… So that's the second level extracts. 00:22:06.000 --> 00:22:11.000 And a lot of times you have third-level extracts. Oh, boy. 00:22:11.000 --> 00:22:18.000 So this is an example from the audit loan table folio circulation audit loan. 00:22:18.000 --> 00:22:23.000 And the audit loan table gets you a lot of information about. 00:22:23.000 --> 00:22:35.000 The real history of alone. And, uh, so here… We have this field called name. 00:22:35.000 --> 00:22:43.000 That's, um… This is within the loan. Here, let me make it a little bit bigger. 00:22:43.000 --> 00:22:48.000 oh come on. 00:22:48.000 --> 00:23:00.000 Okay? So the name field. Is within… First comes the loan section. 00:23:00.000 --> 00:23:16.000 And the loan section has got all this other stuff in it. And then within loan is status. So it has to do with the loan status and the loan status, there's a name. It's open loan or closed loan, right? So you have to dig down. 00:23:16.000 --> 00:23:22.000 To get to that name, to that field. And likewise. 00:23:22.000 --> 00:23:35.000 The loan under loan. Again, you come down to metadata, which is about the loan when it was created, when it was updated, and who did it right? 00:23:35.000 --> 00:23:41.000 So these are things that are just a little more complicated to get at. 00:23:41.000 --> 00:23:57.000 So, here's how you do it. Um… Note that the fields listed in these JSON extract statements have to be in the hierarchical order that they appear in the table. 00:23:57.000 --> 00:24:02.000 So, um… You go Jason extract path text. 00:24:02.000 --> 00:24:08.000 Um, audit loan. This is the name of the table, then the Json array. 00:24:08.000 --> 00:24:22.000 Then you go to loan. That was at the higher level. Then you go down to status. That was at the next level. And then name was at the bottom, and that's the one that you wanted to get to. 00:24:22.000 --> 00:24:34.000 Somebody say something? whatever. Okay. 00:24:34.000 --> 00:24:43.000 Anyway, so I hope I'm making and and then this is an alternate way to do it using that shortcut method. 00:24:43.000 --> 00:24:48.000 Okay, so it's just exactly the same thing. It's just using that shortcut. 00:24:48.000 --> 00:24:56.000 Now, this is the worst part is extracting arrays embedded into objects. 00:24:56.000 --> 00:25:11.000 And here's an example. This is from the users, the folio users custom fields table. I don't know if you guys use custom fields, but we do. 00:25:11.000 --> 00:25:17.000 And the custom fields are a big pain in the rear. 00:25:17.000 --> 00:25:23.000 We have to extract them out of. Um, the custom fields table. 00:25:23.000 --> 00:25:33.000 Uh, which is a production. Okay, so you have… The select field here. 00:25:33.000 --> 00:25:46.000 Let's say that we wanted to select these things, the in the values array, and you know what's an array because of this the square brackets. Okay, you know that that that's an array. 00:25:46.000 --> 00:25:55.000 Uh, and the elements in the array. Or the value ID. 00:25:55.000 --> 00:26:15.000 The value value okay active, or… inactive, and then default true or false. Okay, don't ask me to explain that. But anyway, there's a bunch of things in this values array that you might want to get at. 00:26:15.000 --> 00:26:24.000 So the way to do that… is essentially that you have to extract. 00:26:24.000 --> 00:26:32.000 the… This values array by itself first. 00:26:32.000 --> 00:26:41.000 And… To do that, you're going to use something called a cross join cross join lateral. 00:26:41.000 --> 00:26:58.000 This is what set my head spinning, because I couldn't wrap my head around this, but essentially what you're doing is extracting that array that has the stuff that you need, and you're making it into its own JSON object. 00:26:58.000 --> 00:27:07.000 Okay, and from there, you take that JSON object, and then you can extract it with the simpler stuff. 00:27:07.000 --> 00:27:16.000 With the Jason extract path text stuff. so to make this cross join. 00:27:16.000 --> 00:27:23.000 Um, you have to use this expression. Json array elements. So you're saying. 00:27:23.000 --> 00:27:30.000 What I'm going to ask you to do now is go into an array and take out the elements. 00:27:30.000 --> 00:27:43.000 and you have to follow this path. Jason extract path in order to get to the elements that I want. So Jason array elements, Jason extract path. 00:27:43.000 --> 00:27:50.000 Then you say what the table is. This was my alias for the custom fields table. 00:27:50.000 --> 00:27:55.000 and I said, Okay, go down into select field. 00:27:55.000 --> 00:28:00.000 Then options, then values. And so if we go back up here. 00:28:00.000 --> 00:28:05.000 You can see select fields is the topmost level. 00:28:05.000 --> 00:28:11.000 Then you've got your options chunk. Then you've got your values chunk. 00:28:11.000 --> 00:28:18.000 So that's what it's telling it. It's giving it… a roadmap on how to find this stuff. 00:28:18.000 --> 00:28:19.000 Um… Okay, so that… so you're making something called… You're making another JSON object out of this extraction, and I've named it values, but you can name it any old thing that you want. Doesn't have to be. 00:28:19.000 --> 00:28:38.000 But just let us know. Thanks. 00:28:38.000 --> 00:28:54.000 you know, it could be named Fred Flintstone, it could be named anything. So, um… you're going to be using this thing called values to extract the other things within that object. 00:28:54.000 --> 00:29:07.000 Um, so… up here in your query statement, you're naming the fields that you want. You want your ID fields, your ref ID. 00:29:07.000 --> 00:29:16.000 A custom names field. Then you're doing Jason extract path text out of the. 00:29:16.000 --> 00:29:27.000 array that you just created through the cross join the thing that I called values, and then you're saying, Okay, get the ID out of that chunk. 00:29:27.000 --> 00:29:36.000 So that's the value ID. Then you go through the same chunk, and you get the value section. 00:29:36.000 --> 00:29:42.000 And that's the value name. 00:29:42.000 --> 00:29:51.000 And that's… that's essentially what… how you get to an embedded array object. So you use a cross join. 00:29:51.000 --> 00:29:59.000 You can use these various extracted fields in your order by statement. 00:29:59.000 --> 00:30:10.000 Um, it's perfectly okay to do that, so that's kind of handy. The other thing I'd like to mention is sometimes you might see ordinality. 00:30:10.000 --> 00:30:19.000 And the ordinality is nothing more than. The order in which the values appear. 00:30:19.000 --> 00:30:25.000 in the record. So you can have a whole bunch of values. 00:30:25.000 --> 00:30:42.000 that, um… you know, or just out there. And what the query does, if you don't specify the ordinality of each of the values, that's perfectly okay, but the query will then use alphabetical order to list them. 00:30:42.000 --> 00:30:47.000 And that may be fine, but if it's important to you. 00:30:47.000 --> 00:30:58.000 to list the values that you get out of the array in the order in which they appear in the record. Then you have to stick this. 00:30:58.000 --> 00:31:08.000 This line here, with ordinality. as okay? And. 00:31:08.000 --> 00:31:17.000 This is just… I don't know how to explain this except that once you do your cross join statement, cross join, blah, blah, blah, blah. 00:31:17.000 --> 00:31:33.000 Um… You can say with ordinality. Okay, so saying with ordinality, and then as values Jason B is the is the thing that you're calling it. You're calling it values in this case. 00:31:33.000 --> 00:31:43.000 Um, but the only difference from the previous one is you're saying with ordinality, so it… it says, aha, I have to put an ordinality on these values. 00:31:43.000 --> 00:31:49.000 And when you've got a cross join that says with ordinality. 00:31:49.000 --> 00:31:55.000 Then you can. Where the heck is it? 00:31:55.000 --> 00:32:04.000 I don't have it in here. Well, you can put as part of your select statement. 00:32:04.000 --> 00:32:11.000 Right underneath this stuff. Uh, you can say values.ordinality. 00:32:11.000 --> 00:32:17.000 which will give you the ordinality number for each of these values that it extracted. 00:32:17.000 --> 00:32:28.000 Okay, I'm sorry I didn't put that in. But anyway, extracting all this stuff in the little query that I have up here gives you this result. 00:32:28.000 --> 00:32:35.000 And so you have your custom fields, the ref id, whatever that is custom fields name. 00:32:35.000 --> 00:32:51.000 Then the value and the value name. So. That's enough to confuse anybody, but there it is. Now, the other thing that you might find is. 00:32:51.000 --> 00:32:59.000 An array that has… Just square brackets. It looks like it's a top-level extract. 00:32:59.000 --> 00:33:07.000 You know, it's like… It doesn't have to be dug into. 00:33:07.000 --> 00:33:15.000 Except that this thing that you have at the top level is not a nice text field like this. 00:33:15.000 --> 00:33:22.000 It's in. square brackets, which means that it has an array of values. 00:33:22.000 --> 00:33:31.000 So you will have to do another cross joint statement in order to get it out of there. 00:33:31.000 --> 00:33:44.000 and… you know, I'm I'm doing. I really like this… this thing here. Um… So I'm going to be using that. 00:33:44.000 --> 00:33:48.000 But, uh… We have to jump down. 00:33:48.000 --> 00:33:53.000 into the from statement. So this is from the user users table. 00:33:53.000 --> 00:34:04.000 and I'm doing another cross join. Okay? And whenever you do that, you say Json array elements. You tell it that it's an array that you're getting. 00:34:04.000 --> 00:34:10.000 Then you tell it the path. And in this case, it's at the top level. 00:34:10.000 --> 00:34:16.000 And it's called departments, right? Because up here we saw this. 00:34:16.000 --> 00:34:20.000 Top level, and it was… departments. 00:34:20.000 --> 00:34:26.000 So you're going to do that. And in this case I did put the ordinality. 00:34:26.000 --> 00:34:33.000 And this extracted object, then I just named departments to Epts. 00:34:33.000 --> 00:34:41.000 So up here in the SELECT statement, I'm saying. 00:34:41.000 --> 00:34:48.000 Here, this is the department's object that I created through the cross join. 00:34:48.000 --> 00:34:55.000 And this… This stuff here, these little curly brackets with nothing in them. 00:34:55.000 --> 00:35:00.000 It just means get whatever values are in there. 00:35:00.000 --> 00:35:10.000 As department ID. This extracts the array values from the department's JSON object created through the cross join. 00:35:10.000 --> 00:35:24.000 Note that the curly brackets are empty. This is because there is no tag for the values. It's just the values themselves. If there were a tag for the values, the tag name would go in the curly brackets. 00:35:24.000 --> 00:35:40.000 So in this particular case, I just wanted to show it because it looks bizarre. It's just a set of empty brackets, but this is all you have to do if you see that. You just put your curly brackets and leave them. 00:35:40.000 --> 00:35:43.000 As is. Okay. 00:35:43.000 --> 00:35:45.000 Uh, Joanne? 00:35:45.000 --> 00:35:46.000 Um, Lauren, I'm not sure if that's a question? 00:35:46.000 --> 00:35:50.000 Yes. 00:35:50.000 --> 00:35:54.000 But there's a comment in the chat, and she's saying, 00:35:54.000 --> 00:35:55.000 Uh, she's saying, I was having trouble with a 00:35:55.000 --> 00:35:56.000 Oh, okay. 00:35:56.000 --> 00:36:01.000 We're… query where I needed to extract data from multiple arrays. 00:36:01.000 --> 00:36:03.000 From the same table. 00:36:03.000 --> 00:36:04.000 But using multiple cross-joins, 00:36:04.000 --> 00:36:07.000 Uh-huh. Yes, yes, uh-huh. Yes. 00:36:07.000 --> 00:36:10.000 ended up only giving me results. 00:36:10.000 --> 00:36:13.000 where there was data in all three arrays, 00:36:13.000 --> 00:36:16.000 And not return a null values… 00:36:16.000 --> 00:36:23.000 Um… 00:36:23.000 --> 00:36:24.000 Okay. 00:36:24.000 --> 00:36:27.000 Yes, that is… Correct, and that… that is actually something I'm going to get to in a few minutes. That is a great observation. 00:36:27.000 --> 00:36:44.000 Cross joins, if you use a cross join in a query, it will get only those records that have values for the cross join. So, in other words, if you have the inventory table, and that's got like. 00:36:44.000 --> 00:36:49.000 I don't know how many of these embedded arrays in it. 00:36:49.000 --> 00:36:59.000 Um, and you wanted to get… records that may or may not have values in those arrays. 00:36:59.000 --> 00:37:08.000 You have to do a two-part query. Okay? Or maybe even 3-part. But anyway, I will show that in a second. 00:37:08.000 --> 00:37:19.000 Let me just show you here. God, my… This is hard to… hard to follow, but… Anyway. 00:37:19.000 --> 00:37:24.000 You got your departments out of this particular example. 00:37:24.000 --> 00:37:31.000 and… Okay, why is this not… expanding. 00:37:31.000 --> 00:37:36.000 Okay, I'm not sure why this isn't. Oh, okay, expand, though. 00:37:36.000 --> 00:37:37.000 Yeah, there you go. Yeah, uh… oh… 00:37:37.000 --> 00:37:40.000 Come on. 00:37:40.000 --> 00:37:42.000 Why is it not showing? 00:37:42.000 --> 00:37:43.000 Oh, there you go. It just takes a minute. 00:37:43.000 --> 00:37:46.000 It's probably the… the connection is not great. Okay. 00:37:46.000 --> 00:37:49.000 We're seeing it, yeah. 00:37:49.000 --> 00:37:58.000 Yeah, okay, so you… you see that the departments were extracted. And I put the ordinality in there. So it tells you. 00:37:58.000 --> 00:38:19.000 Uh, for each of these so far in this screenshot. Anyway, there was only one department associated with each user, so department ordinality is one. Um… You might have users that have multiple departments, and so you would have, you know, 123 or something, but. 00:38:19.000 --> 00:38:29.000 Anyway, it comes out nice. Um… So, let's see, what have I got here? 00:38:29.000 --> 00:38:35.000 Okay, the, you know, summarize three ways to extract arrays. 00:38:35.000 --> 00:38:44.000 You don't have to use a cross join. Uh, just use a triple nested JSON extract statement in the select clause. 00:38:44.000 --> 00:38:57.000 And, um… You have to trust me on this. This works. You can essentially do what the cross join did. 00:38:57.000 --> 00:39:06.000 Uh, but you do it in one… one nested statement instead of via the cross join. 00:39:06.000 --> 00:39:10.000 Okay, this has distinct advantages, because this will get you. 00:39:10.000 --> 00:39:17.000 Um, those records that don't have values in that particular. 00:39:17.000 --> 00:39:25.000 data array. Okay, so this is a good way to get things that may or may not have a value. 00:39:25.000 --> 00:39:39.000 Um, you can use the cross join. and put the result in a single level Jason extract statement in the select clause. 00:39:39.000 --> 00:39:47.000 So this is if you just want to have records that have these values. 00:39:47.000 --> 00:39:53.000 Um… 00:39:53.000 --> 00:40:07.000 Now, I don't know what's happening here. Okay, I'm not… my… My view of what is on the page seems to be a little bit spotty because I think my connection is not great. 00:40:07.000 --> 00:40:13.000 But anyway, um… You can use the cross join the same as in. 00:40:13.000 --> 00:40:19.000 Example B here. But you can use that shortcut method, this thing. 00:40:19.000 --> 00:40:20.000 Mm-hmm. 00:40:20.000 --> 00:40:25.000 To get the, um, the values. Now. 00:40:25.000 --> 00:40:27.000 Pounds greater than and greater than. 00:40:27.000 --> 00:40:36.000 Yes, exactly. And then this part, this number 4 is how you can. 00:40:36.000 --> 00:40:56.000 do this to prevent records from dropping out. And, um… Like I say, cross join queries will only return those records that have the array have all the array elements you're trying to find. It's possible to use multiple cross joint statements in the from clause to get multiple array values. 00:40:56.000 --> 00:41:07.000 Which I think is what Lauren was trying to do. But the results will show only those records that have all of the array elements specified in the cross joins. 00:41:07.000 --> 00:41:18.000 So that's usually not great because you want to see stuff that has them and that doesn't have them. 00:41:18.000 --> 00:41:27.000 Your first thing is you're going to create a two-part query. First, create a query that has the nested Jason extract statement. 00:41:27.000 --> 00:41:40.000 Uh, for each of the arrays that you're trying to extract. Um… And if you if you do that long nested JSON extract statement. 00:41:40.000 --> 00:41:46.000 That will get… 00:41:46.000 --> 00:41:53.000 the array values. for the arrays that you're looking at. 00:41:53.000 --> 00:42:08.000 Um… Ah, gosh, I'm sorry. Um, in this example, gosh, I don't know if you're seeing the kind of crazy stuff that I'm seeing on on my screen, but anyway. 00:42:08.000 --> 00:42:12.000 It's staying on the ReportingSec page. 00:42:12.000 --> 00:42:13.000 pretty well. Well, there was a little… little bit of blippiness, but it seems to be okay. 00:42:13.000 --> 00:42:22.000 Um… Okay, uh… Yeah, I'm very sorry for the blippiness, and I'm sorry that this is such a complicated topic. 00:42:22.000 --> 00:42:24.000 Well, it's not your fault. 00:42:24.000 --> 00:42:31.000 Well… So, in this first half, I'm getting the array elements. 00:42:31.000 --> 00:42:48.000 Ah, for contributors, languages, and subjects. So you can see, um… This starting with this part right here. 00:42:48.000 --> 00:42:55.000 This is the nested Json extract statement that gets the contributors. 00:42:55.000 --> 00:43:02.000 Right. This next one… is for languages. 00:43:02.000 --> 00:43:09.000 And this third one is for subjects. 00:43:09.000 --> 00:43:21.000 And then the second half of the query here is I'm going going to go separately, and um… Get all the records from the instance table. 00:43:21.000 --> 00:43:28.000 and left join the results. Um, to what you found in the first half of the query. 00:43:28.000 --> 00:43:33.000 So I'm going down to the inventory instance T table. 00:43:33.000 --> 00:43:41.000 And then I'm going to left join. the values from the first half of the query. 00:43:41.000 --> 00:43:53.000 that got all of the extracted arrays. and I'm going to put it all together. So when you left join it to those extracted array values. 00:43:53.000 --> 00:43:59.000 You can get everything you you can get the. 00:43:59.000 --> 00:44:08.000 You can get records that have those values and that don't have the values in in any combination. 00:44:08.000 --> 00:44:12.000 So you see that the, um… We have a title. 00:44:12.000 --> 00:44:23.000 The first one in the list there has, it has values for contributors, languages, and subjects. But then if you go down to row 10. 00:44:23.000 --> 00:44:35.000 Astrophysics and cosmology, that does not have a contributor's value, so that chose is null also doesn't have a languages that shows as blank and subjects show as null. 00:44:35.000 --> 00:44:52.000 So that didn't have anything. But, you know, you can see that there are different combinations here. Row 15 has a contributor, doesn't have a language, doesn't have subjects. So this two-part method is the way you would go about getting. 00:44:52.000 --> 00:45:00.000 Um, you get your array values, and then you get the entire table, and then left join the array values to it. 00:45:00.000 --> 00:45:10.000 So, um… You know, I am sorry, this is just cancel, cancel, cancel. 00:45:10.000 --> 00:45:13.000 I think if you… just the X in the top right there… 00:45:13.000 --> 00:45:17.000 Um, not the window, but… 00:45:17.000 --> 00:45:20.000 Oh, it looks like Joanne has frozen. 00:45:20.000 --> 00:45:29.000 Uh-oh. Hmm… 00:45:29.000 --> 00:45:32.000 Okay. Um… 00:45:32.000 --> 00:45:34.000 Hmm… 00:45:34.000 --> 00:45:37.000 I'll see if I can… 00:45:37.000 --> 00:45:40.000 Let's see if she's… 00:45:40.000 --> 00:45:48.000 Trying to reach me… 00:45:48.000 --> 00:45:52.000 Uh, is everyone else seeing the frozen screen? 00:45:52.000 --> 00:45:54.000 Oh, 00:45:54.000 --> 00:46:00.000 Okay, I'm… it looks like Joanna's choosing to log in again. Okay, thanks, Scott. 00:46:00.000 --> 00:46:02.000 Okay, yeah, and we have no share. 00:46:02.000 --> 00:46:05.000 Um, and uh, she is… 00:46:05.000 --> 00:46:07.000 She has had a… 00:46:07.000 --> 00:46:09.000 Problem with her network connection. 00:46:09.000 --> 00:46:11.000 Um… 00:46:11.000 --> 00:46:15.000 So… 00:46:15.000 --> 00:46:24.000 Let me just see… 00:46:24.000 --> 00:46:37.000 Does anyone, uh, have any questions while we're waiting for Joanne to pop back in? 00:46:37.000 --> 00:46:40.000 Okay. 00:46:40.000 --> 00:46:47.000 Alright, let me see what's happening… 00:46:47.000 --> 00:46:51.000 Oh, um, okay. 00:46:51.000 --> 00:46:53.000 computer died. 00:46:53.000 --> 00:46:59.000 That sounds serious. Um, okay. Alright. 00:46:59.000 --> 00:47:02.000 Uh, well… 00:47:02.000 --> 00:47:05.000 Let's see here if we can get this up. 00:47:05.000 --> 00:47:11.000 And I think she will continue to try to have her. Oh, you're back! Your computer miraculously recovered! 00:47:11.000 --> 00:47:14.000 I am back, but… oh my god, it is… this is not good, not good at all. 00:47:14.000 --> 00:47:19.000 Oh, dear. Yeah, we could… we need to get IT in on this, yeah. 00:47:19.000 --> 00:47:20.000 We'll get support in on your computer. 00:47:20.000 --> 00:47:29.000 Um… Oh my god. 00:47:29.000 --> 00:47:30.000 Let me get the link for you. 00:47:30.000 --> 00:47:31.000 Not a good time for it to go belly up. Okay, where was I? 00:47:31.000 --> 00:47:32.000 Um… 00:47:32.000 --> 00:47:36.000 Um, I think… see, am I sharing my screen? Probably not. 00:47:36.000 --> 00:47:43.000 Not at this point. 00:47:43.000 --> 00:47:54.000 Hmm… 00:47:54.000 --> 00:47:56.000 Let's see… I don't know if I dare share my screen. 00:47:56.000 --> 00:48:01.000 Aaron has given you the link. Thank you, Aaron. 00:48:01.000 --> 00:48:06.000 Do you have the link for the page? It's right there in the chat. Erin, thanks so much. 00:48:06.000 --> 00:48:09.000 Okay, are we back? Do you see my screen? 00:48:09.000 --> 00:48:16.000 We're back! 00:48:16.000 --> 00:48:17.000 We do have a question in the chat. 00:48:17.000 --> 00:48:19.000 Okay. So we're we're almost done here. Um… Oh, sure. 00:48:19.000 --> 00:48:24.000 While you're finding your place, or I'll just wait for a second, so I'll… I could just tell you… 00:48:24.000 --> 00:48:27.000 Hey, Lauren! 00:48:27.000 --> 00:48:30.000 Um, so… 00:48:30.000 --> 00:48:34.000 Okay. Okay, so Lauren has a question. Oh. 00:48:34.000 --> 00:48:38.000 Uh, sure, uh, other people are using lateral joints, but not cross joins. 00:48:38.000 --> 00:48:43.000 to do multiple array extractions, or people think it's better to do the nested. 00:48:43.000 --> 00:48:46.000 extractions. 00:48:46.000 --> 00:48:51.000 Um, okay, so, uh… 00:48:51.000 --> 00:48:52.000 Yeah, I don't know if people are doing one or the other. 00:48:52.000 --> 00:49:02.000 I see, I don't know. I… I don't know. I really don't know. Um… 00:49:02.000 --> 00:49:09.000 I wish I could answer your question. I truly don't know. I don't know what the difference is. 00:49:09.000 --> 00:49:10.000 Mm-hmm. 00:49:10.000 --> 00:49:13.000 between lateral joins and cross joins. Um… 00:49:13.000 --> 00:49:19.000 Do remember that Nasib showed us the nested later as sort of a new and improved kind of thing, shorter. 00:49:19.000 --> 00:49:21.000 way to do it, so… 00:49:21.000 --> 00:49:28.000 But, um, I don't know if it's necessarily better what people are doing. 00:49:28.000 --> 00:49:29.000 Yeah, we didn't… we didn't have the instructions on the nested at first, and uh… 00:49:29.000 --> 00:49:33.000 I have no clue. Um… 00:49:33.000 --> 00:49:36.000 Lucy gave us those later. 00:49:36.000 --> 00:49:39.000 Um, and they're… they're kind of… 00:49:39.000 --> 00:49:45.000 I don't know, they're shorter, um, so a little less to type. 00:49:45.000 --> 00:49:56.000 What do you mean about this? Shorter. Do you mean this… this, uh… Uh, table.jsonbound sign. 00:49:56.000 --> 00:49:58.000 Yeah, yeah. 00:49:58.000 --> 00:49:59.000 Yeah. 00:49:59.000 --> 00:50:07.000 bright arrows. Yeah, that… that shortcut is… it's just a shorthand, it's not, um… I don't think it's a, you know, thing that you have to use. 00:50:07.000 --> 00:50:15.000 Oh, not versus… yeah, yeah. 00:50:15.000 --> 00:50:16.000 Mm-hmm. Mm-hmm. 00:50:16.000 --> 00:50:20.000 You know, you can write it out. You could do JSON extract path text, blah blah blah, and that will get you to the same place. 00:50:20.000 --> 00:50:27.000 Um, anyway, not to beat a dead horse, but anyway, this last bit. 00:50:27.000 --> 00:50:34.000 is, um, revising your query to get data array extractions instead of derived tables. 00:50:34.000 --> 00:50:47.000 So, this may be something you want to do if your need for data is more immediate, like, you need to get the up-to-the-second, or up-to-the-minute data. 00:50:47.000 --> 00:50:52.000 Um, for your purposes. And one thing that I have looked into. 00:50:52.000 --> 00:51:10.000 is, um, overdue loans for… short-term loan stuff. So, course reserves and equipment. And if you had a need to find things that were overdue right then and there. You could use. 00:51:10.000 --> 00:51:18.000 A data array extraction. You couldn't use the derived tables, because the derived tables are too old. You know they're yesterday's data. 00:51:18.000 --> 00:51:23.000 So, you might want to use a data array extraction. 00:51:23.000 --> 00:51:43.000 So, to get the derived tables, this is an example, and I don't expect you to, like, look at this and memorize it. But anyway, it gets stuff about the the loan and, um… The, uh… the date and time for, uh… 00:51:43.000 --> 00:51:48.000 For when it was checked out and when it was due. 00:51:48.000 --> 00:51:54.000 But again, you know, this is yesterday's data. So it's only going to take you so far. 00:51:54.000 --> 00:52:05.000 And, um… Then, to get… to get it out of the primary tables, the loan table and the users table, which is what you have to do. 00:52:05.000 --> 00:52:14.000 Um, you would then have to use your JSON array extracts. In particular, for the users table. 00:52:14.000 --> 00:52:20.000 You have to go into the… in into the data array, and you dig down. 00:52:20.000 --> 00:52:27.000 to the part that's the personal chunk, and you get the first name and last name. 00:52:27.000 --> 00:52:38.000 And… These are all the tables that you have to use. You got to use the loan table, the users table, the item table. 00:52:38.000 --> 00:52:59.000 material type table, because these are all things that the derived table did for you, but the derived table didn't go down into the nitty-gritty of the username, and it did not, um… It did not give you up to the minute data, which is the big thing with this type of query. 00:52:59.000 --> 00:53:08.000 So… You'll notice that this query is longer. This is like 64 lines long. 00:53:08.000 --> 00:53:15.000 Whereas the other query was only 41 lines long, and that's because you're using more tables. 00:53:15.000 --> 00:53:27.000 And that's generally true if you're trying to substitute primary tables for derived tables, because the derived tables don't get everything, right? So you generally have to use more tables. 00:53:27.000 --> 00:53:33.000 And when that happens, usually the query takes longer to run. 00:53:33.000 --> 00:53:50.000 and it… that may not be much of a difference in in this case. This is a pretty simple query may not look like it on the service, but it really is. And this doesn't take very much longer to run than the derived table query does. 00:53:50.000 --> 00:54:06.000 And I would just want to leave you with a parting statement here. If the whole prospect of using trying to use your own extracts out of a primary table is too much. You could. 00:54:06.000 --> 00:54:21.000 or if you think the derived table is okay, except for the fact that it's yesterday's data, what you can do is go into the derived table code and put that into your query. Just cut and paste. 00:54:21.000 --> 00:54:30.000 And that way you'll have what you need, but without going through the trouble of trying to extract the data from the primary table. 00:54:30.000 --> 00:54:40.000 Now, that only works if the derived table has everything that you need, which for me doesn't happen a lot, but. 00:54:40.000 --> 00:54:45.000 Um, and then one final thing is up here at the top. 00:54:45.000 --> 00:54:51.000 My computer ever gets there. I want to show you here's a cheat sheet. 00:54:51.000 --> 00:55:00.000 Okay, which is… handy because it takes all the junk that I was talking about and shows you. 00:55:00.000 --> 00:55:16.000 In a little. Snippets… The most common… arrays that you might come across, and how to get them out. So here's like an example from the item table. There's a notes array. 00:55:16.000 --> 00:55:25.000 It's got all this stuff, and then here's how you would extract it out of the notes array. This is the formula. 00:55:25.000 --> 00:55:30.000 And then here's an alternate method is instead of using the. 00:55:30.000 --> 00:55:42.000 the long nested Jason statement, you can do the cross join, and that will get you that will extract anything that is in the array. But again, it doesn't. 00:55:42.000 --> 00:55:50.000 doesn't get the records that don't have a notes field, an array notes field value. 00:55:50.000 --> 00:56:00.000 So anyway, I just wanted to let you know that that little cheat sheet is here. Pretty skeletal, but it has some examples, I think, of the sorts of. 00:56:00.000 --> 00:56:07.000 um, arrays that you'll come across. So that is it. 00:56:07.000 --> 00:56:26.000 And I apologize for all the… Um… We're gonna get out of this. Just X out of it. I apologize for the confusion and for the computer glitches, but please let us know. Let me know if you have any questions about this, and I'll try to help. 00:56:26.000 --> 00:56:29.000 But let's, um, go back to… 00:56:29.000 --> 00:56:31.000 Lauren's question, 00:56:31.000 --> 00:56:35.000 Um, she was wondering if people are using the lateral joins, but 00:56:35.000 --> 00:56:37.000 Uh, not cross joints. 00:56:37.000 --> 00:56:44.000 Um, to do multiple array extractions. 00:56:44.000 --> 00:56:48.000 Okay. So she's saying she's, uh… 00:56:48.000 --> 00:56:52.000 You do a left joint lateral instead of a cross-joint lateral, but it'll return 00:56:52.000 --> 00:56:55.000 The null values. 00:56:55.000 --> 00:56:59.000 Not sure she understands how they work. 00:56:59.000 --> 00:57:00.000 Um… 00:57:00.000 --> 00:57:01.000 Oh, that's… that's good. 00:57:01.000 --> 00:57:04.000 So, yeah, don't know if anyone has had… 00:57:04.000 --> 00:57:05.000 some experience. 00:57:05.000 --> 00:57:09.000 That's a… that's… that's good. I… I didn't realize that. 00:57:09.000 --> 00:57:13.000 I didn't realize that. 00:57:13.000 --> 00:57:24.000 doing like a couple weeks ago, um. 00:57:24.000 --> 00:57:25.000 It's, uh, Lauren, are you talking? 00:57:25.000 --> 00:57:26.000 Um, I can't hear whoever's talking. 00:57:26.000 --> 00:57:33.000 We can't hear you at all. I mean, it's sort of like… like that. 00:57:33.000 --> 00:57:36.000 Lauren, do you want to unmute? 00:57:36.000 --> 00:57:40.000 Oh, she needs to troubleshoot her mic. Okay. 00:57:40.000 --> 00:57:41.000 Okay. You were… I mean, yeah, we could hear, like, teeny tiny little sounds. 00:57:41.000 --> 00:57:46.000 Oh, okay. 00:57:46.000 --> 00:57:52.000 Okay… 00:57:52.000 --> 00:57:56.000 Okay… 00:57:56.000 --> 00:57:57.000 Much better, much better. 00:57:57.000 --> 00:57:58.000 Uh, how about now? Great. Yeah, so I was working on a query. 00:57:58.000 --> 00:58:03.000 I can hear, yeah. 00:58:03.000 --> 00:58:20.000 like 2 weeks ago, which had multiple array extractions. And so I discovered this thing because I had previously been doing multiple array extractions and like like sub queries, or like creating subtables. And that that also works. 00:58:20.000 --> 00:58:21.000 Yeah. Yeah. 00:58:21.000 --> 00:58:22.000 Yes. 00:58:22.000 --> 00:58:52.000 Fine, although possibly slower. But I… trying to look things up, and it's like doing, like, left join lateral. Um, and so, like, I have the query was to get, um… identifiers that had particular attributes. And so that was my first cross join on the identifiers from the inventory table. And then I also wanted to get notes on records that on the records that had those identifiers which you know may or may not have admin notes or statistical codes versus the which was the 3rd thing I wanted to do. And so, like those. 00:58:56.000 --> 00:59:12.000 2 things could have null values, but the Medina fires couldn't. And what seemed to work is doing a cross join on identifiers, and then a left join lateral on the admin notes and the statistical codes. But, like, I don't actually know. 00:59:12.000 --> 00:59:18.000 what the lateral join is doing, but it doesn't, like, cross-join lateral. 00:59:18.000 --> 00:59:24.000 is things that only have those values, and the left join lateral is, you know, matches the thing. 00:59:24.000 --> 00:59:25.000 Oh, that's very good hints. Thank you. Yeah. 00:59:25.000 --> 00:59:34.000 on the first like like like like like a normal left join, but, like, what is… what is a lateral join actually actually mean? 00:59:34.000 --> 00:59:35.000 Okay, glad we're all confused together. 00:59:35.000 --> 00:59:56.000 I have no clue. But but if if I don't know, I mean, I… I tried to figure out what these, you know, make sense of what this thing said, and I couldn't. Okay, that's me. I could not. It just seems like an incantation, you know, left joint, cross joint, lateral. Um… 00:59:56.000 --> 01:00:11.000 But I'm glad that you mentioned that, because that actually I'm going to try that, and um… See if that solves this problem of records dropping out, and maybe you can just use that. 01:00:11.000 --> 01:00:25.000 Instead of… Um, doing the methods that I outlined here, you know. 01:00:25.000 --> 01:00:26.000 Mm-hmm. Yes, yes, uh-huh. Right. 01:00:26.000 --> 01:00:33.000 Yeah, that's why I was curious, like, if other people had tried that because doing the nested thing faster or slower than the left join? Like, does it get the same result? Because, like, it looks… like, to me, it looks neater to do the cross join and the left join laterals. 01:00:33.000 --> 01:00:36.000 Yeah, uh-huh. 01:00:36.000 --> 01:00:43.000 But if the nested is faster, then maybe I want to do that instead. So that's… so, like, this is something that we'd like just discovered that when we're trying to figure out, like, what is this actually doing? 01:00:43.000 --> 01:00:48.000 That's terrific. 01:00:48.000 --> 01:01:06.000 Oh, that's that thank you so much for that hint. I'm going to try it and see if it gets me anywhere. Yeah. Or rather it gets me the results that I expect. Uh… I'll do a compare and contrast, you know, I'll see if it does the same. 01:01:06.000 --> 01:01:11.000 Yeah, that would be a good thing to add to the documentation if, you know, 01:01:11.000 --> 01:01:14.000 If it turns out to be a good way to do things. 01:01:14.000 --> 01:01:19.000 I have here a lateral joint in SQL is a special type of joint, this is just a Google search. 01:01:19.000 --> 01:01:22.000 Um, a special type of join that allows a sub… 01:01:22.000 --> 01:01:27.000 query in the from clause to reference columns from a table expression that appears 01:01:27.000 --> 01:01:30.000 before it, in the from list. 01:01:30.000 --> 01:01:32.000 So, uh, and then there's a lot more about it, but uh… 01:01:32.000 --> 01:01:35.000 Oh, okay. Okay. 01:01:35.000 --> 01:01:39.000 Yeah, so we can… 01:01:39.000 --> 01:01:41.000 But, um… 01:01:41.000 --> 01:01:42.000 Yeah. So we can, uh… 01:01:42.000 --> 01:01:43.000 That's… that's good. 01:01:43.000 --> 01:01:46.000 Maybe update the page a little bit more. 01:01:46.000 --> 01:01:51.000 Any other questions? 01:01:51.000 --> 01:01:54.000 Is everyone's mind spinning? 01:01:54.000 --> 01:01:55.000 Oh. 01:01:55.000 --> 01:02:04.000 Well, Joanne has just been, uh, incredible, too, uh, untangle this for us at Cornell, and Joanna, I really appreciate you 01:02:04.000 --> 01:02:12.000 Contributing this to the reporting, say, because, uh, it had… the question had come up a couple times, you know, could we 01:02:12.000 --> 01:02:19.000 have somebody walk through how to do this, and so thank you so much for walking through it, and thanks everyone for sticking around to, um… 01:02:19.000 --> 01:02:21.000 To, uh… 01:02:21.000 --> 01:02:30.000 you know, look at that. You know, I find the easiest way to learn how these work is, of course, to just try them in queries, and… 01:02:30.000 --> 01:02:33.000 Uh, you can start to feel more comfortable with them, so… 01:02:33.000 --> 01:02:37.000 Joanne, thank you so much, uh, for putting all that information together. 01:02:37.000 --> 01:02:40.000 This is all in the reporting SIG. 01:02:40.000 --> 01:02:46.000 wiki, so, uh, you can… it's, uh, very easy to find extracting. 01:02:46.000 --> 01:02:52.000 Um, and I think there's a link that we put in the chat earlier, but, um, you can always reach out if you… 01:02:52.000 --> 01:02:55.000 are looking for that link, and… 01:02:55.000 --> 01:03:05.000 Missing it. Anything else, Joanne, before we part? 01:03:05.000 --> 01:03:11.000 Yeah. 01:03:11.000 --> 01:03:15.000 Yeah. 01:03:15.000 --> 01:03:16.000 Oh, no. 01:03:16.000 --> 01:03:27.000 No, I am, uh… I wish I had the magic wand to make it easy. Like, I could say, okay, this is… I know the page is very dense, and maybe not the clearest it could be, so I apologize for that, but… I hope you'll find some of it helpful. 01:03:27.000 --> 01:03:28.000 Yeah. 01:03:28.000 --> 01:03:30.000 Um, yeah, I mean, if you can extract your own arrays, there's nothing you can't do. I mean, really, that's, uh, your queries will be yours to command. 01:03:30.000 --> 01:03:34.000 Well, said, well said. 01:03:34.000 --> 01:03:42.000 All right. Well, thanks everyone for joining. We'll see you next time at the Reporting SIG. 01:03:42.000 --> 01:03:44.000 And, uh, hope you have a great week. 01:03:44.000 --> 01:03:53.000 Take care.