We're going to use Postgres to create some json. We used it to validate it. Now we're going to use it to create it. You might not be in this situation terribly often because again, you'll probably be getting the json from your application side and putting it into Postgres as fully formed json. There are tons of use cases for creating json within the database itself. We're going to start small and build up.

I have a couple examples here. They do require a lot of typing, so I'm just going to paste it in. The first one that we're going to look at is json_build_object. While this is arranged in sort of a clear key value pair situation, it's just a very attic array or a very attic arguments here, and I have just formatted it such that it looks nicely. This you can just keep feeding keys and values in, but if you do it like this, it sure does read a lot better. The point of this one is to take a bunch of arguments and turn it into a json object. If we run that, you'll see we get this back and let's just make some space and we can put that right there. That's not actually very much better. I could have just expanded, but that's fine. You'll see we get the ideas, the key, and then the name as the key, active as the key. Then we do get a full array down here because we did an array here.

We will run that again and we can do pg_typeof and surround that. We'll come up here and you'll see that it comes back as json. There is a corresponding jsonb_build_object, and it comes back as jsonb. Now the question here is, what are you going to do with it? What are you going to do with the result of this function? If you're going to write it into a jsonb column, you might use the jsonb operator. If you're going to return it back over the wire to the client, well, you just leave it as json text and return it back. There's no point in using the jsonb format there. It kind of depends on what you're going to do. If you're going to continue operating on this json object, leave it as jsonb, leave it as its parsed representation, and then continue to chain operations off of that rather than jumping back and forth between the parsed representation and the textual representation. In, for many of these, there are going to be, there are going to be json and jsonb variants. I'm not going to cover all of the different variants, but just remember that sometimes, if you need a jsonb variant, it exists. If you need a json variant, it exists. I'll leave a link to the docs down below.

Let's keep building some json. In the same way that we have json_build_object. You guessed it. We've got json_build_array. This is a wonky looking array 'cause it was originally set up to be an object. You'd probably have something more reasonable like that. There you go. You can build an array in Postgres. None of these terribly move me. There's one more that can be useful and that is to_json or to_jsonb. We'll stick with to_json. You can just pass in any value here. We'll say ('aason'::text). It comes back as a quoted json string and we can prove that it is json by doing typeof, there you go. We do get json there. It's not just strings. You could do, you know, you could do, I forgot a double colon. You could do an integer there. It's not just scalers. You could have an array of 1, 2, 3, 4, if you can type. You get your array back right there. Now these are useful, I will admit these are useful, but we need to move on to something that is probably more, probably more practical or probably more useful in your day-to-day potentially application building.

Let's look at a few of those. Now, one that does move me that I do think is quite practical is row_to_json. Now impractically, you could just put a row constructor in here, but we don't want to do that. We're want to do something more real. We're going to do row_to_json(u) from, let's do as users_json, you see where this is going, from, and then we're going to open this up and we'll say select * from users where, let's just do, where email = 'aaron.francis@example.com' as, and we're going to alias this to u. So if we run that, boom, there is the entire row of my record as json. You can imagine that the requirements are, I just need id and email. Now you can get back json blobs for each individual user or each individual, in this case, row that matches that query. Now we're not quite done yet because let's say that we expand this and we say where is_pro is pro is true. Now we get back a table where each individual row is json. That is what we asked it to do. This is not Postgres fault, but this is not terribly useful in this scenario, right?

You can imagine if you're trying to get back a bunch of json objects, you probably want that as a json blob and a json array. You can do that in Postgres without having to pull it all back across the wire and then loop over it and add it to a json array. We can do that with SQL. Let me show you how. We've got our row_to_json working pretty well here. You see we get those 44,000 rows back with a bunch of json blobs. If you want a single row, you can say json_agg and then we're going to throw parentheses around that and that aggregates, that's say json aggregation function, of which there are many we'll look at a few more later. This is a json aggregation function. We've gone from rows and columns, we've gone down just to a single row, single column that contains the value. This is a massive json blob that holds 44,000 ish records. This can be terribly useful if, on the other side, you're just going potentially throw it back out over some API, right? You can do your aggregations or json manipulation, you can do that here and then just throw it out over the wire. You do have more control.

Let's say you want to bring it up a level. You can say json_build_object again. Instead of just relying on your subquery down here, you can say json_build_object. You can say, "Eh, let's just start with 'id' , id. If you run that, you'll see, wow, okay, I can kind of start to get a sense of how this might be useful. Up here we can say 'email' , email and so we can build out our object up here instead of relying on this subquery down here. Now you'll see we're just selecting, we're just selecting from the entire table where is_pro is true. Then we're doing our little bit of json manipulation up here using the json_build_object. We can get rid of this and say from users u where is_pro is true and we should get the same thing back.

We've eliminated that temporary table and that subquery, which is, I don't know that super matters, but I can't imagine a very, very common situation where you're using some sort of ORM on the other side to build up a query. You don't necessarily want to do all the mucking about with the json manipulation down in the query. You want to do that up in the, or down in the filter. You want to do that up in the select. You do have a few options.

You can use your json_build_object to get it exactly how you want it, or you could do the subquery and just do the row_to_json function and that will take whatever is in the row and turn it into a json object, which can be terribly convenient. Now at least you know how to build some json objects out of your existing data, which I think is going to be very useful and very practical versus building them up from scratch, which can be useful. I think just in far fewer scenarios.