Now that we've built JSON objects, extracted keys out of JSON objects, we're gonna look at updating JSON objects. So, there are five functions we're gonna cover here, each of them does something slightly different. But, we're gonna start with JSON A of one and this is going to be our object. The first thing that we're gonna look at is, select JSON insert. And we're always going to pass A of one. So you always pass the JSON first and then you have a path. So, we're gonna say, let's put it in at key B. So we're gonna start at the object and then say key B is going to be value two. Great. So we have inserted a new key. Now, insert, if we were to change this to A, insert does not overwrite. It will set it and it will create it if it doesn't exist but it does not overwrite.

However, we can say, JSON replace and that will set something that is already there but it will not set something that is not there. So, JSON insert will insert something new but it will not overwrite. JSON replace will overwrite but it will not insert something new. And if you want to just brute force set it, regardless of if it is there or not, you can say, JSON Set and that will set it and it will overwrite it. So JSON Set is kind of a combination of both, in that it will overwrite a value, it will insert a value. JSON replace does not insert. And JSON insert does not overwrite. You also have, if we go back out here and we were to say JSON remove, you can remove a path out of an object. And so that's a way that you can just, yoink, pull it right out. And you can do that, if we were to say, A is one and B is one, two, three. So B is an array and we're gonna remove B number one. And you'll see that has extracted, well it's deleted, in fact, it's deleted the item at index one of key B, which is this guy here. So, those are four of the functions. Insert and replace, do slightly different things. Set covers both of those, it just, kind of, brute force, sets it. And then you have remove. Now, the fifth option. The fifth option is something you'll probably be familiar with from various programming languages. You have JSON Patch. Now, JSON Patch takes two or more JSON objects and applies them left to right. So if you have A one and B two and you get out of here, now you have A one and B two. If you had A one and A two, it does overwrite.

And so, this is a way that you can combine two objects with the right hand taking precedent over the left hand. So this could be a good way to do defaults or deep object merging. However, something you do need to know, is that, if you have arrays here, so let's say we have A of one and A of two, three, it treats the array as atomic. So it doesn't merge, it doesn't combine those two arrays. It just says, alright, key A is an array and it's in both and so last one wins. And so that's a complete full overwrite. And so if you need a little bit more control, you can use something like, JSON Set. So let's do this. Let's do JSON Set and then we'll take this same object here. We'll say, the object has a top level key of A and that key contains an array. And then we can use, remember this path, we'll use path.a. And then we can use this wild, wacky, syntax right there, to say, alright, the portion of this array that I want to insert something, is actually the end. I want to append. This hashtag means, start at the right. That means the last element. And this means append a new element. So you can append an element like that. And remember, you can work your way from the right, working back. And so we'll say, actually, let's change the last element in the array, let's change that to nine. And that will work. So a bunch of functions you can use to modify your JSON. Each of these functions has a JSONB corollary that you can use if you're planning on writing the data back or feeding it to another JSON function. If you need it for human consumption or even machine consumption outside of SQLite, use one of the JSON versions, one of the plain JSON versions that doesn't have the B appended to it. And that will give you a text representation back, instead of SQLites secret binary representation.

So, insert will add an element, it will not overwrite. Replace will update an element if it exists but it will not add. Set will insert or replace. Remove yoinks it out, takes it away altogether. And then Patch works its way from left to right and the right side wins, overriding anything on the left. Keeping in mind that it treats arrays as atomic. And so if you want to add or change an array element, you're better off using JSON Set. So, those are all the ways you can do it. And remember, we're operating on hand coded JSON here. In reality, you're probably gonna be operating on a JSON column. So instead of passing in a string of made up JSON, you're just gonna put the column name and do your operations there. These are really great. This is a really great example of where storing JSONB would be really appropriate. So if you have a column that is stored as JSONB, then you can operate on that column, without having to juggle back and forth between text and JSON binary and then back to text. Store it as JSONB, use the JSONB functions here and then put it back in the database as JSONB. And not only are you gonna save space from having that JSONB compacted, it's gonna be so much faster 'cause you're not going back and forth between formats. You are storing the binary representation on disc and that's gonna be a lot faster.