(keyboard clacking) As promised, we're finally gonna look at some JSON functions. We're gonna look first at JSON, JSON-B, and JSON Valid. So getting started, this is what we have been doing. Select json, and you can pass through basically anything that is valid JSON or JSON5. So you could do a one and it's gonna come back as valid JSON. Remember, this JSON function returns JSON as text, which is great if you need to consume it in your application or send it off to somebody else to be consumed. Now, if we do the same thing, this also accepts this JSON5 format, but it returns a binary representation that is opaque to you. I wouldn't inspect it, I wouldn't do anything with it. The only thing I would do with the result of a JSON-B function is feed that into another SQLite JSON function or write it to disk. And that way when it is pulled back out, the parsing doesn't have to happen again. To prove that this is happening, this is being converted from JSON5 to JSON as well, if we do that again, we get the same binary representation regardless of if we quoted that key or not. So just like the JSON function, it accepts JSON5, but it converts it to proper RFC, yada, yada, yada, JSON, instead of just leaving it unquoted 'cause these binary representations are the same. Now, if we were to pass, let's come back up to just JSON. If we were to pass something that is in fact invalid, so we'll do b without a key. So we were doing JSON5, which is like borderline invalid, but this, this is actually invalid. You'll see we get this runtime error. SQLite kept its promise and it did not crash but we do get this error that says malformed JSON. Depends on the use case, right? Maybe this is fine, maybe this doesn't matter. However, if you want to confirm before you try to actually use it, if you want to confirm that JSON is valid, according to SQLite, there is a json_valid function, which can be very, very helpful. So if you do json_valid, and then let's start by passing in a quoted string, a one, and forget to close it. And if we do that, you'll see yeah, cool, totally valid. Now, let's get that again and we come in here, and we'll drop the quotes. It's bizarre, right? I've been telling you all along that JSON5 is cool and valuable and works, but it doesn't come back as valid JSON. You can pass a second parameter to this json_valid function and that will determine what you mean by valid. So it's not just a matter of one, two, three; there are several bits that you can set. There are several flags that you can set. And here's a list of those bits. If you set the one bit to on, it is strictly real, legit JSON, RFC 8259 JSON, no JSON extensions. It's just vanilla JSON. If the two bit is on the input can be text, that is JSON or JSON5. So the first bit is pure JSON. The second bit is JSON5. If you turn on the third bit, which is the number four, if you turn that on, then that means that you're verifying or you're validating that the input is a blob and appears to be JSON-B. It appears to be JSON-B. The bit in the fourth position, which is the number eight, means that the input is a blob that strictly conforms to JSON-B. It doesn't just appear to be JSON-B, it strictly conforms to JSON-B. And if you don't pass anything through, then it defaults to one, which is why we got that error or rather that zero value in the first place because this is text, but it is JSON5 text. You can combine these bits; just like every bit mask, you can combine them. So this is text, including JSON5. And if we come up here and we do number four, that is a JSON blob that appears to be a JSON blob. And what that means is it doesn't do deep validation of the JSON blob. So it looks at the outer object and says, "Yeah, that looks like JSON, that looks like JSON to me." So a way that we can prove this is we can say jsonb and we'll first convert this text into JSON-B, and then we'll validate that it's valid JSON-B. And we see that totally works. Now, what you probably want, let's take a look at all of these, what you probably want is you definitely want flag number two to be on, so you want bit in the second position bit. You want that flipped on 'cause that will validate strict JSON and JSON5, which are both text. You probably also want the four bit on because that will validate JSON-B blobs. And you can combine those by adding two and four and saying six. So this still validates because it's totally valid JSON-B, but if we were to drop this out, this would validate as well because this is totally valid text, and inside that text is valid JSON5. And so you can kind of monkey around with the flags that you want. If you want to strictly validate the blob, you would add eight plus two together and you would get 10 and that would work. And then you would see that the two is still on. So that would work as well. In practice, you probably want number six. Number six covers strict JSON as text, JSON5 as text and the appearance of a correct JSON blob, which in most cases is going to be a correct JSON blob because likely you generated it using SQLite and it spit out a correct JSON blob. So that's the json_valid function. The json and jsonb functions will take almost whatever you give it in terms of valid JSON and turn it into either text that has been compressed or a blob that has been compressed and is much smaller and faster for using in future functions. But it will error out if it's invalid. So you can use the json_valid function to validate it if you don't know if it's valid JSON.