Adding JSONB to Recipes Table

[00:00:00]
>> The first thing that we're gonna do is we're gonna alter our table recipes to add this JSON B column and we're gonna just gonna call it meta. You don't really have to give it much more information than that. You just have to say like, I'm gonna give you a column.

[00:00:13]
It's gonna be called meta and it's gonna be a JSONB1, and then at that point, it's kind of unbounded. You can make these columns as big or as small as you want. So let's head back over here to our database. You can see here I still have all of my tables.

[00:00:26]
So I'm gonna say ALTER TABLE recipes, ADD COLUMN meta. I just call it meta cuz you could have anything in here like any sort of like metadata, but this is just the name of the column. You can call whatever you want. And there's nothing special about meta, either the name or the company, JSONB.

[00:00:50]
And you can see now if I say describe recipes, I have this meta column that is of type JSONB. Now imagine if we wanted to add tags to our recipes. This is gluten free, this is good for keto, this is chocolaty, things of that nature. That's what I'm gonna use this for.

[00:01:13]
So I'm gonna say UPDATE recipes. I'm gonna set meta equal to you're gonna put a single quote here still, right? It's basically gonna take your string that you provide to it and then it will process it. And then it's just valid JSON at this point. So you need to use double quotes appropriately and things like that.

[00:01:37]
Like it's not a JavaScript object. It's actually JSON tags. And then we're gonna get a better array of chocolate, dessert, And cake, Okay? And so that is what I'm gonna put there. Then I'm going to say WHERE recipe_id=16. So I updated exactly one column here where the recipe_id is 16, and I set the meta to this.

[00:02:26]
So let's just see what that looks like. I actually do a couple more here. So if you followed along with me, just grab a few more of these here. It's kinda the same thing, I just wanted you to have a few to query. So I'm just gonna copy and paste those in there, hit Enter, and you can see there I updated three more.

[00:02:48]
Actually, you can copy and paste all of them cuz that first one you'd just be setting it again to the same thing, doesn't matter. So first thing, let's just say SELECT meta FROM recipes WHERE, and you can just say where, WHERE meta IS NOT NULL if we just want to look at all of them right now, right?

[00:03:11]
Cuz the rest of them, it'll be null cuz we haven't set them yet. And you can see here it actually just gives you back this like JSON object. Notice it's not a string, it actually is giving you back this like JSONB object. Now, let's say if I wanted to select just the tags cuz you might imagine, this is like meta info.

[00:03:29]
This is a JSON object. We can put anything in here, right? We could add another column, we can take back another column. It's like JSON, right? It's a JavaScript object that we can just keep adding and deleting things from without altering our table. Right now it has just one top level key, but we can have many top level keys.

[00:03:47]
Okay, so let's say I just wanted to select from meta here. I wanna select just the tags. I'm gonna use this arrow, so it's a dash and then a right pointing angle bracket, and I'm gonna select just tags. And I need to put that into a single quote here because it's actually the value that we're looking for.

[00:04:10]
And then here now notice I'm getting back these arrays. I'm not getting back the whole object. I'm just getting back the array. We selected just the tags out of meta, right? So you can see here we're getting back just the array. What if I want just the first one, right?

[00:04:23]
I just want the first thing to come back. So I want dessert, dessert, chocolate, and dessert. You can actually keep going here, so I just give it another arrow, and asked for 0. You can see here now I'm getting back, dessert, dessert, chocolate, and dessert. As you may imagine, you can do 1 as well.

[00:04:47]
If you get 2, you should get probably empty strings I imagine, let's see. Yeah, empty string, empty string, this one had cake so it actually did have a third one, so I got cake back here and then I got nothing. If I asked for the tenth one it would just give me back a bunch of empty strings from where meta is not null.

[00:05:05]
A couple things to notice, first of all, notice it give me a question mark column question mark because it actually doesn't know what this is called so you actually should give it as like an AS, right? So here you should give it like AS first_tag, so it actually has like a good useful name up here, right?

[00:05:22]
Otherwise you get that weird question mark column thing. The other thing to notice here is it's giving you back still this is technically a JSON string, right, cuz it's thinking is like you might query like later and more deeply into this. So on your final terminal thing, we actually tried to get out just the thing that you want.

[00:05:40]
Give it to angle brackets like that. And it'll actually give you back, then it's like, okay, you're actually getting back a real string now, right? This isn't a JSON string, this is just text. In our case, it doesn't really matter because we're just reading out of this. But when you're actually trying to get it out of your database and into your code, it actually will make a difference.

[00:06:04]
So, yeah, the last thing that you're trying to get out here when you're actually trying to get a string out of it, you'll use this double angle bracket looking thing here. So that's that. You saw that. You saw here. We can get the different array indexes. And it's the same thing.

[00:06:21]
Imagine you have nested objects. Because you can have infinitely nested objects. You could have user, then address, then state. You could go multiple layers deep inside of that using keys as far as, it's just JSON. Okay, so let's say that we have search facets on the sidebar application like our recipe website, right?

[00:06:49]
And we wanted to look for only the cakes, right? We want to exclude all the other dishes we wanna search by tag. So we want to search into this JSON object to grab only the cakes. So what we can do here is we can say, and I'll just clear this so you can see at the top, SELECT recipe_id, title, and then meta.

[00:07:14]
We're gonna grab just to all of the tags, so you can see that we're not lying here. FROM recipes, so we don't want that last comma, FROM recipes WHERE meta tags. And then you can do this question mark to say I want to find ones that have cake.

[00:07:44]
So select from recipe title or meta tags, right? So you can see that getting all of those here. FROM recipes, and then I'm selecting into meta where it's looking into the tags, right, and it's asking do you contain cake? Something I frequently ask a lot of things, do you contain cake?

[00:08:05]
And so it's nice it's not giving back to the other ones, right, like if you go back up here, this is a dessert fruit so doesn't give me back this one, this one's dessert fruit. It doesn't give me back that one, right, it gives me just the two that have cake in the text.

[00:08:23]
So that's what that question mark does, is asking do you have any cake? So specifically what that question mark is asking is do you have a top level thing called cake? So in theory, if I just didn't meta here, And I could ask it give me things, only things that have tags.

[00:08:55]
Notice we got back all four of them because they all have tags. If I have a different thing that in a different column that had meta but didn't have tags, I wouldn't get that back, right? So that's what this question is asking, is like give me everything that has a top level thing called or Tableau key, which works with arrays, right?

[00:09:14]
That contains tags. If you wanna do a specifically in an array contains value there is a thing for that as well. So what you do is, meta, and then you can do this like ampersand angle bracket, and here you would ask for, I think you have to put double quotes inside of this.

[00:09:44]
Let me check if that's actually true, no.
>> Should it be meta tags?
>> Yeah, not meta meta. Thank you. There you go, thank you. So here we're asking for meta, we're asking for tags, and then we're going, does your array contain the string cake? That's where the outermost single quotes means you're looking for us Some sort of string inside of Postgres.

[00:10:16]
So the inner double quotes means I'm looking for the array in a string cake.