The first JSON functions that we're going to look at are JSON validation. We're going to take some strings and use Postgres to validate whether or not that's legitimate JSON. We have a couple of different options that we can use there, which we'll look at here in a second. After that, we're going to look at JSON creation, then extraction, and then updating in place, and then indexing. We're going to try to build up from the beginning, starting with, is it valid JSON? We can do that here. I have SELECT val, and then I just have a bunch of hard-coded JSON or JSON-like strings, and I just gave it a table alias of test, and then named that fake column val, such that if we run this, we get back val and all of our values here.

Now, we can do a couple of different things. We're going to start very simple and say val is json. If we run that, we see that several of them are and if few of them are not. If we look at the few that are not, we see that this is not valid JSON. It looks like JSON, but it is not valid JSON. This first one is an unquoted string. You see the guy above it is a quoted string that came back as true. Then the one below it is an unquoted string that came back as false because that is not legitimate JSON. Same goes with this guy down here. You'll see that the key a is unquoted, whereas the one above it has proper quotes around the key a in that object. This comes back as not valid JSON. You could use this, you could use this to potentially help you move from a old, potentially, column that holds JSON as text. You want to see if all of the values in that column are valid JSON before you start trying to run these JSONB functions over it or move it to a proper JSON or JSONB column. This is one way that you can test.

There are several other things that we can test here. We can add a few modifiers. We can say val is json, and we can say, is it a scalar value? We need to remove that trailing comma. Is it a scalar value? Let's give that a name as scalar, and then let's give this guy a name as json. There we go. If we look here, a integer is scalar, a string is scalar. Once you start getting down into objects and arrays, we see that they are not scalar. What they are is potentially an array. We can say array as well. We see that this guy turned true there at the end. Finally, we also have access to json object as object. If we run that, you'll see that our objects in this last column here did get marked true as being JSON objects. You just test, is it JSON at all or is it a particular type of JSON?

If you are in a situation where you have been storing raw JSON somewhere and you need to validate, am I going to lose any information? You can come in here and say object with unique keys as object unique. If you run that, you'll see we forgot the comma again. Down here, this very last one, you'll see it has two keys that are both of value a. A is b, and a is also 2. What a great JSON document. You'll see over here it's saying, "No, those keys aren't unique." Should you convert that to proper JSON, you're going to lose one of those keys. I think you lose the first one, but I don't really know. At least now you know these do not have unique keys. Whereas this guy up here is saying, yes, it is an object, and yes, it does have unique keys.

I think, in most cases, you are likely going to be providing the JSON from the application, and therefore, kind of knowing that it's already valid. Where this can be useful is if your database has grown over time, and it hasn't always been, let's say, thoughtfully constructed, and you're trying to move some JSON out of a text column into a JSON column, you can do this as an intermediate step to confirm that all of that JSON is going to move over correctly, you're not going to run into any errors, and you're not going to lose any data. This is how you would validate JSON. Let's look at how you would construct JSON using Postgres.