JSON in PostgreSQL

[00:00:00]
>> At this point, we've done pretty extensive querying coverage for SQL. So you can take pretty much all these skills and move directly over to MySQL, probably Oracle and some of these other SQL based databases and just fly with it right. So now I've kind of, I've taught you generic SQL stuff.

[00:00:23]
I'm going to move you into. Postgres stuff, specifically Postgres stuff. And this is the part that like, these are the reasons why I chose Postgres to teach you versus MySQL and some of those other ones. So one of the really cool features of Postgres is it's kind of ends up being a multi paradigm database because it has the ability to do unstructured data within SQL.

[00:00:53]
And it's kind of a fascinating way they chosen to do it. You can actually store JSON as a data type in Postgres. And then Postgres knows how to query JSON for you. So you can have it this mixed, like schema and schemaless data that lives side by side in the same thing.

[00:01:13]
So that's the cool thing about this JSON data type. Type is a doesn't have to have a schema so you can put whatever you want in it. And it kind of allows you to kind of blur that line between what a document is and what a record is across document and SQL based databases.

[00:01:31]
This is, as far as I know, very unique to Postgres. I don't think this exists in many SQL types. So, one of the In fact, let's go back over here if we look at this you can see we have this rich content table if I only put five records in here so you can if you want to look at it, just say select star from rich content.

[00:02:00]
And you'll see here it has a Content ID a comment ID, and has content in it. So the content ID is that it's unique. In fact, let's go back over here is it It's a unique primary key. The comment ID refers to a comment. And then the content is a JSON data type and it's not known.

[00:02:26]
So imagine you're making a message board anyone had the ability to embed videos embed polls, and embed images, you wanna have this rich content experience. The way that you can do that is with this JSON data type, because these things are gonna be kinda amorphous, right? So if you look down here, I have several of them out here.

[00:02:49]
This one's gonna be a poll and it's gonna have a question, it's gonna have options. Or this one's going to be a video and it's going to be URL and it's going to dimension to those videos. So these various different types of Rich Content are going to have different kinds of things that are going to associate with it.

[00:03:08]
So it's going to have this kind of, it needs a schemaless way of expressing. Our other option is to have multiple tables. One describes poles one describes videos. One describes images and then we're going to have to have some sort of like connecting table in the middle. That's going to be just a comment idea of links to a particular type of other common or like Rich Content ID, you can express this in tables but it gets messy and gross and involves a lot of tables.

[00:03:37]
This is really nice because we can just put everything in the same table and just express it as JSON. So that's kind of the use case for this one is I have heterogeneous data is I guess how I would describe that like data that should live in the same table.

[00:03:51]
It's of a similar thing, collection variety, but might have different dimensions to it. This is exactly what JSON is made for. So, the symbols that you need to get used to are these arrow types. So let's just go ahead and go write one right now. So we're going to say select, content, which is the name of the JSON.

[00:04:22]
Column, right content, this one here, but I'm going to put minus and then I'm gonna put angle bracket now, my font here puts these down as one glyph. But just know this is actually it's my font combining them into one. glyph right. This is with the. Cascadia code is a free font put out by Microsoft.

[00:04:46]
So that's what that arrow means. It just makes it more readable for me. And then I want to select the type here, right? So this is something inside of that JSON object. So I'm going to select type Form. Which under store content so now this is going to go through each one of these and it grabs out pole and video and pull Image and Image, right?

[00:05:15]
Which makes sense if you look at these pull video pull image and the question mark column there We didn't give it a name. But we can go in here and say as you know, content type right? And now it's actually called content type look a little bit more readable, right?

[00:05:37]
This is great. This is really cool when we're now querying JSON out of Postgres. Another thing that this will do for you this JSON data type, it will enforce that you're putting in proper JSON. So if you try and Feed it like a string of not well formed JSON, it will rebel and say, no, you won't do this.

[00:05:53]
This must be valid JSON. Let's fathom for a moment that I'm making some sort of like drop down selector for my rich, you know, experience to create these rich content. And I want to have a drop down of all the various different types that I can support. Well, I could do this.

[00:06:09]
This is exactly what I did. But I have image in here twice. I have pulled her twice. It'd be nice if I could just select the distinct ones right? Well, lo and behold there's something unsurprisingly called select distinct like this. And actually that that's a problem and I meant to show you that so what distinct does is like alright distill this down to just the ones that are you know the same one so what we wanna get back as poll video and image and we wanna leave out the duplicate so we wanna D duplicate this set.

[00:06:49]
It'd be nice if we could do, this, but the problem is that the type that you get back here, we assume that this is a string. It looks like a string here, but the problem is, is it's actually not correct quite still actually a JSON blackbox to postgres.

[00:07:05]
It doesn't know what to do with these. You can see here it says, I don't have an equality operator from type JSON because it doesn't know what it is and refuses to tell you what it is because it just won't it doesn't know It could be something different from every single one.

[00:07:18]
Now we could do something like this where we say cast, Type, as text. Like that, and this will say, hey, postgres, I'm positive. What's coming out of here is text us all the text operations here, and that'll end up working. Right? And that's what this does. Now it works.

[00:07:43]
Now we're getting distinct. Just so you know. This is the first time I'm showing you distinct but distinct works everywhere. Select distinct just will d duplicate whatever returns to you. So, This is burdensome, right? This would be nice if we could just get it as text in the first place and we didn't have to do this all this casting business.

[00:08:04]
Well, you can if you put a second angle bracket there, which again, I promise you, this is three glyphs together if A minus sign and two angle brackets, which my font combines into this pretty arrow. And it's just saying, whatever you select out of this, just cast it as a text immediately.

[00:08:26]
And you can see there, it does actually end up doing that you can see it even lacks those quotes. Again, this is Postgres saying, I don't know what comes out of JSON. I don't you have to tell me explicitly what is coming out of JSON or I don't know what to do with it.

[00:08:44]
Whereas this one is just saying. I know this is text cast it as text, because you have to imagine, I mean, let's let's do this, right. If I Select content if I select dimensions. These ones don't have dimensions. If you go back and look at these up here Images and videos have dimensions polls do not.

[00:09:12]
So these come back as no and these come back as objects. And this is Postgres. Just saying, I will give you whatever you ask for. But if you asked me to do competitors and operations in addition and stuff like that, you got to tell me a friend, I'm getting a string, I'm getting a number, that kind of stuff.

[00:09:26]
>> It does the output from Postgres, say five rows and Wi Fi, were returned. No values comes as rows discuss?
>> Yeah. So in this particular case. There's these two poles don't have heights and widths, but they still get returned cuz we're not trying it to exclude anything.

[00:09:44]
So it's gonna return something from everything. And it just won't return. It'll return null for those ones. So this is a no. So if you wanted to get from rich content where content dimensions think you have to do this is not no and there you go. So you have to tell us like specifically, I don't want any North things back.

[00:10:18]
That makes sense.
>> Thank you.
>> Yep. Good question. Other questions. All righty Like you kind of spoiled my next little section there. So thanks for that. Just kidding. That's a good question. But what I wanna do right now I want to go and select all the heights and widths for things that have heights and widths.

[00:10:51]
So what I want to do is I want to say select content. Then I want to select from dimensions. Because notice that that's nested, right? And inside of 'dimensions' I wanna get height, Height' As height. So notice the first one here, this one, I'm using a single arrow.

[00:11:22]
Cuz I still want the JSON object, I still wanna do I want to access something deeper in it. So I do single error here. And then here, I know this is the terminal of this particular,j ason tree that I'm traversing. So I'm asking for the height back. This will give me the height back as a string, and I'm using that as height.

[00:11:44]
We'll do the same thing for a width. I need to do dimensions, dimensions width as with. Okay, from rich underscore content where and this is where I'm going to say content is not no. So where content dimensions is not Nmo. And here you can see, this will give me back all of the dimensions of my various images and videos that have them.

[00:12:44]
t's going to exclude all the polls that don't have dimensions associated with them. But I wanted to show you here, this is how you do multi level, right because these things can have multiple layers of depth. They can have arrays, they can have all sorts of stuff as well and this is how you do that.

[00:13:01]
And what I was originally intending to do just put in here. Fine, I can do that. Comment ID and this will give you back as well. The comment ID.