Working With JSON in Postgres 14

Note: This post is targeted at versions of Postgres 14 and greater. If you are using a version of Postgres prior to 14, check out these posts for some more great information on querying and updating JSON data in Postgres.

jsonb Subscripting

In previous versions of Postgres, we used special operators to access data in jsonb columns. These operators are -> and ->> to extract data as jsonb and text respectively. Updating jsonb data also required specific functions to be used like jsonb_set and jsonb_set_lax. These methods for working with jsonb all still work in Postgres 14, but we now have an even easier way of working with JSON data. The feature is called jsonb subscripting and it provides a more natural way of accessing the data. Let's first look at querying data with jsonb subscripting.

If you'd like to follow along with the code samples for the rest of this post, feel free to run the following SQL to create the table and insert a couple of rows.

CREATE TABLE public.artist
(
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    document jsonb NOT NULL
)

insert into artist (document)
values 
    ('{"first_name": "Leonardo", "last_name": "da Vinci", "country": "Italy", "paintings": ["The Vitruvian Man", "The Last Supper", "Mona Lisa"]}'),
    ('{"first_name": "Vincent", "last_name": "van Gogh", "country": "Netherlands", "paintings": ["Sunflowers", "The Starry Night"]}');

Querying Data

An important thing to note about accessing jsonb data with subscripting is that the result of the expression is always jsonb. This makes the subscripting syntax functionally equivalent to the -> operator.

With subscripting we are able to use an array-like syntax to access jsonb data by object key. In the case of arrays, we are also able to access that data via the zero-based index. Let's look at a couple of examples.

-- Access jsonb data via object key
select document['first_name'] as first_name, document['last_name'] as last_name
from artist
where document['country'] = '"Italy"';

-- Access jsonb array via zero-based index
select document['paintings'][0] as first_painting
from artist
where document['last_name'] = '"van Gogh"';

A couple of things to call out with this syntax.

1. The key inside of the [] subscript is surrounded by single quotes.

2. Since subscripting always returns a jsonb value, we need to format the values accordingly (ie '"Italy"' and '"van Gogh"')

It's as simple as that! I found the subscripting syntax to be more natural than the -> and ->> operators. Be aware that those operators did not go away, so if that syntax feels right by all means continue using it! Now let's take a look at updating data with the subscript syntax.

Updating Data

Prior to Postgres 14 updating jsonb data involved the using jsonb_set or jsonb_set_lax functions to do the data manipulation. In Postgres 14 we are now able to update data with the same subscript syntax that we use to query data. Before looking at examples let's make note of a few important topics related to updating jsonb data via subscripts.

1. The subscript is used directly in the SET statement ie SET document['country'] = '"France"'

2. Nested object traversal is possible via document['a']['b']

3. If the value at the expected location does not exist, it will be created

4. If an attempted traversal encounters a key that is not an object, then an error will be thrown

   • Using document['a']['b']['c'] as an example, if b is not an object (ie string, number or null) then an error will be thrown.

Let's take a look at some examples to provide a little more context.

-- Update jsonb object value by key
update artist
set document['country'] = '"Holland"'
where document['last_name'] = '"van Gogh"';

-- Update jsonb array value by index
update artist
set document['paintings'][1] = '"The Final Meal"'
where document['last_name'] = '"da Vinci"';

-- Add a jsonb key/value via update when the key doesn't exist already
update artist
-- "occupation"" key will be added to all documents with value "artist"
set document['occupation'] = '"artist"';

-- Append to jsonb array when index doesn't exist already
update artist
set document['paintings'][3] = '"Virgin of the Rocks"'
where document['last_name'] = '"da Vinci"';

I hate to be a broken record, but I find this syntax for updating JSON data so much better than pre-existing options. We may still need to use the methods to handle special scenarios, but I think that the subscript syntax will provide the functionality needed for the majority of use cases.

I hope this post goes to show how seriously Postgres is taking its support for NoSQL-like data structures. In my opinion, it's a great option for use cases that may need to combine relational and non-relational data. If you'd like to learn even more about the new features in Postgres 14 be sure to check out the release notes here!