(icon taps) Before we dive into all of the JSON functions, and yes, I promise we're going to get there, we just have one more philosophical thing to talk about and that is value arguments versus path arguments. So as a little tease, here are the JSON functions. You'll see we've got scalar up here, and then aggregate and table value. If you look at these scalar functions, you'll see these are the function signatures and you see a lot of the same stuff. Path value, path value, value one, value one, path, value one, two, value, one, two, path versus value. So path arguments must begin with a dollar sign. They are strings that must begin with a dollar sign. Let's flip over here and take a look at the thing that we looked at in a previous video, which is JSON extract. And then the first argument is the JSON. And this is our path argument. You'll see that the path argument starts with the dollar sign and then is followed by a dot and then a path. So if we were to abstract that a little bit, we would say it starts with a dollar sign and then it must have either an object path like this, or if it is an array, you can open square braces and do array syntax. You can do zero indexed addressing and address it directly. Or you can use this hash dollar sign, or I'm sorry, pound sign format and address it from the right side of the array. So for a zero indexed array, this pound sign, this hashtag means the length of the array. So this right here is referencing an item that doesn't exist because it's referencing the length of the array and at a zero index, if you have five elements in there, this would be five, but there is no item at position five. So if you need the last item in an array, that is how you would get there using the JSON path argument. Did you follow all that about the zero indexing last element thing? Maybe not. Not my clearest explanation. So here is an example. JSON extract, A, B, C. You have three items in this array and we are extracting from the entire length of the array back one, which will give us the last element in the array. So if we were to do this, you'll see there is nothing there because at position three there is nothing. 'Cause this is position zero, position one, position two, and then finally there's nothing at position three, nothing there. And then you can address it directly like that. But this syntax right here is a great way to insert something at the very end of the array. So we'll look at some JSON set functions. And this is a nice little shortcut to say, just put this guy at the end. So that's the path argument. It is a string always starting with a dollar sign followed by a dot and an object path or a string always starting with a dollar sign followed by a square bracket array accessor. Now, the value argument's a little bit different. If we look back here at the functions, we'll see value 1, 2, 1, 2. Then we see JSON, JSON, JSON, JSON, path value, path value, JSON. So, we're left with two things 'cause we've already talked about path, right? But we're left with JSON and value. And these two things are treated slightly differently. So we're gonna go back over into SQLite and we're gonna look at this JSON function. And we're gonna look at this JSON object function. So the JSON function accepts JSON and the JSON object function accepts a label and a value. And we're gonna see how these things are different. So if we come back over here, and we'll get rid of all of this stuff, and if we can, and we'll do select JSON, and I'm just gonna open a string and we're gonna do an array. And just so we don't have a bunch of quotes everywhere, we're gonna do one, two, and three. And if you select that, you see you get one, two, and three. But, if we were to do something like JSON object, so if we were to do JSON object and we were to say example is the label and this is the value argument, and we were to put 1, 2, 3 in there, this is not what we wanted. We got our quoted key, which is correct, but then our value ended up being a string that looks like an array. But boys, that not an array. That is definitely a string. So before I explain what exactly is happening here, we're gonna do something different and see if we can intuit what is happening 'cause I find that that always makes the learning process a little bit more stable if we can kind muck around and figure it out ourselves. So we passed in a string and it gave us back a string, which is computers, right? It's technically right, but not what we wanted. So if we were to, let's say instead wrap this in that JSON function, which we did just above there, and then we look again. Now that's a little bit better. That's an actual array, not a string that maybe looks like an array underneath, but honestly is not gonna work. It's just a string. Here's what's happening. When a function requests JSON, so the JSON argument that we're looking at on the docs page, when it says JSON, you can pass a string in and that function will interpret it as JSON. When a function calls for a value, if you pass a string in, it gets treated as a string. Lemme say that again. When a function, when the argument says JSON, you can give it a string and SQLite says, "I asked for JSON, they gave me a string. This looks like a JSON object in a string. I'm gonna turn it into JSON." That's JSON. When it asks for a value, if you give it a string, SQLite says, "That's great. I got a string." Not exactly what you're looking for most of the time, but it is what you're looking for when you do something like this. When you pass in a string that says, hey, you get exactly what you want. So you need to be careful when you're passing in an argument into a function that expects a value, you have to be sure to turn that into real JSON before you pass it in, which is exactly what we did here. We said, "Alright, listen, I have an array of 1, 2, 3. It's actually wrapped in a string. So before I feed it to this JSON object method or this function, I'm gonna turn it into JSON myself so that it comes out as an actual native array instead of a text or instead of string." There are a few other ways to get real JSON out of a string. We passed it through that JSON function, which accepts JSON as an argument. And so it inspected it and turned it into real JSON. Let me show you two other ways to construct this JSON array out of a string. The first one is this JSON unquoting operator. And we have, I have a full video on JSON extract, which includes this, but this is a way to extract JSON out of a string. And if we were to do that, you'll see we all we did is we said, "Give me the full object back." This is a path argument. It must start with a dollar sign. And in this case we're saying, "Just gimme the whole thing, gimme the entire object." And that turned into a native array just like we wanted. And then the other way that you could do it is you could use any of the other built-in functions. Every time I type array, I wanna type Aaron instead. You could use any of these JSON functions. And so this JSON array function is going to construct a native JSON array. And so there you're good as well. The trick is, if you give it a string, it's gonna think it's a string. And so you can either say, "Actually, this is JSON, do your best, turn this into JSON." Or extract just the entire object. Or I'm actually gonna construct it out of native types instead of taking it from a string and parsing it as JSON, I'm just gonna build the array myself, which in fact is what we're doing here, building the object ourself by passing in the key and the value. Very important to remember the path arguments versus the JSON arguments versus the value arguments. You'll end up in a little bit of a sticky situation if you pass in a string and then you expect it to be a JSON object later, you're gonna be kind of hosed. So just be careful as you're doing this, think about what type of argument SQLite is expecting and make sure you're giving it the thing that you want to see when you pull it back out.