5mins of Postgres E13: New SQL/JSON and JSON_TABLE features in Postgres 15
In today's video, we take a look at the SQL/JSON features in Postgres 15. Postgres 15 will most likely be released in September or October, so it's still quite a way until we can actually use this on a production server. However, a couple of days ago, the Postgres 15 feature freeze occurred, which means we now have a good sense for which functionality will make it into this next Postgres release.
Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel.
Transcript
Let's dive in.
SQL/JSON constructor syntax in Postgres 15
One of the features is the new SQL/JSON functionality. Depesz wrote about this on his blog.
First of all, let’s have a look at the new SQL/JSON constructor syntax. One of the new features here is that you can use the WITH UNIQUE KEY syntax to ensure that a JSON document does not have any duplicate keys. For example, you have a key "a" and a key "b" and it's going to process it successfully. Now, if you pass a key "a" and another key "a", then it will error out saying "you have duplicate key values". Depesz wrote more in detail about it here.
Generally, Postgres has been more permissive in this regard. For example, if you have JSONB data, JSONB internally would only store one of the two keys. It's very good to validate your data here early so that you don't store data incorrectly.
IS JSON predicate in Postgres 15
Closely related to constructor syntax is the new IS JSON predicate to check if a certain value is a valid JSON value. We can say of the data that we stored in our table: does it have unique keys or does it not have unique keys? This might be useful if you have existing JSON datasets where you're trying to understand: do they conform to these requirements or not? You can learn more about it here.
Working with jsonpath expressions in Postgres 15
Additionally, there is new functionality for working with jsonpath expressions. jsonpath expressions already exist in Postgres and important to note is that jsonpath only works on JSONB data types, not JSON – that's because Postgres treats a JSON data type as text, whereas JSONB has a logical structure to it.
Check constraints with jsonpath
Now, we have a document with a key "a" and so I can check for the existence of a particular key in this document. If I check whether there is a key "b" that will return false. Where it is really useful is for check constraints. I can now conveniently use this JSON_EXISTS function on, for example, this “addresses” JSONB document, and so I can validate that there is a "main" key present, and if it wasn't present, then you couldn't insert the data into this table. It would actually reject the data because of that check constraint. You can see more about this here.
Postgres treats a JSON data type as text, whereas JSONB has a logical structure to it.
Using JSON_TABLE in Postgres 15
Now, my favorite new JSON feature in Postgres 15: JSON_TABLE lets you work with a JSON structure in Postgres as if it was a regular Postgres table. You can see here, we have our "a" and our "b" keys and it's essentially an array of hashes, very typical JSONB data. Now, we have this jsonpath expression and this tells us how to interpret this document as a table.
In addition to that you have this new COLUMNS keyword, and the COLUMNS keyword indicates that I'm creating an in-memory Postgres table and this table has a "column_a" that maps to the jsonpath key "a", and then "column_b" that maps to the jsonpath column "b". In our resultset we're using this like a set returning function, so we say "SELECT * FROM" and this JSON_TABLE expression and then the result looks as if we queried a regular Postgres table. That's of course very useful, because now you can do all kinds of other transformations, aggregations and such on this JSON data as if it was a regular Postgres table that you worked with. You can learn more about this here.
There is some additional convenience functions, for example I can use this FOR ORDINALITY syntax to get one value for each row in the document, useful if you want to have something you can identify each row with. This syntax automatically detects when a column is named the same way, so I'm declaring a column here with an int4 data type called "a" and we already have a JSON key called "a". That automatically matches, you don't need to do any extra work and write a lot of jsonpath expressions in those cases.
Using NESTED PATH with JSON_TABLE
Last but not least, there is a really neat feature here, which lets you work with nested data as well. For example, we have an array of hashes, these are blog posts and each of the blog posts also has comments.
In a nested structure like this, if you want to work with it, there is a new NESTED PATH keyword for JSON_TABLE which lets you break down further as if you're doing a JOIN on two tables. The top level data gets repeated and then the individual comments here are unique.
Again, this just makes it very convenient to work with complex documents. If you want to learn more about this, you can also see the JSON_TABLE documentation in the development snapshot of the Postgres documentation.
As you can see, I'm very excited about Postgres 15. Thanks so much for joining us for this episode of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on Twitter to get updates about new episodes!