PostgreSQL V14 recently released some major improvements. Some of these improvements include enhanced performance for overloaded work, improved support for distributed workloads, and security improvements.

One of the most exciting releases is the enhanced JSON support, which will be the focus of this post.

Before we dive into these improvements, let’s take a closer look at the use of JSON in PostgreSQL. This will help us better understand these improvements.

A brief history of JSON in PostgreSQL

JSON was first introduced in the V9.2 release of Postgres. While this is a very exciting development, its implementation is not perfect. Postgres basically validates that the JSON to be stored is valid JSON and stores it as a text string.

One major improvement is the JSONB type, which was released in version 9.4. This is often referred to as “better JSON” for a simple reason. JSONB is stored in a shred binary format, which adds a bit of conversion overhead when stored, but is very efficient when manipulating and querying JSON. JSONB also supports indexing of data. Most people prefer to use JSONB instead of JSON in Postgres.

In Postgres V12, JSONPath was added to improve the efficiency of querying JSON data.

Which brings us to the present. Now let’s consider v14’s improvements to JSON.

JSON convenience of V14

Postgres V14 allows you to access and manipulate JSON data in a traditional way. Let’s illustrate this with some examples.

Suppose we have a blog post table with data columns stored in JSON format. In this article, we will use the JSONB type in all of our examples.

CREATE TABLE blogs (
  id serial,
  data JSONB
)

Copy the code

We also insert some test values into it.

INSERT INTO 
  blogs (data)
VALUES 
 ('{"title": "blog one", "author": {"first_name": "Ada", "last_name": "Love"}}' ),
 ('{"title": "blog two", "author": {"first_name": "Star", "last_name": "Work"}}' );

Copy the code

This will result in the following table.

SELECT *
 FROM blogs;

 id |                                     data                                     
----+------------------------------------------------------------------------------
  1 | {"title": "blog one", "author": {"last_name": "Love", "first_name": "Ada"}}
  2 | {"title": "blog two", "author": {"last_name": "Work", "first_name": "Star"}}
(2 rows)

Copy the code

Let’s take a look at the v14 improvements.

Access JSON data using subscripts

In Postgres 13 and earlier versions, if you wanted to find the titles of all blogs whose authors’ names were “Ada, “you would do the following.

SELECT data -> 'title' as title
FROM blogs 
WHERE data -> 'author' ->> 'first_name' = 'Ada' ;


   title    
------------
 "blog one"
(1 row)

Copy the code

Notice the operators we use to get this data.

  • ->Is the key used to get a zero-indexed JSON array element or JSON object field.
  • ->>To get the text of a JSON array element or JSON object field.

While this is effective, it’s not the easiest syntax to remember. This is because this syntax is different from the traditional way of accessing JSON data. What if we could access JSON data stored in Postgres using subscripts as we’re used to? That’s what Postgres V14 brings us.

Let’s try to retrieve the data from above, but this time in Postgres V14 style, using sublabels.

SELECT data['title'] as title
FROM blogs 
WHERE data['author']['first_name'] = '"Ada"';

   title    
------------
 "blog one"
(1 row)

Copy the code

Note that you must use a JSON string when using subscripts for comparisons.

Update JSON with subscripts

It’s also easy to update JSON data stored in Postgres using subscripts. In V13 and earlier versions, to update JSON, we need to use the jSONb_set function with the following signature.

 jsonb_set (target jsonb, path text[], new_value jsonb [, create_if_missing boolean ])

Copy the code

In this code. -target is the JSONB column to update. -path indicates the JSON key you want to update. -new_value is the new value of the item to update

Now, let’s use this function to update the data column in the above example. For example, if we want to update the blogger’s last name with ID 1, we do this.

UPDATE blogs
SET data = jsonb_set(data, '{author, last_name}', '"Sarah"', false)
WHERE id = 1;

Copy the code

This will lead to.

SELECT * FROM blogs;

 id |                                     data                                     
----+------------------------------------------------------------------------------
  2 | {"title": "blog two", "author": {"last_name": "Work", "first_name": "Star"}}
  1 | {"title": "blog one", "author": {"last_name": "Sarah", "first_name": "Ada"}}
(2 rows)

Copy the code

In Postgres V14, we do not need to use the jSONb_set function to update JSONB data. We can do that.

UPDATE blogs                                                                       SET data['author']['first_name'] = '"Sarah"'                                       WHERE id = 2; 
//id is different in this case it updates a different row

Copy the code

This will lead to.

select * from blogs;                                                                
id  |                                  data                                      
----+---------------------------------------------------------------------------------
  1 | {"title": "blog one", "author": {"last_name": "Sarah", "first_name": "Ada"}}
  2 | {"title": "blog two", "author": {"last_name": "Work", "first_name": "Sarah"}}
(2 rows)

Copy the code

The second line is updated.

Important things to note when updating JSON with subscripts

Handling some edge cases using JSON assignment via subscript is different from jSONb_set. Let’s consider some of them.

  • If the value of the updated JSON key isnullAssignment by subscript will be as if the key value were an empty object or array.

So in our example above, if we try to update a row with a tag that doesn’t exist in any of the rows below.

UPDATE blogs SET
data['tags'] =' ["postgresql"] '
WHERE id = 1;

Copy the code

We’re going to get something like this.

SELECT * FROM blogs WHERE id = 1; id | data ----+----------------------------------------------------------------------------------------------------- 1 |  {"tags": ["postgresql"], "title": "blog one", "author": {"lastCopy the code

Tags are always added to the line. There is no option to prevent it from adding a column that does not exist, such as the jSONb_set create_optional parameter.

  • If you specify an index for an array that contains too few elements,null, until the index is reached.

So, if we try to update the tags field that we added in the previous example, its index exceeds the current length of the array, like this.

UPDATE blogs                                     SET
data['tags'][4] =' "javascript" '              WHERE
id = 1;
Copy the code

We’re going to get something like this.

SELECT * FROM blogs WHERE id = 1; id | data ----+------------------------------------------------------------------------------------------------------------------- ------------------ 1 | {"tags": ["postgresql", null, null, null, "javascript"], "title": "blog one", "author": {"last_name": "Love", "first_name": "Ada"}} (1 row)Copy the code

Note, null, until the specified index is reached.

  • If a JSON value is assigned to a nonexistent subscript path, and the last existing element to be traversed is an object or array, nested arrays or objects will be created. However, as in the example above,nullThe created object or array is not placed until the specified index is reached

So, in our example, if we did the following.

UPDATE blogs
SET data['otherdata'][3]['address'] =' "New York" '                              
WHERE id = 2;

Copy the code

We get the following result.

id | data ----+------------------------------------------------------------------------------------------------------------------- ------------------- 2 | {"title": "blog two", "author": {"last_name": "Work", "first_name": "Star"}, "otherdata": [null, null, null, {"address": "New York"}]} (1 row)Copy the code

As you can see, the object is created. Null, however, until the index is reached.

conclusion

It’s really exciting to see how JSON support has improved in Postgres over the years. In this article, we saw how the addition of JSON subscripts to Postgres V14 makes it easy to access and update JSON. We also considered some considerations for assigning JSON values when using subscripts.

Does this mean that subscripts completely replace the old way of accessing JSON data? Not necessarily. For example, we saw that when updating the JSON value, there is no way to prevent the column from being created if it does not exist. The jsonb_set function gives us this choice. Also, indexes are not always used when accessing your JSON data using subscripts. However, for uncomplex queries, subscripts can be used.

Who knows what improvements Postgres’s new version will bring? Better use of indexes when using subscripts? Accessing JSON via dot notation? Only the future will know.

See the official documentation on JSON subscripts for more information.

You can also check out other Postgres V14 releases here.

  • PostgreSQL 14 has been released!

New JSON features in PostgreSQL V14 appear on the LogRocket blog.