Unlock the Power of JSON in PostgreSQL 14

A Brief History of JSON in PostgreSQL

JSON first made its debut in PostgreSQL 9.2, but its initial implementation was far from perfect. The database simply validated JSON data and stored it as a text string.

CREATE TABLE json_data (
    id SERIAL PRIMARY KEY,
    data JSON
);

However, with the introduction of JSONB in version 9.4, things took a dramatic turn. JSONB, often referred to as “better JSON,” stores data in a decomposed binary format, allowing for efficient manipulation and querying. This format also supports indexing, making it a preferred choice among developers.

CREATE TABLE jsonb_data (
    id SERIAL PRIMARY KEY,
    data JSONB
);

The Evolution of JSON in PostgreSQL

Fast-forward to PostgreSQL 12, which saw the addition of JSONPath, a powerful tool for querying JSON data.

SELECT *
FROM jsonb_data
WHERE data @@ '$.author.firstName == "Ada"';

Now, with the release of version 14, JSON support has reached new heights. But what exactly does this mean for developers?

JSON Conveniences in PostgreSQL 14

PostgreSQL 14 introduces a game-changing feature: accessing and manipulating JSON data using subscripts. This means you can work with JSON data in a conventional, intuitive way, without having to remember complex syntax.

Accessing JSON Data with Subscripts

Imagine you have a table containing blog posts with a JSON data column. You want to find the title of all blogs where the author’s first name is “Ada.”

SELECT data->'title'
FROM jsonb_data
WHERE data->'author'->>'firstName' = 'Ada';

In PostgreSQL 14, you can access JSON data using subscripts, making the process much more straightforward.

SELECT data['author']['firstName']
FROM jsonb_data
WHERE data['author']['firstName'] = 'Ada';

Updating JSON Data with Subscripting

Updating JSON data is also simplified in PostgreSQL 14. Gone are the days of using the jsonb_set function with its complex signature.

UPDATE jsonb_data
SET data = jsonb_set(data, '{author, firstName}', '"John"')
WHERE data->'author'->>'firstName' = 'Ada';

Now, you can update JSON data using subscripting, making it easier to modify your data.

UPDATE jsonb_data
SET data['author']['firstName'] = 'John'
WHERE data['author']['firstName'] = 'Ada';

Important Considerations for JSON Subscripting

While subscripting offers a more intuitive way of working with JSON data, there are some important edge cases to keep in mind.

• When updating a JSON key with a null value, subscripting will behave differently than jsonb_set.
• When specifying an index for an array, null values will be appended until the index is reached.

The Future of JSON in PostgreSQL

As we look to the future, it’s exciting to think about the possibilities that await us. Will we see better index leverage while using subscripts? Perhaps support for accessing JSON via dot notation?

One thing is certain – the improvements in PostgreSQL 14 have set the stage for even more innovative features to come.