Okay, I don't want to break your heart, but we're nearing the end of our discussion about JSON. We have a few more interesting things to cover. In this one we're going to talk about updating JSON blobs. Now, historically, before databases had great support for JSON, what you would have to do is you'd have to issue a select query, bring the JSON over the wire into your application, do all of your JSON manipulation there, and then issue an update statement to put it back into the database. No more gone are the days of doing that.

We can now update the JSON blobs in place without really paying any mind to whatever the rest of the JSON blob is. We can issue an update statement and add a key, remove a key, update a key, something like that. Everything else that's happening in the blob doesn't super matter. We're just going to operate on that little bitty part, which makes for a really nice experience for us. Let's start with the config table. We're going to take a look at this table and we're going to change user number one. We're going to change their theme to light mode. I am the only user in the world that loves light mode, but boy, I do like light mode.

We're going to make some space and we're going to come up here and say update config, set, settings equal to something where user ID equals one. Now what is the something, the something is not a big old JSON blob, the something is going to be JSONB set based on the blob itself. We're going to say, "Hey, let's set a an individual key in this blob." I don't care what else is in there. Let's set this key. Now we are going to use that extraction path syntax where you open the brackets like this. In this case we just have a top level. You could of course go down to other levels like this, but that doesn't make any sense right here. We do have to pass through a valid JSON string. We'll look at this in a second 'cause I don't super love that. If we run that and we read it back, our theme has been set to light mode.

Now, the part that I don't super love is this part right here where I as the user or the developer or the application layer have to know, well, I got to pass in a jsonified or JSON stringfied version of this. That feels like it's not my responsibility. We have a couple of options here and let's hop down here and do select JSON. I believe JSON scaler is one. If we do light like that, we do get back, we do get back that type right there. If we did PG type of, it looks like, ah, that's JSON, not JSONB. If we did JSONB, there is no JSONB. Okay, that's fine. What else can we do?

We can try, if we were to run this, we can try. Let's drop the PG type of, we can do two JSONB. If we run that shoot, we have to come over here and we have to explicitly cast that to text and then we get that. What we want is in fact this, we want this type to be JSONB. That's good. Again, I don't love that I have to put in this explicit type conversion here because if I'm coming from the application side, you might imagine that the query over there is going to look something like this. Then I'm going to have to bind in. Sometimes it's text, sometimes it's integers. I don't super love that from the application side. What we can do to kind of square this circle is we can do maybe a little bit of best of both worlds. If we go back to, what was it? It was JSON, JSON scaler, and we typed in light and we decided, okay, that gives us a quoted string. JSONB scaler doesn't exist. What we can do is just up cast this to JSONB.

So now we get the best of all worlds, not just both worlds, all worlds, the PG type of is JSONB. I can just simply pass this in as a bind parameter from my application side or my ORM, whatever. Then I don't have to say, "Well, sometimes it's text, sometimes it's int." So woo, boy, we sure are going deep on JSON. Here we go. We're going to put this up here and we're going to say that this is what our query should look like and we'll change this to synth wave just to see if it gets updated and it does get updated. Again, the reason I like this one the most is because I get to just pass in whatever the parameter is. In this case it's a string and I let Postgres figure out how to turn that into what it's supposed to be. The first operation is, let's make it into some JSON data and we explicitly cast it into JSONB because this is a JSONB set function and it requires JSONB. Kind of cool, kind of like it.

Let's keep going. Now hysterically, the way that you delete a key is you set it to the object less the key you're trying to get rid of. I mean, it makes enough sense. You don't typically see less a key based on JSON, but hey, I kinda like it. If you run that and you read it back, you'll see that theme is now gone. If you come back here and run it again, theme has been added again. This set will update or it will insert. However, if we switch over to select * from orders, JSON, you'll see we do have some data in here and we have customer as a nested object. The customer has a name and an email.

If we were to, let's make some space and we were to say update orders JSON, set, what is this called? Details equal to JSONB set. We're going to set in the details document. We're going to set, we are going to set our open path here. We're going to do a nested set. We're going to do customer, and then inside of that, we're going to set age and we'll say to JSON 30. Then cast that as JSOB. We'll do where order ID, we'll just do the first one equals one. Now we should be able to look down here and see for order ID number one. We do see age of 30. We now have set a nested key here, but importantly, it doesn't work if the entire path doesn't exist, it will set that last key. If you're missing a bunch of keys in the middle, that's not going to work. If we did try to do that and instead of age, we did foo, bar, baz, age, and we ran that, and then we read it back from orders, JSON, we read it back and we pull it out and we'll come up here. Now it is exactly the same. It didn't set that intermediate chain of missing keys, but it will add a key into an object that already exists.

Interestingly, and for your edification, I think it would be interesting to see how do we remove this nested age key here? Well, based on what we've learned so far, we can go ahead and just do that. What we're going to do is we're going to set the customer object here and we're going to say details, we'll say details customer. Remember, we're not going to use the ex, unquoting extraction operator 'cause in fact, we do want JSON to come out the other side 'cause we're going to continue to operate on it. Then we're just going to say less the age key.

Follow along with me updating orders, JSON, setting the details to set the details this path. We're setting the customer object inside of the details and we're just setting it to the customer object from details less the age key. Hopefully, my goodness, hopefully that worked. We'll pop out here, come down here, add a little guy, and the age is gone. We pulled it off. The last thing I want to show you is a few array operations, and then we will be done and we'll move on to indexing JSON. In this, in this schema here, we'll just, we can get rid of all of that.

The thing that is important right now is this items array. We can, we can do it this way. We can say items and then we can do comma zero. The first item and then price. We'll come down here and get rid of all of that. We can say to JSON, what should the price be? It's 999 right now. Let's drop it to 899 and then we'll cast this up to JSONB. Now you'll see that this item, item number zero, the zeroth item has been set to 899. We can of course run JSONB insert. This is a little bit interesting in my opinion. We can have, let's see what this, oh, we have one right here. Let's take that guy. We're going to come up here and we'll just straight cast this one. We'll say the price is 99 and the product is a, what's a $99 product? I can't think of a single $99 product. We'll just go with headphones. Goodness gracious. Single $99 product headphones. We're going to up cast that to JSONB. Now what, this is a little bit ambiguous, right? This is a little bit ambiguous because zero could mean before or after the zero element as it exists.

You do have a third argument here. Let's start by, let's start by turning it false. You have this argument down here and we've said insert it at items position zero false. If we run that and then we read it back, we see that, we see that that item got put into the very first position, which is array position zero. Now, if we were to change this to true, and we were to bump this up to 199 and change this to, in all of my creativity, better headphones, a $100 more. We changed this to true, but we left that index set to zero. If we run that and read it back, order ID, yep, there we go. It is no longer it inserting at the zeroth position. It is inserting after the index that we declared right here.

What we have learned, what we have intuited, what we have discovered by experimentation, is that this final parameter controls before or after, so you can set it to false to insert it before the index, or you can set it too true to insert it after the index. That gives you full positional power over the array. You can insert stuff wherever you want. There are a lot more functions and they kind of, they kind of get off into the weeds. I think JSONB set is probably the one that you're going to be using most likely. Again, I will leave a link to the docs down below so you can see the rest of the JSON manipulation functions.