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.
Let's dive in.
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.
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.
Additionally, there is new functionality for working with
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.
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.
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.
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.