PostgreSQL 17 introduces new features for working with JSON data. These features align PostgreSQL more closely with the SQL/JSON standard and improve the developer experience when dealing with semi-structured data. In this blog, we will explore the new JSON capabilities by walking through real-world examples using a table with a jsonb column.

The JSON Data Challenge for Web Developers

When building modern web applications, it’s common to handle JSON data—whether it’s from API responses, user data, or configuration files. PostgreSQL has supported JSON for years, and with the release of PostgreSQL 17, working with JSON becomes even more streamlined.
Let’s start by creating a table to store user data in a jsonb column and use it to demonstrate the new features.

I. Creating a Table with a jsonb Column

To demonstrate PostgreSQL 17’s new features, let’s create a simple table called users that stores user information in a jsonb column.

postgres=# CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    profile JSONB
);
CREATE TABLE

This table has three columns:

  • id: A unique identifier for each user.
  • name: The user’s name.
  • profile: A jsonb column that stores various information about the user, such as their age, preferences, and settings.

Inserting Data

Now, we’ll insert some user data in JSON format into the profile column.

postgres=# INSERT INTO users (name, profile)
VALUES
('John Doe', '{"age": 30, "preferences": {"newsletter": true, "theme": "dark"}}'),
('Jane Smith', '{"age": 25, "preferences": {"newsletter": false, "theme": "light"}}'),
('Alice Brown', '{"age": 35, "preferences": {"newsletter": true, "theme": "dark"}, "address":                        {"city": "Paris", "country": "France"}}');
INSERT 0 3

We now have three users with different JSON profiles, each containing details such as age, preferences for newsletters and themes, and, in Alice’s case, an address.

postgres=# select * from users;
 id |    name     |                                                       profile
----+-------------+----------------------------------------------------------------------------------------------------------------------
  1 | John Doe    | {"age": 30, "preferences": {"theme": "dark", "newsletter": true}}
  2 | Jane Smith  | {"age": 25, "preferences": {"theme": "light", "newsletter": false}}
  3 | Alice Brown | {"age": 35, "address": {"city": "Paris", "country": "France"}, "preferences": {"theme": "dark", "newsletter": true}}
(3 rows)

II. Using PostgreSQL 17’s New JSON Features

With the table set up and populated, let’s explore the new JSON-related features in PostgreSQL 17, such as JSON_TABLE, SQL/JSON query functions, and enhanced jsonpath expressions.

a. JSON_TABLE: Converting JSON into Tabular Format

The JSON_TABLE function allows us to transform our jsonb data into rows and columns. This is particularly useful when we want to extract structured data from JSON documents stored in a relational database.

Let’s extract the age and theme from the profile column and convert it into a tabular format:

postgres=# SELECT *
FROM JSON_TABLE(
    (SELECT profile FROM users WHERE name = 'Alice Brown'),
    '$.preferences' COLUMNS (theme TEXT PATH '$.theme', newsletter BOOLEAN PATH '$.newsletter')
) AS jt;
 theme | newsletter
-------+------------
 dark  | t
(1 row)

Here, we extracted Alice Brown’s theme preference and whether she subscribes to the newsletter from her profile. The $ symbol is essential for this operation.

Here’s a breakdown of how this works:

  1. $.preferences: This part refers to the preferences key at the root of the JSON document. Once this key is selected, it acts as the root for the columns inside the preferences object.
  2. Inside the COLUMNS clause:
    • $.theme: Here, $ refers to the root of the preferences object, not the root of the entire JSON document. So, it looks for the theme key inside the preferences object.
    • $.newsletter: Similarly, $ here refers to the root of the preferences object, and it looks for the newsletter key within that object.

In this context, the $ in the COLUMNS clause is “relative” to the object you are working with, which in this case is preferences. This is a key concept in using the JSON_TABLE function and jsonpath expressions in PostgreSQL—$ adapts based on the context of the object you’re working with at that stage of the query.

b. jsonb_build_object: Creating JSON Data in Queries

PostgreSQL allows you to create JSON directly from SQL expressions, making it easier to work with JSON data dynamically. This function exists since PostgreSQL 12, but I believe that it makes sense to present it here.

Let’s construct some JSON data based on our users table:

postgres=# SELECT name,
       jsonb_build_object(
           'age', profile->>'age',
           'theme', profile->'preferences'->>'theme'
       ) AS constructed_json
FROM users;
    name     |        constructed_json
-------------+---------------------------------
 John Doe    | {"age": "30", "theme": "dark"}
 Jane Smith  | {"age": "25", "theme": "light"}
 Alice Brown | {"age": "35", "theme": "dark"}
(3 rows)

This query dynamically builds a JSON object from the age and theme fields in the profile column.

c. SQL/JSON Query Functions: Simplifying JSON Queries

PostgreSQL 17 introduces several new SQL/JSON query functions, such as JSON_EXISTS, JSON_QUERY, and JSON_VALUE. These functions allow you to query and extract values from JSON documents more efficiently.

Example: Checking for the Existence of a Key

Let’s check if the address key exists in John Doe’s profile:

postgres=# SELECT JSON_EXISTS(profile, '$.address')
FROM users
WHERE name = 'John Doe';
 json_exists
-------------
 f
(1 row)

Example: Extracting Scalar Values

We can use the JSON_VALUE function to extract specific values from a JSON document. For example, let’s extract the city from Alice’s address:

postgres=# SELECT JSON_VALUE(profile, '$.address.city')
FROM users
WHERE name = 'Alice Brown';
 json_value
------------
 Paris
(1 row)

The JSON_VALUE function simplifies the process of extracting individual scalar values from JSON documents. Use JSON_VALUE() only when you expect the extracted value to be a single SQL/JSON scalar. Attempting to retrieve multiple values will result in an error. If the extracted value might be an object or an array, opt for the JSON_QUERY function instead.

Example: extract specific values from a JSON document

The JSON_QUERY function in PostgreSQL provides a powerful way to extract data from JSONB columns using path expressions. By leveraging its various options, developers can customize the output format, handle errors gracefully, and work efficiently with JSON data stored in PostgreSQL. Now, let’s use JSON_QUERY to extract the preferences for each user. We want to get the preferences object for each user’s profile.

postgres=# SELECT
    name,
    JSON_QUERY(profile, '$.preferences') AS user_preferences
FROM users;
    name     |            user_preferences
-------------+-----------------------------------------
 John Doe    | {"theme": "dark", "newsletter": true}
 Jane Smith  | {"theme": "light", "newsletter": false}
 Alice Brown | {"theme": "dark", "newsletter": true}
(3 rows)

d. Enhanced jsonpath Expressions

PostgreSQL improves its support for jsonpath expressions, enabling more advanced queries. You can now cast JSON values into native PostgreSQL types, such as integers or booleans.

Let’s extract Jane Smith’s age and cast it as an integer:

postgres=# SELECT JSON_VALUE(profile, '$.age' RETURNING INT) AS age_int
FROM users
WHERE name = 'Jane Smith';
 age_int
---------
      25
(1 row)

This query demonstrates how you can convert JSON data into a native PostgreSQL type using the RETURNING clause.

Conclusion

PostgreSQL 17 brings powerful new features for working with JSON data, making it easier for web developers to query and manipulate JSON in a database. We explored how JSON_TABLE, new SQL/JSON functions, and enhanced jsonpath expressions help convert JSON data into a more usable format, extract values, and even handle complex queries.

These new tools make working with semi-structured data simpler, allowing you to build more efficient and flexible web applications. If you often work with JSON in PostgreSQL, upgrading to version 17 will streamline your workflow and enhance your capabilities.

Stay tuned and don’t forget to check PostgreSQL 17 Release note https://www.postgresql.org/about/news/postgresql-17-released-2936/

Post Views: 1,253
Thumbnail [60x60]
by
Joan Frey