I want to show you some very practical use cases of where you might take a JSON blob and turn it into a proper table. Explode it into rows and columns, and make it an actual table against which you can query or turn it into a CTE or anything like that. All we've been doing is extracting little parts of a blob or checking for existence, that sort of thing. When you're storing configuration or settings or feature flags or anything like that, you might be storing it all as a JSON blob, but then you need to turn it into a table to do regular SQL stuff against. You can do that. Let me show you how. We're going to start slow and build up from there. A lot of what we're going to be doing is this jsonb_each function here.

Right now, let's just hard code some stuff. We're going to put a as 1 in there, and you'll see we are selecting from the result of this jsonb_each function. This is returning a result set against which we can select or filter if we want to. If we were to carry on with this object and see b, 2, you'll see extra rows starting to pop in there. This is taking that object and turning it into two columns, key and value, and then populating the rows for every key value pair in this object. Now, one of the problems, which you're probably already anticipating at this point is this. This is probably not what you're looking for when you're doing this jsonb_each. Fortunately, we don't have to muck about. There is a each_text and that turns it all into text. This is the structure or the means by which we will be taking a JSON blob and turning it into a table. This is kind of cool, but we need to see how we actually use this in real life with an actual table.

Let's look at that now. Here is an example table select * from config. In this configuration table, we just have a JSON blob full of settings and merely two users. We have user one, user two, and these are their settings, dark mode, light mode, notifications, items per page. Now, how are we going to blow this up or expand this out into a proper table? We are going to keep config, and then we're going to do lateral. We're going to use this fancy lateral join that we covered way back when, and we're going to use jsonb_each_text, and we're going to throw settings in there. Now you'll see we actually get the key value over here on the right. We'll probably turn this down a little bit. Let's just do user_id, key, and value. Now we have a proper table against which we can work. Now this is what you might traditionally call an entity attribute value table. I don't super love these, because this is a little bit hard to work with, even still, even coming out of a JSON blob into an EAV style table, this is what WordPress uses, by the way, is a lot of EAV tables, I don't super love them. What I love to see, is I love to see proper columns. I love to see columns that are named theme, and notification, and items_per_page, and not this whole key value goofy little schema.

Good news, there are some other JSON functions we can use to turn this into an even better table. This one's going to be a little bit of a journey, but as always, we'll build up from the very beginning, jsonb_to_recordset. Now, what this function does is that, it takes an array of objects and it turns it into a proper table where you get to name the columns and their types. Let's just start here by hard coding some stuff in. We'll say that a is 1. Then for jsonb_to_recordset, you do have to declare what those columns are and what their types are. We'll say t. We're going to say as t, and we'll say, a is an integer. If we run that, you'll see we do get a down there. If we were to say b as foo, then we were to say that b is text, we're starting to actually get a table out of this blob of data. Not only a table, a table with proper column names and a table with proper column types, which I super love. We need to take this, this jsonb_to_recordset, we need to take this and plug it back into our configuration table so we can get those settings expanded out properly.

The first thing we're going to do is come in here and get rid of that. Now, we can't just, although I did, we can't just write settings, because this jsonb_to_recordset expects an array, but we can kind of work around that. Let's come down here and mimic that, select * from jsonb. We'll just take that whole thing. What we're trying to do here is we're trying to prove that we can work around that. In our world, settings is an object, and the object has keys of, it's got keys of like theme. The theme is light mode. If we were to come in here and say, theme is text, you'll see, ah, we can't really do that, can't call it on a non array, but jsonb_to_array, not to_array, jsonb_build_array will allow us to create an array out of that, and we'll cast that to jsonb. Now we're back in a good spot. All we need to do up here is take this jsonb_build_array and wrap it around the settings. Now we've turned what was a JSON object into a JSON array that has a single item inside. That single item is in fact, our settings blob.

To finish this out, we are going to do the lateral trick. I think this table is called config, and then we're going to say lateral with this new record set. We need to come in here and say, that theme is text, and I have forgotten the rest of the keys, select * from config. Config theme, notifications, and per items_per_page. We'll say that notifications is a boolean, and items_per_page is an int4, that seems fine. If we run that, look at that. Now we've got a proper table. This is what I'm talking about.

We can, in fact, ooh, yeah, let's do that. Let's trim this down a little bit further. We only want, we only want user_id from the config table. Then interestingly, we might not at this point, as we're constructing our query, we might not know all of the column names. We're just going to say, take everything from t.*. We could come down here and copy and paste them up here, but I like this better. Now we have our user IDs, their themes, their notifications, their items per page, et cetera. We could wrap this up in a CTE and do further operations on it. We could treat this as a user settings table. What we've done here is we've gone three steps, or maybe two, I don't really know.

We started with a JSON blob, which is great for storage. I'll show you how we can patch in and do some updates later. We've gone from a JSON blob, which is great for storage. If you are looking for a single specific key or containment or overlap, you have operators for that. After that, we turned it into a table, but a table with a structure that I didn't super love, that entity attribute value pattern, that puts a key value column and then lists out the attributes as rows. I don't love that. I would rather go wide with proper columns with names and data types, and the jsonb_to_recordset gets us there. I like these functions. These are fundamentally different than the functions we've looked at before, because these return record sets, instead of scalar values or other JSON values, these return rows and columns, which allows you to treat it as a proper table.