WEBVTT 1 00:01:15.030 --> 00:01:20.799 Sharon Markus: Good morning, we'll get started in a few minutes. Welcome to the Reporting SIG meeting. 2 00:01:22.180 --> 00:01:24.639 Steven Pryor (he/him/his): Good morning, I didn't think I was that early. 3 00:01:24.640 --> 00:01:26.140 Sharon Markus: Yep. 4 00:01:26.460 --> 00:01:28.130 Sharon Markus: It's got a couple minutes here. 5 00:01:28.360 --> 00:01:28.880 Steven Pryor (he/him/his): Nope. 6 00:02:41.430 --> 00:02:44.520 Sharon Markus: Good morning, we'll get started in just a couple minutes here. 7 00:03:29.610 --> 00:03:30.710 Sharon Markus: Good morning! 8 00:03:48.990 --> 00:03:52.210 Sharon Markus: Good morning, just, waiting for folks to join. 9 00:03:52.390 --> 00:03:57.790 Sharon Markus: Getting a head start on attendance. Is there anyone who would be willing to do… 10 00:03:58.310 --> 00:04:02.360 Sharon Markus: the rest of the attendants while I… start… 11 00:04:03.730 --> 00:04:05.420 Scott Perry: I should be able to finish it. 12 00:04:05.740 --> 00:04:07.980 Sharon Markus: Oh, hey Scott, thank you. 13 00:04:08.460 --> 00:04:11.110 Sharon Markus: Can only see so many names here. 14 00:04:11.580 --> 00:04:13.550 Sharon Markus: Thanks, Scott. 15 00:04:14.890 --> 00:04:16.399 Sharon Markus: Okay, let me update that… 16 00:04:27.270 --> 00:04:28.030 Sharon Markus: Yay. 17 00:04:30.000 --> 00:04:46.790 Sharon Markus: All right. Well, welcome to the ReportingSync meeting. I hope you're all doing well. We have an interesting tutorial today that Joanne and I have put together, and I think you'll find it helpful. So… 18 00:04:47.560 --> 00:04:50.219 Sharon Markus: We'll get started with that shortly. 19 00:04:51.910 --> 00:05:02.220 Sharon Markus: So, yeah, our meetings are held the first three Mondays of the month. We've, dropped the last meeting in the interest of calming our schedules. 20 00:05:02.690 --> 00:05:07.230 Sharon Markus: Are there any topics that you've been looking for 21 00:05:07.390 --> 00:05:18.819 Sharon Markus: And the reporting SIG, and I should mention that today's topic was requested by a reporting SIG member who attended and asked for a tutorial on data array. 22 00:05:19.230 --> 00:05:21.130 Sharon Markus: extraction. 23 00:05:24.760 --> 00:05:29.569 Sharon Markus: I don't have the chat open, so… Just make sure that's open. 24 00:05:39.550 --> 00:05:49.619 Sharon Markus: Okay, well, as I go, if you think of topics that you'd like to see, just let me or Scott know, and we'll see if we can get it into the 25 00:05:49.790 --> 00:05:50.950 Sharon Markus: scheduled. 26 00:05:51.470 --> 00:05:59.370 Sharon Markus: So, let's see, do we have any new members today, folks who would like to introduce themselves? 27 00:06:16.720 --> 00:06:32.970 Sharon Markus: Okay, I don't hear anyone, so welcome back, everybody. Our latest recordings are here, so if you miss a meeting and want to go back and watch what happened, you can always get to that here in the agenda. 28 00:06:33.390 --> 00:06:43.230 Sharon Markus: For recruitment, we had asked for some help with drive tables. We have that now, and I, we also have some help with. 29 00:06:43.320 --> 00:07:00.459 Sharon Markus: reviewing drive tables, but if you would like to jump in and do more with reporting, just to help out, or because you want to learn more about this, don't hesitate to reach out to me or just Scott. We always have stuff to do, so… 30 00:07:00.570 --> 00:07:05.070 Sharon Markus: Happy to have additional help on reporting stuff. 31 00:07:05.560 --> 00:07:14.539 Sharon Markus: So today, Joanne and I are gonna present, a tutorial on how to revise your query 32 00:07:14.710 --> 00:07:19.710 Sharon Markus: That you had set up to… Extract data fields from… 33 00:07:19.920 --> 00:07:26.349 Sharon Markus: using a derived table that was already sitting in your MetaDB environment. 34 00:07:26.460 --> 00:07:32.920 Sharon Markus: And, instead to extract it directly from the data array. So… 35 00:07:33.420 --> 00:07:36.809 Sharon Markus: That will be the focus for today. 36 00:07:37.040 --> 00:07:42.609 Sharon Markus: Drive tables are… A snapshot of data from the previous day. 37 00:07:42.930 --> 00:07:50.939 Sharon Markus: And so sometimes we need to rewrite queries in order to capture the most up-to-date information now that we have that. 38 00:07:51.050 --> 00:07:54.149 Sharon Markus: ability in MetaDB. We have, 39 00:07:54.580 --> 00:08:02.090 Sharon Markus: Live synchronization of data, so, being able to get the most Up-to-date information is really helpful. 40 00:08:02.230 --> 00:08:06.469 Sharon Markus: So, this goes… goes through that. 41 00:08:07.280 --> 00:08:10.290 Sharon Markus: So, I'm gonna hand it over to… 42 00:08:10.730 --> 00:08:15.259 Sharon Markus: Do you want to share, Joanne? Is that easiest? 43 00:08:15.520 --> 00:08:16.100 Sharon Markus: Maybe. 44 00:08:16.100 --> 00:08:20.089 Joanne Leary: Oh, let me see if I can manage that. Let's take a look. 45 00:08:20.600 --> 00:08:26.899 Sharon Markus: I can put the link to this in the chat, or to the… do you want me to… I can link to the page. 46 00:08:27.450 --> 00:08:28.820 Sharon Markus: So… 47 00:08:28.930 --> 00:08:39.110 Sharon Markus: Joanne has written these excellent instructions and contributed those to the reporting SIG. She has done a lot of instructions for our 48 00:08:39.280 --> 00:08:47.129 Sharon Markus: home reporting team, documentation at Cornell. And, so she has generously 49 00:08:47.130 --> 00:09:00.049 Sharon Markus: Given that to us, and has extended it a little bit for all of you, so that you can see an example of how to do this with a literal, you know. 50 00:09:00.050 --> 00:09:08.319 Sharon Markus: query that is changed from derived table to data array. So, but, this is all here. I'll, I'll… 51 00:09:08.570 --> 00:09:12.980 Sharon Markus: Do you want this link, Joanne? Is that helpful? 52 00:09:12.980 --> 00:09:19.419 Joanne Leary: I have… I have a blank, here. 53 00:09:20.010 --> 00:09:21.580 Joanne Leary: Okay, so I'll stop. 54 00:09:21.580 --> 00:09:22.550 Sharon Markus: share. 55 00:09:23.260 --> 00:09:25.040 Sharon Markus: I'll switch it over to you, because then you're… 56 00:09:27.360 --> 00:09:29.460 Sharon Markus: The mouse control's a little easier. 57 00:09:30.920 --> 00:09:35.370 Sharon Markus: We've been experimenting with remote control of presentations, and it's… 58 00:09:36.160 --> 00:09:38.050 Sharon Markus: It's kind of an interesting option. 59 00:09:38.390 --> 00:09:44.889 Joanne Leary: Yeah, especially for those of us who are not very adept at doing things. 60 00:09:45.400 --> 00:10:01.590 Joanne Leary: Okay, I'm hoping that you can see the… the page I have opened here, Extracting Fields from MetaDB JSON Arrays. I can see that. Anybody be able to see… see that? Okay. This is basically a… 61 00:10:02.190 --> 00:10:02.960 Joanne Leary: Ugh. 62 00:10:04.070 --> 00:10:09.820 Joanne Leary: a crib sheet on… What you need to do if you are… 63 00:10:10.660 --> 00:10:16.540 Joanne Leary: Like, you've got a query, and maybe you've been using your derived tables. 64 00:10:16.730 --> 00:10:22.579 Joanne Leary: But perhaps the derived table doesn't have what you need, because sometimes it doesn't. 65 00:10:22.870 --> 00:10:29.280 Joanne Leary: The other thing is sometimes you're using one of the tea tables. 66 00:10:29.570 --> 00:10:40.169 Joanne Leary: That is the, the transform table from a primary table, and the T table doesn't have what you need. Okay, that's pretty common, actually. 67 00:10:40.470 --> 00:10:44.530 Joanne Leary: So this tells you how to go about 68 00:10:44.710 --> 00:10:51.350 Joanne Leary: going into a table's data array, that is the JSON array. 69 00:10:51.630 --> 00:10:59.759 Joanne Leary: and taking out what you need from those tables. And unfortunately, it's not, like, a totally… 70 00:11:00.440 --> 00:11:07.969 Joanne Leary: simple thing, at least I haven't found it to be so. But there are some basic expressions that you need. 71 00:11:08.090 --> 00:11:16.300 Joanne Leary: to… to do this. And this very first part here is to explain each of the expressions. 72 00:11:16.410 --> 00:11:22.480 Joanne Leary: The JSONB extract path… 73 00:11:22.990 --> 00:11:31.879 Joanne Leary: This expression specifies the path in the data array that you have to follow in order to get to the bit that you want. 74 00:11:32.590 --> 00:11:49.179 Joanne Leary: This result is a JSON object, which means, okay, it's already… it's not a text object, it's not, like, just straight text, it's actually a data array in and of itself, which you will need to do sometimes. 75 00:11:49.410 --> 00:11:54.480 Joanne Leary: Then you have… JSON extract path text. 76 00:11:54.610 --> 00:12:11.499 Joanne Leary: And this does sort of the same thing as the other one does, except the result is a text object, which is just great, because then you can work with it a little bit easier. This expression, JSON ArrayElements, this is… 77 00:12:11.900 --> 00:12:16.400 Joanne Leary: you're telling it, okay, I'm going to send you down this path. 78 00:12:16.520 --> 00:12:23.569 Joanne Leary: And what you're expecting to find is an array of things, not just a simple value. 79 00:12:23.760 --> 00:12:26.939 Joanne Leary: So, this is part of the, 80 00:12:27.330 --> 00:12:31.169 Joanne Leary: Extraction expression that you'll often need to use. 81 00:12:31.570 --> 00:12:39.909 Joanne Leary: Then, I just wanted to quickly show you this operator, this, funny… pound, double, arrow. 82 00:12:40.120 --> 00:12:44.170 Joanne Leary: Sign operator, and that essentially is… 83 00:12:44.380 --> 00:12:50.999 Joanne Leary: The same thing as using the JSON extract path text thing. 84 00:12:51.120 --> 00:12:58.279 Joanne Leary: And it says, okay, follow this path of hierarchy elements. 85 00:12:58.470 --> 00:13:02.879 Joanne Leary: And each point in the path is separated by a comma. 86 00:13:03.190 --> 00:13:09.239 Joanne Leary: And then what you get at the end is the thing that you want, right? 87 00:13:09.390 --> 00:13:14.400 Joanne Leary: Okay, this is, very broadly worded. 88 00:13:14.840 --> 00:13:23.180 Joanne Leary: But, you're gonna find that your, data array in a table Has multiple levels. 89 00:13:23.500 --> 00:13:27.689 Joanne Leary: Each particular element in the array might have several 90 00:13:28.060 --> 00:13:35.749 Joanne Leary: levels of hierarchy that when you open up the table, you'll see these things. And here's an example. 91 00:13:36.220 --> 00:13:46.270 Joanne Leary: In the user table, when I look at the user table, I'm confronted with… come on… 92 00:13:47.210 --> 00:14:01.169 Joanne Leary: with different levels of hierarchy. So, you see the first levels are just these simple, fields, followed by a colon, followed by just a value, right? So, ID, 93 00:14:01.290 --> 00:14:16.079 Joanne Leary: Active, barcode, those are pretty simple. All you got is one thing. But then you get down to this. Metadata. Oops, I mean, what's it… what's going on here? This… this thing called metadata. 94 00:14:16.380 --> 00:14:27.849 Joanne Leary: has some subfields in it. It's got the create date, updated date, updated by user ID, created by user ID, and 95 00:14:28.010 --> 00:14:43.439 Joanne Leary: These may be things that you really need. You want to know when a record was created or updated. Then, this field, personal, this tells you all kinds of stuff. It tells you the username, the email address. 96 00:14:43.440 --> 00:14:51.749 Joanne Leary: And then, if the person has various addresses, in our case, it would be home address and campus address. 97 00:14:51.840 --> 00:14:58.189 Joanne Leary: And maybe some other addresses. That stuff… 98 00:14:58.450 --> 00:15:09.260 Joanne Leary: is enclosed in… you see these square brackets? That means that the values for this address field is actually another array. 99 00:15:09.380 --> 00:15:12.619 Joanne Leary: So that's a whole other level of complexity. 100 00:15:13.360 --> 00:15:19.560 Joanne Leary: And then whenever you see, square brackets. 101 00:15:19.740 --> 00:15:24.619 Joanne Leary: That's going to be your signal that what you're dealing with is an array of values. 102 00:15:25.040 --> 00:15:31.030 Joanne Leary: And, there's a special statement that you can use to get at those array of values. 103 00:15:31.480 --> 00:15:37.559 Joanne Leary: So, that is what you're going to be confronting. 104 00:15:39.340 --> 00:15:50.349 Joanne Leary: And, you can do these, JSON extract path statements to get at those various things. 105 00:15:50.670 --> 00:15:53.980 Joanne Leary: The very first level 106 00:15:54.390 --> 00:16:11.399 Joanne Leary: is… is easy peasy, right? You're… you're just gonna be able to get the, the value for ID, or the value for, barcode, I think, in this particular example. 107 00:16:11.470 --> 00:16:19.159 Joanne Leary: They are just at the top level, so you don't have to dig very far. Joanne? Yes. 108 00:16:19.480 --> 00:16:28.710 Sharon Markus: I just want to check in, because, I'm hoping that everyone is with us here, and just want to ask. 109 00:16:29.070 --> 00:16:37.949 Sharon Markus: Has everyone looked at the tables in MetaDB closely enough to understand what we're referring to? 110 00:16:38.060 --> 00:16:47.920 Sharon Markus: when we're talking about these various tables. So, if I… I often give the example of the loans table collection, so you'll have 111 00:16:48.170 --> 00:17:08.099 Sharon Markus: loan underscore underscore T is the current loan's information, and then loan just underscore underscore, or you could just cut it off as loan, is the table that has all the data to be extracted, loan 112 00:17:08.319 --> 00:17:21.050 Sharon Markus: underscore T underscore has the current and the historical information. 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. 113 00:17:28.180 --> 00:17:30.310 Joanne Leary: Okay, Juan is giving a thumbs up. 114 00:17:30.310 --> 00:17:32.740 Sharon Markus: One's got a thumb up, okay. Okay. 115 00:17:32.740 --> 00:17:36.309 Joanne Leary: Let's see, who else do we have? I got a fancy thumbs up. 116 00:17:36.310 --> 00:17:37.120 Sharon Markus: Okay. 117 00:17:37.120 --> 00:17:41.600 Joanne Leary: All right, please do feel free to interrupt if… 118 00:17:41.900 --> 00:17:46.080 Joanne Leary: what I'm saying doesn't make sense, or if you need some background. 119 00:17:46.420 --> 00:17:54.489 Joanne Leary: data, information, whatever. I'm sorry, this is a kind of a complicated topic, so… 120 00:17:54.490 --> 00:17:55.340 Sharon Markus: Yeah, that's what I wanted to. 121 00:17:55.340 --> 00:17:55.810 Joanne Leary: just open. 122 00:17:55.810 --> 00:17:56.860 Sharon Markus: Open that up, because… 123 00:17:57.400 --> 00:18:01.720 Sharon Markus: If you're like, I don't know what she's talking about, you know, we can show you the. 124 00:18:01.720 --> 00:18:02.190 Joanne Leary: Yeah. 125 00:18:02.190 --> 00:18:03.560 Sharon Markus: in a MetaDB environment. 126 00:18:03.900 --> 00:18:14.219 Sharon Markus: so you can relate to that a little better. But if everyone's like, no, no, we know exactly what you're talking about, which is what I'm getting a sense of, so… but just let us know. 127 00:18:14.480 --> 00:18:15.270 Sharon Markus: Thanks. 128 00:18:15.690 --> 00:18:24.510 Joanne Leary: Okay, so, anyway, I was saying that the first level extractions are really easy, because all you need is… 129 00:18:24.630 --> 00:18:34.080 Joanne Leary: Just your basic JSON extract path text, And then… 130 00:18:34.250 --> 00:18:39.549 Joanne Leary: this… this thing called active, that was a first… that was a top-level field. 131 00:18:39.680 --> 00:18:50.020 Joanne Leary: 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. 132 00:18:50.020 --> 00:19:01.620 Joanne Leary: users, and then you specify what the field is that you're looking for, and that's the thing called active. That's a Boolean field, so it's a true-false type of thing. 133 00:19:01.730 --> 00:19:06.810 Joanne Leary: And, and then you just say, from the users table, right? 134 00:19:07.500 --> 00:19:26.740 Joanne Leary: And the other way to write this, if you're lazy like me, and you don't want to write out JSON 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. 135 00:19:26.990 --> 00:19:33.780 Joanne Leary: And then, get… The active field, and that's what this little shorthand stuff is. 136 00:19:34.490 --> 00:19:35.260 Joanne Leary: Okay. 137 00:19:36.280 --> 00:19:43.799 Joanne Leary: Now, a lot of times, you're not so lucky, and you need to extract more than just a top-level field. 138 00:19:44.180 --> 00:19:46.590 Joanne Leary: This is an example where 139 00:19:46.710 --> 00:19:56.299 Joanne Leary: If you looked back at the users table, you would see that there is a chunk in that JSON array called personal. 140 00:19:56.730 --> 00:20:02.599 Joanne Leary: And within that personal chunk is where you get your first name and last name. 141 00:20:02.760 --> 00:20:09.039 Joanne Leary: So, that was one of my gripes early on when I was trying to get user 142 00:20:09.240 --> 00:20:12.860 Joanne Leary: info, for a lot of my CERC queries. 143 00:20:13.020 --> 00:20:28.580 Joanne Leary: And I couldn't use the extracted table, the T tables, user's T, I couldn't use that because it didn't have it. And the reason why it doesn't have it is because the transform tables only extract the very first highest level 144 00:20:28.880 --> 00:20:47.300 Joanne Leary: 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. So, I had to figure out how to get it myself. 145 00:20:47.510 --> 00:20:49.230 Joanne Leary: And this was Hal. 146 00:20:49.370 --> 00:20:54.469 Joanne Leary: you go to, you have to go JSON Extract Path Text. 147 00:20:54.610 --> 00:21:01.210 Joanne Leary: You go into your users table, and then you specify… go down to the section called Personal. 148 00:21:01.630 --> 00:21:07.029 Joanne Leary: And then get to the field called Last Name. So… 149 00:21:07.860 --> 00:21:15.490 Joanne Leary: That's all you have to do, and that will get you the user information, last name, first name, from the users table. 150 00:21:16.380 --> 00:21:22.980 Joanne Leary: Now, using that little shortcut method thing, you can do exactly the same thing. You can say. 151 00:21:23.140 --> 00:21:28.029 Joanne Leary: Jason B, you know, pound sign, bracket, bracket, 152 00:21:28.190 --> 00:21:38.660 Joanne Leary: personal, and then last name. This is, this is a shortcut method that I like to use, but it does exactly, precisely the same thing as this. 153 00:21:39.120 --> 00:21:40.760 Joanne Leary: So, 154 00:21:41.110 --> 00:21:47.860 Joanne Leary: 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 155 00:21:48.270 --> 00:21:49.380 Joanne Leary: Top of the… 156 00:21:49.770 --> 00:21:57.900 Joanne Leary: Page here, which will kind of take these examples and put them into a form that hopefully you'll be able to decipher. 157 00:21:59.030 --> 00:21:59.940 Joanne Leary: Okay. 158 00:22:00.140 --> 00:22:05.340 Joanne Leary: So that's the second level, extracts. 159 00:22:05.690 --> 00:22:10.460 Joanne Leary: And a lot of times you have third-level extracts, oh boy. 160 00:22:10.650 --> 00:22:17.249 Joanne Leary: So, this is an example from the audit loan table, folio circulation audit loan. 161 00:22:17.390 --> 00:22:22.200 Joanne Leary: And the audit loan table gets you a lot of information about 162 00:22:22.550 --> 00:22:25.139 Joanne Leary: The real history of a loan. 163 00:22:26.030 --> 00:22:35.940 Joanne Leary: And, so here… We have… this field called Name, That's, 164 00:22:37.290 --> 00:22:42.320 Joanne Leary: This is within the loan… here, let me make it a little bit bigger. 165 00:22:43.130 --> 00:22:44.800 Joanne Leary: Aww, come on! 166 00:22:47.940 --> 00:22:59.440 Joanne Leary: Okay, so… the name field… is… within… First comes the loan section. 167 00:22:59.660 --> 00:23:17.820 Joanne Leary: 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 to get to that name, to that field. 168 00:23:17.990 --> 00:23:20.680 Joanne Leary: And likewise. 169 00:23:21.210 --> 00:23:34.189 Joanne Leary: 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? 170 00:23:34.470 --> 00:23:39.209 Joanne Leary: So, these are things that are just a little more complicated to get at. 171 00:23:40.230 --> 00:23:43.080 Joanne Leary: So, here's how you do it. 172 00:23:45.010 --> 00:23:55.869 Joanne Leary: Note that the fields listed in these JSON extract statements have to be in the hierarchical order that they appear in the table. 173 00:23:56.250 --> 00:24:01.169 Joanne Leary: So, you go JSON Extract Path Text. 174 00:24:01.780 --> 00:24:07.479 Joanne Leary: Audit loan is the name of the table, then the JSON array. 175 00:24:07.740 --> 00:24:11.279 Joanne Leary: Then you go to Loan, that was at the higher level. 176 00:24:11.400 --> 00:24:19.580 Joanne Leary: 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. 177 00:24:21.780 --> 00:24:23.310 Joanne Leary: Somebody say something? 178 00:24:26.170 --> 00:24:27.680 Joanne Leary: Whatever, okay. 179 00:24:33.280 --> 00:24:42.300 Joanne Leary: Anyway, so, hope I'm making… and then this is an alternate way to do it using that shortcut method. 180 00:24:42.530 --> 00:24:47.120 Joanne Leary: Okay, so it's just exactly the same thing, it's just using that shortcut. 181 00:24:48.160 --> 00:24:50.150 Joanne Leary: Now, this is the worst part. 182 00:24:50.290 --> 00:24:55.530 Joanne Leary: Is extracting arrays embedded into objects. 183 00:24:56.010 --> 00:25:00.420 Joanne Leary: And… Here's an example. 184 00:25:02.550 --> 00:25:10.319 Joanne Leary: This is from the users, the Folio Users Custom Fields table. I don't know if you guys use custom fields, but we do. 185 00:25:10.530 --> 00:25:14.980 Joanne Leary: And the custom fields are a big pain in the rear. 186 00:25:15.140 --> 00:25:26.270 Joanne Leary: we have to extract them out of… The custom fields table, which is a production. 187 00:25:26.830 --> 00:25:31.979 Joanne Leary: Okay, so you have… The select field here 188 00:25:32.500 --> 00:25:45.700 Joanne Leary: Let's say that we wanted to select these things, the, in the values array, and you know it's an array because of this, the square brackets, okay? You know that that's an array. 189 00:25:45.840 --> 00:25:59.590 Joanne Leary: And the elements in the array Or… the value ID the value, value, okay, active. 190 00:25:59.750 --> 00:26:03.760 Joanne Leary: Or… Inactive. And then, 191 00:26:03.940 --> 00:26:13.549 Joanne Leary: 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. 192 00:26:15.080 --> 00:26:17.809 Joanne Leary: So, the way to do that 193 00:26:18.510 --> 00:26:31.599 Joanne Leary: Is, essentially that you have to extract the… This values array By itself, first. 194 00:26:32.130 --> 00:26:40.879 Joanne Leary: And… To do that, you're going to use something called a cross-join. Cross-join lateral. 195 00:26:42.630 --> 00:26:54.129 Joanne Leary: 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. 196 00:26:54.190 --> 00:27:06.790 Joanne Leary: And you're making it into its own JSON object, okay? And from there, you take that JSON object, and then you can extract it with the simpler stuff. 197 00:27:07.160 --> 00:27:10.059 Joanne Leary: with the JSON extract path text stuff. 198 00:27:10.690 --> 00:27:15.189 Joanne Leary: So, to make this cross-join. 199 00:27:15.570 --> 00:27:22.389 Joanne Leary: you have to use this expression, JSON array elements. So you're saying. 200 00:27:22.500 --> 00:27:29.110 Joanne Leary: What I'm going to ask you to do now is go into an array and take out the elements. 201 00:27:29.610 --> 00:27:33.400 Joanne Leary: And you have to follow this path. 202 00:27:33.530 --> 00:27:41.970 Joanne Leary: JSON extract pass in order to get to the elements that I want. So, JSON array elements, JSON extract pass. 203 00:27:42.470 --> 00:27:48.569 Joanne Leary: Then you say what the table is. This was my alias for the custom fields table. 204 00:27:49.240 --> 00:27:59.090 Joanne Leary: And I said, okay, go down into Select Field, then Options, then Values. And so if we go back up here. 205 00:27:59.860 --> 00:28:03.769 Joanne Leary: You can see Select Fields is the topmost level. 206 00:28:04.420 --> 00:28:06.949 Joanne Leary: Then you've got your options chunk. 207 00:28:07.300 --> 00:28:09.740 Joanne Leary: Then you've got your values chunk. 208 00:28:10.920 --> 00:28:13.790 Joanne Leary: So that's what it's telling it. It's giving it… 209 00:28:14.150 --> 00:28:17.160 Joanne Leary: A roadmap on how to find this stuff. 210 00:28:17.930 --> 00:28:25.419 Joanne Leary: okay, so the… so you're making something called… 211 00:28:26.170 --> 00:28:37.049 Joanne Leary: 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… 212 00:28:37.640 --> 00:28:44.930 Joanne Leary: You know, it could be named Fred Flintstone, it could be named anything. So, you… 213 00:28:45.150 --> 00:28:52.320 Joanne Leary: You're going to be using this thing called values to extract the other things within that object. 214 00:28:53.010 --> 00:28:54.950 Joanne Leary: So… 215 00:28:56.190 --> 00:29:03.839 Joanne Leary: up here in your query statement, you're naming the fields that you want. You want your ID fields, your ref ID, 216 00:29:04.090 --> 00:29:15.429 Joanne Leary: A custom names field, then you're doing JSON extract path text out of the 217 00:29:16.090 --> 00:29:26.490 Joanne Leary: array that you just created through the crossjoin, the thing that I called values, and then you're saying, okay, get the ID out of that chunk. 218 00:29:26.690 --> 00:29:29.119 Joanne Leary: So that's the value ID. 219 00:29:29.800 --> 00:29:34.099 Joanne Leary: Then you go through the same chunk, and you get the value section. 220 00:29:35.400 --> 00:29:38.309 Joanne Leary: And that's the value name. 221 00:29:41.650 --> 00:29:50.509 Joanne Leary: And that's… that's essentially what… how you get to an embedded array object. So you use a crossjoin. 222 00:29:50.760 --> 00:29:58.480 Joanne Leary: You can use these various extracted fields in your order by statement. 223 00:29:58.620 --> 00:30:01.819 Joanne Leary: It's perfectly okay to do that. 224 00:30:01.920 --> 00:30:08.759 Joanne Leary: So that's kind of handy. The other thing I'd like to mention is sometimes you might see ordinality. 225 00:30:09.320 --> 00:30:13.630 Joanne Leary: And… The ordinality is nothing more than… 226 00:30:14.140 --> 00:30:20.690 Joanne Leary: The order in which the values appear in the record. 227 00:30:21.010 --> 00:30:26.930 Joanne Leary: So, you could have a whole bunch of values, that, 228 00:30:27.290 --> 00:30:41.120 Joanne Leary: You know, are 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. 229 00:30:41.480 --> 00:30:47.960 Joanne Leary: And that may be fine, but if it's important to you, to list… 230 00:30:48.110 --> 00:30:55.359 Joanne Leary: The values that you get out of the array in the order in which they appear in the record. 231 00:30:55.460 --> 00:31:09.170 Joanne Leary: Then you have to stick this… This line here, with ordinality, Okay, and, this is just… 232 00:31:09.550 --> 00:31:16.599 Joanne Leary: I don't know how to explain this, except that once you do your cross-join statement, cross-join, blah blah blah blah. 233 00:31:19.790 --> 00:31:32.420 Joanne Leary: you can say, with ordinality. Okay, so saying with ordinality, and then as values, JSONB is the… is the thing that you're calling it. You're calling it values in this case. 234 00:31:32.510 --> 00:31:42.110 Joanne Leary: 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. 235 00:31:42.240 --> 00:31:51.319 Joanne Leary: And… When you've got a cross join that says, with ordinality, Then you can, 236 00:31:52.920 --> 00:31:54.150 Joanne Leary: Where the heck is it? 237 00:31:55.230 --> 00:32:02.970 Joanne Leary: I don't have it in here. Well, you can put, as part of your select statement. 238 00:32:03.320 --> 00:32:05.240 Joanne Leary: Right underneath this stuff. 239 00:32:05.970 --> 00:32:10.190 Joanne Leary: You can say values.ordinality. 240 00:32:10.590 --> 00:32:15.959 Joanne Leary: Which will give you the ordinality number for each of these values that it extracted. 241 00:32:16.710 --> 00:32:20.270 Joanne Leary: Okay, I'm sorry I didn't put that in. But anyway. 242 00:32:20.400 --> 00:32:26.459 Joanne Leary: Extracting all this stuff in the little query that I have up here gives you this result. 243 00:32:26.800 --> 00:32:34.750 Joanne Leary: And so you have your custom fields ID, the ref ID, whatever that is, custom fields name. 244 00:32:35.100 --> 00:32:40.130 Joanne Leary: then the value, and the value name. So… 245 00:32:40.610 --> 00:32:49.550 Joanne Leary: That's enough to confuse anybody, but there it is. Now the other thing that you might find is 246 00:32:49.730 --> 00:33:01.019 Joanne Leary: an array… that has… just square brackets. It looks like it's a top-level extract, You know, it's like… 247 00:33:02.870 --> 00:33:05.540 Joanne Leary: It doesn't have to be dug into. 248 00:33:06.760 --> 00:33:13.999 Joanne Leary: Except that… This thing that you have at the top level is not a nice text field like this. 249 00:33:15.050 --> 00:33:21.770 Joanne Leary: It's in… Square brackets, which means that it has an array of values. 250 00:33:22.140 --> 00:33:29.170 Joanne Leary: So, you will have to do another cross-joint statement in order to get it out of there. 251 00:33:30.730 --> 00:33:40.439 Joanne Leary: And… you know, I'm… I'm doing… I… I really like this… this thing here. 252 00:33:40.620 --> 00:33:43.120 Joanne Leary: So, I'm going to be using that. 253 00:33:43.370 --> 00:33:47.189 Joanne Leary: But, we have to jump down… 254 00:33:47.710 --> 00:33:52.529 Joanne Leary: into the from statement. So this is from the user users table. 255 00:33:52.850 --> 00:34:03.140 Joanne Leary: 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. 256 00:34:03.300 --> 00:34:05.580 Joanne Leary: Then you tell it the path. 257 00:34:06.330 --> 00:34:09.620 Joanne Leary: And in this case, it's at the top level. 258 00:34:09.860 --> 00:34:19.290 Joanne Leary: And it's called departments, right? Because up here, we saw this… Top level, and it was… Departments. 259 00:34:20.110 --> 00:34:25.480 Joanne Leary: So you're gonna do that, and in this case, I did put the ordinality. 260 00:34:25.820 --> 00:34:32.590 Joanne Leary: And this extracted object, then, I just named departments DEPTS. 261 00:34:32.850 --> 00:34:37.890 Joanne Leary: So, up here in the SELECT statement, I'm saying, 262 00:34:41.219 --> 00:34:47.280 Joanne Leary: Here, This is the department's object that I created through the crossjoin. 263 00:34:47.810 --> 00:34:54.069 Joanne Leary: And… this… This stuff here, these little curly brackets with nothing in them. 264 00:34:54.350 --> 00:34:58.990 Joanne Leary: It just means get whatever values are in there. 265 00:34:59.580 --> 00:35:09.190 Joanne Leary: As department ID. This extracts the array values from the department's JSON object created through the crossjoin. 266 00:35:09.340 --> 00:35:18.300 Joanne Leary: Note that the curly brackets are empty. This is because there is no tag for the values, it's just the values themselves. 267 00:35:18.380 --> 00:35:32.780 Joanne Leary: If there were a tag for the values, the tag name would go in the curly brackets. So, in this particular case, I just wanted to show it because it looks bizarre. It's just a set of empty brackets. 268 00:35:32.860 --> 00:35:41.020 Joanne Leary: But this is all you have to do if you see that, okay? You just put your curly brackets and leave them as is. 269 00:35:42.920 --> 00:35:43.870 Joanne Leary: Okay. 270 00:35:44.030 --> 00:35:45.170 Sharon Markus: Joanne? 271 00:35:45.970 --> 00:35:50.120 Sharon Markus: Lauren, I'm not sure if that's a question. 272 00:35:50.420 --> 00:35:53.449 Sharon Markus: But there's a comment in the chat, and she's saying. 273 00:35:53.450 --> 00:35:54.170 Joanne Leary: Oh, okay. 274 00:35:54.170 --> 00:36:01.099 Sharon Markus: She's saying, I was having trouble with a query where I needed to extract data from multiple arrays. 275 00:36:01.800 --> 00:36:03.310 Sharon Markus: From the same table. 276 00:36:03.390 --> 00:36:04.580 Joanne Leary: Yes, yes. 277 00:36:04.580 --> 00:36:07.020 Sharon Markus: Using multiple cross-joins. 278 00:36:07.080 --> 00:36:09.820 Joanne Leary: Yes. Ended up only giving me results. 279 00:36:09.820 --> 00:36:13.119 Sharon Markus: Where there was data in all three arrays. 280 00:36:13.120 --> 00:36:13.710 Joanne Leary: Yes. 281 00:36:13.710 --> 00:36:14.060 Sharon Markus: and not… 282 00:36:14.060 --> 00:36:14.710 Joanne Leary: That is… 283 00:36:14.710 --> 00:36:16.410 Sharon Markus: null values… 284 00:36:16.700 --> 00:36:25.909 Joanne Leary: Correct, and that is actually something I'm going to get to in a few minutes. That is a great observation. 285 00:36:26.530 --> 00:36:43.539 Joanne Leary: Cross-joins… if you use a cross-join in a query, it will get ONLY those records that have values for the crossjoin. So, in other words, if you have the inventory table, and that's got, like. 286 00:36:43.770 --> 00:36:47.990 Joanne Leary: I don't know how many of these embedded arrays in it. 287 00:36:48.210 --> 00:36:50.930 Joanne Leary: And you wanted to get 288 00:36:51.490 --> 00:36:57.939 Joanne Leary: Records that may or may not have values in those arrays. 289 00:36:58.250 --> 00:37:06.679 Joanne Leary: you have to do a two-part query, okay? Or maybe even three-part, but anyway, I will show that in a second. 290 00:37:07.230 --> 00:37:10.759 Joanne Leary: Let me just show you here… God, my… 291 00:37:12.380 --> 00:37:17.730 Joanne Leary: This is hard to… hard to follow. But, anyway… 292 00:37:18.610 --> 00:37:23.420 Joanne Leary: You got your departments out of this particular example. 293 00:37:23.870 --> 00:37:29.940 Joanne Leary: And… Okay, why is this not… Expanding. 294 00:37:30.350 --> 00:37:35.139 Joanne Leary: Okay, I'm not sure why this isn't… oh, okay, expand though. 295 00:37:35.790 --> 00:37:36.500 Joanne Leary: Come on. 296 00:37:36.500 --> 00:37:41.829 Sharon Markus: Yeah, there you go. Yeah, oh… Why is it not showing? 297 00:37:41.830 --> 00:37:47.590 Joanne Leary: It's probably… Oh, there you go. It just takes a minute. The connection is not great. Okay. 298 00:37:47.590 --> 00:37:48.530 Sharon Markus: it, yeah. 299 00:37:48.530 --> 00:37:57.060 Joanne Leary: Yeah, okay, so you… you see that the departments were extracted, and I put the ordinality in there so it tells you 300 00:37:57.250 --> 00:38:10.580 Joanne Leary: For each of these so far, in this screenshot, anyway, there was only one department associated with each user, so department ordinality is… is… is one. 301 00:38:10.850 --> 00:38:17.699 Joanne Leary: You might have users that have multiple departments, and so you would have, you know, 1, 2, 3, or something, but… 302 00:38:19.300 --> 00:38:21.819 Joanne Leary: Anyway, it comes out nice. 303 00:38:22.030 --> 00:38:27.859 Joanne Leary: So, let's see, what have I got here? 304 00:38:28.810 --> 00:38:34.090 Joanne Leary: Okay, the, you know, summarize, three ways to extract arrays. 305 00:38:34.260 --> 00:38:36.769 Joanne Leary: You don't have to use a cross join. 306 00:38:37.110 --> 00:38:43.490 Joanne Leary: Just use a triple nested JSON extract statement in the select clause. 307 00:38:43.810 --> 00:38:50.160 Joanne Leary: And, you have to trust me on this. This works. 308 00:38:50.290 --> 00:38:55.459 Joanne Leary: You can, essentially do what the cross-join did. 309 00:38:56.360 --> 00:38:59.119 Joanne Leary: But you do it in one… 310 00:38:59.510 --> 00:39:03.940 Joanne Leary: One nested statement instead of via the crossjoin. 311 00:39:04.740 --> 00:39:09.840 Joanne Leary: Okay, this has distinct advantages, because this will get you 312 00:39:10.020 --> 00:39:17.880 Joanne Leary: Those records that don't have values in that particular data array. 313 00:39:18.350 --> 00:39:24.470 Joanne Leary: Okay, so this is a good way to get things that have… that may or may not have a value. 314 00:39:24.930 --> 00:39:28.139 Joanne Leary: You can use the cross-join 315 00:39:30.120 --> 00:39:37.729 Joanne Leary: and put the result in a single-level JSON extract statement in the select clause. 316 00:39:38.320 --> 00:39:44.620 Joanne Leary: So, this is if you just want to have records that have these values. 317 00:39:53.110 --> 00:39:56.570 Joanne Leary: Now, I don't know what's happening here. Okay, I'm not… my… 318 00:39:56.920 --> 00:40:01.229 Joanne Leary: My view of what is on the page seems to be… 319 00:40:01.380 --> 00:40:06.200 Joanne Leary: a little bit spotty, because I think my connection is not great. 320 00:40:06.340 --> 00:40:14.189 Joanne Leary: But anyway, you can use the cross-join the same as in… example B here. 321 00:40:14.310 --> 00:40:19.419 Joanne Leary: But you can use that shortcut method, this thing. 322 00:40:19.820 --> 00:40:23.269 Joanne Leary: To get the, the values. 323 00:40:23.690 --> 00:40:24.310 Joanne Leary: No. 324 00:40:25.690 --> 00:40:27.100 Sharon Markus: Pounds greater than and greater than. 325 00:40:27.100 --> 00:40:35.339 Joanne Leary: Yes, exactly. And then this part, this number 4, is how you can 326 00:40:35.660 --> 00:40:39.299 Joanne Leary: Do this to prevent records from dropping out. 327 00:40:39.740 --> 00:40:41.790 Joanne Leary: And, 328 00:40:42.230 --> 00:40:55.120 Joanne Leary: 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-join statements in the FROM clause to get multiple array values. 329 00:40:55.220 --> 00:41:10.269 Joanne Leary: 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. So that's usually not great, because you want to see 330 00:41:10.430 --> 00:41:15.820 Joanne Leary: stuff that has them and that doesn't have them. So, 331 00:41:17.550 --> 00:41:26.310 Joanne Leary: Your first thing is you're going to create a two-part query. First, create a query that has the nested JSON extract statement. 332 00:41:26.670 --> 00:41:30.470 Joanne Leary: For each of the arrays that you're trying to extract. 333 00:41:30.810 --> 00:41:44.120 Joanne Leary: And… if you… if you do that long-nested JSON extract statement, that will get… 334 00:41:46.100 --> 00:41:52.870 Joanne Leary: the array values, for… The arrays that you're looking at. 335 00:41:56.040 --> 00:42:08.829 Joanne Leary: Gosh, I'm sorry. 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. 336 00:42:08.830 --> 00:42:11.089 Sharon Markus: It's staying on the ReportingSync page. 337 00:42:11.550 --> 00:42:12.770 Joanne Leary: Okay. 338 00:42:12.770 --> 00:42:15.309 Sharon Markus: Well, there was a little… little bit of blippiness, but… 339 00:42:15.310 --> 00:42:16.260 Joanne Leary: Yeah, there's… 340 00:42:16.260 --> 00:42:16.920 Sharon Markus: Okay. 341 00:42:16.920 --> 00:42:22.099 Joanne Leary: Sorry for the blippiness, and I'm sorry that this is such a complicated topic. 342 00:42:22.340 --> 00:42:23.940 Sharon Markus: Well, it's not your fault. 343 00:42:23.940 --> 00:42:35.550 Joanne Leary: Well… So, in this first half, I'm getting the array elements for contributors, languages, and subjects. 344 00:42:36.460 --> 00:42:46.670 Joanne Leary: So you can see, this, starting with this part right here. 345 00:42:47.460 --> 00:42:54.119 Joanne Leary: This is the nested JSON extract statement that gets the contributors. 346 00:42:54.490 --> 00:42:55.340 Joanne Leary: Right? 347 00:42:55.630 --> 00:43:01.050 Joanne Leary: This next one… is for languages. 348 00:43:01.670 --> 00:43:05.880 Joanne Leary: And this third one is for subjects. 349 00:43:09.270 --> 00:43:16.230 Joanne Leary: And then, the second half of the query here is, I'm going… going to go separately and, 350 00:43:16.700 --> 00:43:23.390 Joanne Leary: Get all the records from the instance table, And left join the results. 351 00:43:23.970 --> 00:43:27.640 Joanne Leary: To what you found in the first half of the query. 352 00:43:28.150 --> 00:43:32.710 Joanne Leary: So, I'm going down to the inventory instance T-Table. 353 00:43:32.930 --> 00:43:35.779 Joanne Leary: And then I'm going to left join. 354 00:43:36.280 --> 00:43:40.730 Joanne Leary: The values, from the first half of the query. 355 00:43:41.120 --> 00:43:45.009 Joanne Leary: That got all of the extracted arrays. 356 00:43:45.550 --> 00:43:52.060 Joanne Leary: And I'm going to put it all together, so when you left-join it to those extracted array values. 357 00:43:52.400 --> 00:43:56.830 Joanne Leary: You can get everything. You, you can get the, 358 00:43:59.080 --> 00:44:06.100 Joanne Leary: You can get records that have those values and that don't have the values in any combination. 359 00:44:06.270 --> 00:44:11.720 Joanne Leary: So you see that the, we have a title. 360 00:44:11.930 --> 00:44:22.209 Joanne Leary: 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, 361 00:44:22.460 --> 00:44:28.639 Joanne Leary: Astrophysics and cosmology, that does not have a contributor's value, so that chose is null. 362 00:44:28.750 --> 00:44:36.700 Joanne Leary: also doesn't have a languages, that shows as blank, and the subjects show as null. So that didn't have anything. 363 00:44:37.020 --> 00:44:46.580 Joanne Leary: 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. 364 00:44:46.700 --> 00:44:59.569 Joanne Leary: So this two-part method is the way you would go about getting, you get your array values, and then you get the entire table, and then left-join the array values to it. 365 00:45:00.160 --> 00:45:06.600 Joanne Leary: So, you know, I am sorry, this is just cancel, cancel, cancel… 366 00:45:10.520 --> 00:45:15.680 Sharon Markus: I think if you… just the X in the top right there, not the window, but… 367 00:45:17.510 --> 00:45:21.070 Sharon Markus: Oh, it looks like Joanne has frozen. -Oh. 368 00:45:21.460 --> 00:45:22.460 Sharon Markus: Hmm… 369 00:45:29.640 --> 00:45:30.750 Sharon Markus: Okay. 370 00:45:31.280 --> 00:45:33.840 Sharon Markus: Hmm. 371 00:45:34.880 --> 00:45:42.300 Sharon Markus: I'll see if I can… Let's see if she's… Trying to reach me. 372 00:45:48.590 --> 00:45:51.470 Sharon Markus: Is everyone else seeing a frozen screen? 373 00:45:52.590 --> 00:45:53.270 Sharon Markus: Oh. 374 00:45:54.780 --> 00:46:00.240 Sharon Markus: Okay, I'm… it looks like Joanne is choosing to log in again. Okay, thanks, Scott. 375 00:46:00.420 --> 00:46:09.510 Sharon Markus: Okay, yeah, and we have no share, and she is… she has had a problem with her network connection. 376 00:46:09.720 --> 00:46:16.969 Sharon Markus: So… Let me just see… 377 00:46:24.600 --> 00:46:29.530 Sharon Markus: Does anyone, have any questions while we're waiting for Joanne to pop back in? 378 00:46:37.850 --> 00:46:38.730 Sharon Markus: Okay. 379 00:46:40.720 --> 00:46:42.939 Sharon Markus: Alright, let me see what's happening. 380 00:46:46.180 --> 00:46:47.160 Sharon Markus: Oh. 381 00:46:47.280 --> 00:46:49.200 Sharon Markus: Okay. 382 00:46:51.840 --> 00:46:53.290 Sharon Markus: Computer died. 383 00:46:53.930 --> 00:47:02.030 Sharon Markus: That sounds serious. Okay. All right. Well… 384 00:47:02.470 --> 00:47:11.389 Sharon Markus: Let's see here if we can get this up, and I think she will continue to try to have her. Oh, you're back! 385 00:47:11.390 --> 00:47:14.509 Joanne Leary: recovered. Oh my god, it is. 386 00:47:15.220 --> 00:47:15.590 Sharon Markus: dear. 387 00:47:15.590 --> 00:47:16.899 Joanne Leary: Not good, not good. 388 00:47:16.900 --> 00:47:20.260 Sharon Markus: We need to get IT in on this. Yeah. We'll get support in. 389 00:47:20.260 --> 00:47:20.870 Joanne Leary: Oh, okay. 390 00:47:20.870 --> 00:47:21.540 Sharon Markus: Peter. 391 00:47:23.590 --> 00:47:24.759 Joanne Leary: Oh my god. 392 00:47:25.260 --> 00:47:30.599 Joanne Leary: Not a good time for it to go belly up. Okay. Let me get the link for you. 393 00:47:31.970 --> 00:47:36.949 Joanne Leary: I think… let's see, am I sharing my screen? Probably not. 394 00:47:36.950 --> 00:47:38.670 Sharon Markus: Not, not at this point. 395 00:47:43.320 --> 00:47:43.730 Joanne Leary: Mmm… 396 00:47:53.330 --> 00:47:54.410 Joanne Leary: Let's see… 397 00:47:55.980 --> 00:47:57.550 Sharon Markus: Aaron is… 398 00:47:57.550 --> 00:47:58.080 Joanne Leary: It's given you… 399 00:47:58.080 --> 00:48:00.160 Sharon Markus: the link. Thank you, Erin. 400 00:48:01.070 --> 00:48:05.269 Sharon Markus: Do you have the link for the page? It's right there in the chat. Erin, thanks so much. 401 00:48:05.830 --> 00:48:08.840 Joanne Leary: Okay, are we back? Do you see my screen? 402 00:48:09.240 --> 00:48:10.389 Sharon Markus: We're back! 403 00:48:10.880 --> 00:48:11.990 Joanne Leary: Okay. 404 00:48:12.160 --> 00:48:14.760 Joanne Leary: So we're, we're almost done here. 405 00:48:15.650 --> 00:48:16.830 Joanne Leary: Mom… 406 00:48:16.830 --> 00:48:24.310 Sharon Markus: We do have a question in the chat while you're finding your place, or I'll just wait for a second, so I'll… I could just tell you 407 00:48:24.500 --> 00:48:25.600 Sharon Markus: Hey, Lauren! 408 00:48:27.220 --> 00:48:29.560 Sharon Markus: So… 409 00:48:30.930 --> 00:48:43.099 Sharon Markus: Okay. Okay, so Lauren has a question. Oh, sure, other people are using lateral joins, but not cross joins to do multiple array extractions, or people think it's better to do the nested. 410 00:48:43.690 --> 00:48:45.260 Sharon Markus: extractions. 411 00:48:46.460 --> 00:48:49.050 Sharon Markus: Okay, so. 412 00:48:49.050 --> 00:48:50.539 Joanne Leary: I don't know. 413 00:48:51.240 --> 00:48:52.389 Sharon Markus: I don't know if people are. 414 00:48:52.390 --> 00:48:55.779 Joanne Leary: I… I don't know. I really don't know. 415 00:49:01.370 --> 00:49:07.640 Joanne Leary: I wish I could answer your question. I truly don't know. I don't know what the difference is. 416 00:49:09.600 --> 00:49:12.660 Sharon Markus: Between lateral joins and cross joins. 417 00:49:13.270 --> 00:49:25.700 Sharon Markus: I do remember that Nasib showed us the nested later as sort of a new and improved kind of thing, shorter way to do it, so… but, I don't know if it's necessarily better or what people are doing. 418 00:49:26.120 --> 00:49:28.000 Joanne Leary: I have no clue. 419 00:49:28.230 --> 00:49:29.350 Sharon Markus: Yeah. 420 00:49:29.580 --> 00:49:37.369 Sharon Markus: We didn't… we didn't have the instructions on the nested at first, and… and Missy gave us those later. 421 00:49:37.500 --> 00:49:43.579 Sharon Markus: And they're, they're kind of… I don't know, they're shorter, so a little less to type. 422 00:49:44.880 --> 00:49:46.360 Joanne Leary: What do you mean about this? 423 00:49:47.300 --> 00:49:56.870 Joanne Leary: shorter. Do you mean this, this, Table.jsonb, pound sign, Right out. 424 00:49:56.870 --> 00:49:57.810 Sharon Markus: Yes. 425 00:49:58.580 --> 00:50:03.570 Joanne Leary: Yeah, that… that shortcut is… it's just a shorthand, it's not, 426 00:50:05.580 --> 00:50:09.589 Joanne Leary: I don't think it's a, you know, thing that you have to use. 427 00:50:09.590 --> 00:50:10.260 Sharon Markus: Yeah. 428 00:50:10.260 --> 00:50:19.559 Joanne Leary: 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. 429 00:50:20.910 --> 00:50:26.199 Joanne Leary: Anyway, not to be a dead horse, but anyway, this last bit 430 00:50:26.420 --> 00:50:33.349 Joanne Leary: Is, revising your query to get data array extractions instead of derived tables. 431 00:50:33.750 --> 00:50:45.870 Joanne Leary: 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 432 00:50:46.000 --> 00:50:56.040 Joanne Leary: For your purposes, and one thing that I have looked into Is, overdue loans for… 433 00:50:56.320 --> 00:51:09.309 Joanne Leary: 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, 434 00:51:10.130 --> 00:51:17.330 Joanne Leary: 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. 435 00:51:17.610 --> 00:51:22.110 Joanne Leary: So, you might want to use, a data array extraction. 436 00:51:23.120 --> 00:51:35.360 Joanne Leary: 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 loan and, 437 00:51:38.010 --> 00:51:42.640 Joanne Leary: The, the date and time for, 438 00:51:42.850 --> 00:51:46.939 Joanne Leary: For when it was checked out, and when it was due. 439 00:51:47.520 --> 00:51:53.809 Joanne Leary: But again, you know, this is yesterday's data, so it's only going to take you so far. 440 00:51:54.090 --> 00:51:55.850 Joanne Leary: And, 441 00:51:56.820 --> 00:52:04.179 Joanne Leary: Then, to get… to get it out of the primary tables, the loan table and the user's table, which is what you have to do. 442 00:52:04.330 --> 00:52:13.209 Joanne Leary: You would then have to use your, your JSON array extracts. In particular, for the users table. 443 00:52:13.940 --> 00:52:15.780 Joanne Leary: You have to go into the… 444 00:52:15.960 --> 00:52:19.169 Joanne Leary: In… into the data array, and you dig down. 445 00:52:19.350 --> 00:52:22.680 Joanne Leary: To the part that's the personal chunk. 446 00:52:22.790 --> 00:52:25.230 Joanne Leary: And you get the first name and last name. 447 00:52:26.690 --> 00:52:27.900 Joanne Leary: And… 448 00:52:30.530 --> 00:52:35.919 Joanne Leary: These are all the tables that you have to use. You gotta use the loan table, the users table. 449 00:52:36.030 --> 00:52:37.790 Joanne Leary: the item table… 450 00:52:38.300 --> 00:52:51.140 Joanne Leary: material type table, because these are all things that the derive table did for you, but the derive table didn't go down into the nitty-gritty of the username, and it did not, 451 00:52:52.390 --> 00:52:57.999 Joanne Leary: It did not give you up-to-the-minute data, which is the big thing with this type of query. 452 00:52:58.450 --> 00:53:07.129 Joanne Leary: So… You'll notice that this query is longer. This is, like, 64 lines long. 453 00:53:07.780 --> 00:53:13.979 Joanne Leary: Whereas the other query was only 41 lines long, and that's because you're using more tables. 454 00:53:14.480 --> 00:53:32.320 Joanne Leary: 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, and, when that happens, usually the query takes longer to run. 455 00:53:32.740 --> 00:53:34.950 Joanne Leary: And it… that may not be… 456 00:53:35.070 --> 00:53:49.099 Joanne Leary: much of a difference. 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 drive table query does. 457 00:53:51.520 --> 00:54:05.979 Joanne Leary: 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 458 00:54:06.160 --> 00:54:20.709 Joanne Leary: Or… 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 derive table code and put that into your query, just cut and paste. 459 00:54:21.030 --> 00:54:29.909 Joanne Leary: 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. 460 00:54:30.210 --> 00:54:32.760 Joanne Leary: Now, that only works if 461 00:54:32.900 --> 00:54:39.330 Joanne Leary: Your… if the derived table has everything that you need, which, for me, doesn't happen a lot, but… 462 00:54:39.490 --> 00:54:44.100 Joanne Leary: And then one final thing is up here at the top. 463 00:54:45.210 --> 00:54:47.559 Joanne Leary: If my computer ever gets there. 464 00:54:47.700 --> 00:54:50.630 Joanne Leary: I want to show you, here's a cheat sheet. 465 00:54:51.290 --> 00:54:59.419 Joanne Leary: Okay, which is… handy, because it takes all the junk that I was talking about and shows you 466 00:54:59.530 --> 00:55:06.380 Joanne Leary: In a little… Snippet… the most… common… 467 00:55:06.640 --> 00:55:15.770 Joanne Leary: 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. 468 00:55:16.120 --> 00:55:23.539 Joanne Leary: It's got all this stuff, and then here's how you would extract it out of the notes array. This is the formula. 469 00:55:23.770 --> 00:55:28.859 Joanne Leary: And then here's an alternate method, is instead of using the… 470 00:55:29.710 --> 00:55:36.220 Joanne Leary: the long-nested JSON statement, you can do the cross-join, and that… that will get you 471 00:55:36.350 --> 00:55:41.660 Joanne Leary: That will extract anything that is in the array, but again, it doesn't… 472 00:55:41.870 --> 00:55:47.650 Joanne Leary: Doesn't get the records that don't have a notes field, an array notes field. 473 00:55:47.830 --> 00:55:49.260 Joanne Leary: value. 474 00:55:49.880 --> 00:56:03.000 Joanne Leary: 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, arrays that you'll come across. 475 00:56:03.240 --> 00:56:06.360 Joanne Leary: So, that is it. 476 00:56:06.970 --> 00:56:11.410 Joanne Leary: And I apologize for all the… 477 00:56:13.590 --> 00:56:26.390 Joanne Leary: 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. 478 00:56:26.540 --> 00:56:37.609 Sharon Markus: But let's, go back to Lauren's question. She was wondering if people are using the lateral joints, but, not cross joins. 479 00:56:37.750 --> 00:56:41.500 Sharon Markus: To do multiple array extractions. 480 00:56:44.890 --> 00:56:45.760 Sharon Markus: Okay. 481 00:56:46.120 --> 00:56:48.500 Sharon Markus: So she's saying she's, 482 00:56:49.070 --> 00:56:54.640 Sharon Markus: You do a left-join lateral instead of a cross-join lateral, but it'll return the null values. 483 00:56:55.830 --> 00:56:59.259 Sharon Markus: Not sure… Oh, that's just true. …understands how they work. 484 00:56:59.430 --> 00:57:05.910 Sharon Markus: So, yeah, don't know if anyone has had some experience. 485 00:57:05.910 --> 00:57:08.500 Joanne Leary: That's… that's good. I… I didn't realize that. 486 00:57:08.740 --> 00:57:10.190 Joanne Leary: I didn't realize that. 487 00:57:12.380 --> 00:57:13.170 Joanne Leary: doing, like. 488 00:57:21.220 --> 00:57:24.290 Joanne Leary: I can't hear whoever's talking. 489 00:57:24.290 --> 00:57:26.610 Sharon Markus: Is, Lauren, are you talking? 490 00:57:26.950 --> 00:57:31.059 Sharon Markus: We can't hear you at all. I mean, it's sort of like… like that. 491 00:57:33.860 --> 00:57:35.790 Sharon Markus: Lauren, do you want to unmute? 492 00:57:36.970 --> 00:57:41.279 Joanne Leary: Oh, she needs to troubleshoot her mic, okay. Oh, okay. Okay. 493 00:57:41.530 --> 00:57:45.370 Sharon Markus: You were on your mute. Yeah, we could hear, like, teeny tiny little sounds. 494 00:57:46.260 --> 00:57:47.620 Sharon Markus: Okay… 495 00:57:52.270 --> 00:57:53.610 Sharon Markus: Okay… 496 00:57:55.400 --> 00:57:56.500 Lauren Schiller: How about now? 497 00:57:57.360 --> 00:57:59.409 Sharon Markus: Much better, much better. 498 00:57:59.410 --> 00:58:02.349 Lauren Schiller: So, I was, I was working on a query, 499 00:58:03.070 --> 00:58:22.220 Lauren Schiller: like, two weeks ago, which had multiple array extractions, and so I discovered this thing, because, like, I'd previously been doing multiple array extractions and, like, like, sub-queries or, like, creating subtables, and that, that also works fine, although possibly slower. 500 00:58:22.790 --> 00:58:34.980 Lauren Schiller: but I… I was ex… I… trying to look things up, and it's like… like, doing, like, left join lateral. And so, like, I have… the query was to get, 501 00:58:35.400 --> 00:58:46.200 Lauren Schiller: 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 502 00:58:46.200 --> 00:58:55.990 Lauren Schiller: on the records that had those identifiers, which, you know, may or may not have admin notes, or statistical codes, which was the third thing I wanted to do. And so, like, those… 503 00:58:56.000 --> 00:59:11.580 Lauren Schiller: two things could have null values, but the identifiers 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 504 00:59:11.580 --> 00:59:17.560 Lauren Schiller: what the lateral join is doing, but it doesn't, like, cross-join lateral… 505 00:59:17.850 --> 00:59:24.190 Lauren Schiller: is things that only have those values, and the left join lateral is, you know, matches the same. 506 00:59:24.190 --> 00:59:25.360 Joanne Leary: very good. 507 00:59:25.360 --> 00:59:32.269 Lauren Schiller: On the first, like, like, like a normal left join, but, like, what does a lateral join actually mean? 508 00:59:33.090 --> 00:59:34.319 Joanne Leary: I have no clue. 509 00:59:34.320 --> 00:59:36.730 Lauren Schiller: We're all confused. 510 00:59:36.730 --> 00:59:39.410 Joanne Leary: I don't know, I mean, I… 511 00:59:40.060 --> 00:59:55.090 Joanne Leary: 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. 512 00:59:55.620 --> 01:00:02.950 Joanne Leary: But… I'm glad that you mentioned that, because that's… that actually… I'm gonna try that, and 513 01:00:03.350 --> 01:00:10.430 Joanne Leary: See if that solves this problem of records dropping out, and maybe, you can just use that. 514 01:00:10.580 --> 01:00:17.090 Joanne Leary: Instead of… Doing the methods that I outlined here, you know? 515 01:00:17.090 --> 01:00:23.740 Lauren Schiller: Yeah, that's why I was always curious, like, if other people had tried that, because, like, it's like, it is doing the nested thing 516 01:00:23.930 --> 01:00:26.009 Lauren Schiller: Faster or slower than the left join. 517 01:00:26.010 --> 01:00:26.340 Joanne Leary: It doesn't. 518 01:00:26.340 --> 01:00:27.340 Lauren Schiller: The same result. 519 01:00:27.340 --> 01:00:27.880 Joanne Leary: Right. 520 01:00:27.880 --> 01:00:33.600 Lauren Schiller: looks… Like, to me, it looks neater to do the cross join and the left join laterally. 521 01:00:35.600 --> 01:00:43.560 Lauren Schiller: But if the nested is faster, then maybe I want to do that instead. So that's… so, like, this is something that we, like, I just… we, like, just discovered. 522 01:00:43.560 --> 01:00:44.040 Joanne Leary: That's true. 523 01:00:44.040 --> 01:00:47.699 Lauren Schiller: That number and try to figure out, like, what is this actually doing? 524 01:00:48.250 --> 01:01:06.240 Joanne Leary: Oh, that's… that… thank you so much for that, hint. I'm gonna try it and see if it gets me anywhere, yeah? Or rather, it gets me the results that I expect. I'll do a compare and contrast, you know? I'll see if it, does the same. 525 01:01:06.690 --> 01:01:14.060 Sharon Markus: Yeah, that would be a good thing to add to the documentation if, you know, if it turns out to be a good way to do things. 526 01:01:14.950 --> 01:01:22.400 Sharon Markus: I have here a lateral join in SQL is a special type of join, this is just a Google search, a special type of join that allows a sub… 527 01:01:22.730 --> 01:01:29.849 Sharon Markus: query in the FROM clause to reference columns from a table expression that appears before it in the FROM list. 528 01:01:30.690 --> 01:01:34.739 Sharon Markus: So… And then there's a lot more about it, but . 529 01:01:35.140 --> 01:01:35.960 Joanne Leary: Okay. 530 01:01:36.170 --> 01:01:40.170 Sharon Markus: Yeah, so we can… But. 531 01:01:40.170 --> 01:01:41.280 Joanne Leary: That's good. 532 01:01:41.460 --> 01:01:42.040 Sharon Markus: Yeah. 533 01:01:42.540 --> 01:01:46.259 Sharon Markus: So we can, maybe update the page a little bit more. 534 01:01:46.580 --> 01:01:48.330 Sharon Markus: Any other questions? 535 01:01:51.670 --> 01:01:55.289 Sharon Markus: Is everyone's minds spinning? 536 01:01:55.910 --> 01:02:11.540 Sharon Markus: Well, Joanne has just been, incredible to, untangle this for us at Cornell, and Joanne, I really appreciate you contributing this to the reporting today, because the question had come up a couple times, you know, could we 537 01:02:11.540 --> 01:02:19.319 Sharon Markus: 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, 538 01:02:19.560 --> 01:02:33.350 Sharon Markus: To, 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 you can start to feel more comfortable with them, so… 539 01:02:33.610 --> 01:02:37.649 Sharon Markus: Joanne, thank you so much, for putting all that information together. 540 01:02:37.880 --> 01:02:40.609 Sharon Markus: This is all in the reporting SIG. 541 01:02:40.780 --> 01:02:58.390 Sharon Markus: wiki, so, you can… it's, very easy to find extracting. And I think there's a link that we put in the chat earlier, but, you can always reach out if you, are looking for that link and missing it. Anything else, Joanne, before. 542 01:02:58.390 --> 01:03:15.300 Joanne Leary: No, I am, 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… 543 01:03:15.300 --> 01:03:15.870 Sharon Markus: Oh, no. 544 01:03:15.870 --> 01:03:18.300 Joanne Leary: I hope you'll find some of it helpful. 545 01:03:18.790 --> 01:03:31.149 Joanne Leary: Yeah, I mean, if you can extract your own arrays, there's nothing you can't do. I mean, really, that's… your queries will be yours to command. 546 01:03:31.490 --> 01:03:34.050 Sharon Markus: Well said, well said. 547 01:03:34.880 --> 01:03:45.760 Sharon Markus: All right. Well, thanks everyone for joining. We'll see you next time at the Reporting SIG, and I hope you have a great week. Take care.