JSON Operations

[00:00:00]
>> Brian Holt: Let's talk about how to manipulate things, cuz that becomes really important. Let's say that you know BT Holt is browsing on your website and he wants to, update or add his address to it, right so we can do select JSON, insert. And you would do, let's get that whole, where is this?

[00:00:31]
>> Brian Holt: So we have this object, we give it there, we have that, and then you wanna tell what you wanna insert, so I'm gonna put in I want to put in a city, right. And then you tell it what you wanna put in there, and I wanna put in Sacramento.

[00:00:51]
>> Brian Holt: Okay, so what do I expect to get back? I expect to get back this object that I've defined up here, but it has one more key in it, which is city and the value of that city is Sacramento. Lo and behold, there you go, worked okay, right?

[00:01:07]
Remove is kind of similar as well, so I'm gonna do select, JSON, remove, I'm gonna need a single quote there.
>> Brian Holt: Yeah, I don't think I mentioned this anywhere else, but just as a pro tip, if you ever need to put single quotes inside of the single quotes, double single quotes, right.

[00:01:35]
So if I wanted this to end with a double or with a one quote, you have to put two quotes, and that's how you do a single quote inside of a SQLite query string.
>> Brian Holt: Cool, there's that remove, and then I want to remove favorites, so I would do $.favorites.

[00:01:58]
What do I expect to get back? I expect to get back this particular object, but lacking favorites because I told it to remove it with this JSON remove function, and that's what I got.
>> Brian Holt: And JSON replace, pretty similar as you might imagine, you just wanna update the username or something like that.

[00:02:25]
>> Brian Holt: So there's replace, we're gonna update username.
>> Brian Holt: And we are gonna update it to holt bt because I always wanted I wanted btholt on Twitter and it was taken By someone that hasn't tweeted in like a decade, not bitter. And so I have to go by HoltBT on Twitter, there you go.

[00:02:47]
So this is just all the functions we'll get back into actually how you use them with the database here in just a second. I just wanted you to be exposed to all the various different ways to craft a JSON object to be used. Now I'm gonna show you how to do accessors, which is actually part of the part that you'll be using quite a bit.

[00:03:09]
>> Brian Holt: Okay, so let's grab our,
>> Brian Holt: This thing again, this one, okay, we have a object here. What happens, I'll make this a little bit wider so you can see it's all one line.
>> Brian Holt: Okay, so I have this JSON object and I want to get btholt out of it, right, or I want to get the username out of it, right.

[00:03:36]
So maybe I'm selecting from my database and I want to grab the user's name of all the people in my users database. What would I do? Well, there's this like really convenient way of doing it with SQL, and there are these accessor arrows. So again, this is just my font combining them, it's a minus sign and an angle bracket next to each other, which it combines into a nice looking arrow, which is nice.

[00:03:59]
And you just give it that, and whatever you want out of it,
>> Brian Holt: Okay and if I run that, then I get BTHolt back,
>> Brian Holt: Pretty convenient, right,
>> Brian Holt: Something critical about this, which will make sense here in just a second. This is actually still a JSON object, it's just a JSON object of a string, and that's essentially the same thing.

[00:04:26]
But if you actually want the value out of it, cuz this could be a number, right, and SQLite would still give it to you at back as a string as a JSON object, right. A JSON value, essentially, if you want the actual real item out of it, you put another angle bracket here.

[00:04:42]
Mine will combine it into this fun looking double arrow, and if I do that again, you'll notice that there's no double quotes there, that's kind of how you can tell that it's not actually a JSON object, it's really the value itself. So I don't know, let's just fathom for a second that your username was the actual number 15, as opposed to that's the double arrow, single arrow, it actually does give it back to as that.

[00:05:10]
So, that's the important distinction between the single arrow and the double arrow is the single arrow you can keep doing more JSON Accessors in it, right. So if it's a deeply nested object, you can keep pulling stuff out of it, and then the last one, the leaf, the terminal accessor, you wanna have the double arrow to actually get the value out of it.

[00:05:31]
I think this is less important in SQLite, this is extremely important in Postgres, cuz it's like a bunch of stuff doesn't work in Postgres the way that you expect it to if you don't. But in SQLite because everything's string based, I don't think it actually matters too too much.

[00:05:49]
This is all real SQL grammar that's built into the spec, so I think they have to support both ways of doing it.
>> Brian Holt: Yeah just to give you some more examples of that, and again, I don't think anyone wants to watch me write this all out, I'm just gonna grab one like this.

[00:06:12]
So this is a JSON username, and this has a name field that is a deeply nested object, that's another one that has first in it. If I wanted to get just the whole object out of there, I can do that in the notes, this will give me back the whole object of the name object.

[00:06:30]
And if I wanted to get out of it, my actual name first like that, then I would use the double arrow and I would get back Brian. Yeah, just to I'm now curious what happens if we ask this JSON, type on the single error, single arrow, it might say, I'm gonna guess it's gonna say,
>> Brian Holt: I don't know.

[00:06:57]
>> Brian Holt: Yeah.
>> Brian Holt: I segfaulted, that's interesting, I have not segfaulted SQLite before. Wild, let's load that.
>> Brian Holt: Okay, because I needed to get this all inside,
>> Brian Holt: It does give you back, text, interesting, okay, well, do what you want YOLO, [LAUGH] I guess both of them are gonna work.

[00:07:38]
It sounds like with escalate for the most part, you might be able to ignore it, this is untested advice, this is just based on my observation here. I will tell you that just out of habit from doing this with PostgreSQL, I will always make the last one the double arrow just to get the actual value out, that's what it's supposed to work like.

[00:07:58]
>> Brian Holt: Interesting, okay, so I might retract what I just said because this actually worked because it's still a JSON object, this didn't because it's like this isn't valid JSON anymore. It found that to be valid JSON, it found this to be just a normal value and therefore not valid, cuz you'll get the double quotes, it's back I think that's the difference here.

[00:08:23]
I'm learning, learning is fun, so I think if you do this like within a node app, you'll get your string back within double quotes, which is probably not what you want. You want it without the double quotes, which is why the double arrow becomes useful. Okay, I talked myself out of it, don't do this, do this.