DbVisualizer

Posted on Oct 16, 2023 • Originally published at dbvis.com

PostgreSQL JSONPATH: Dealing with SQL/JSON Path Language

#postgres #json

Write jsonpath expressions in PostgreSQL through the SQL/JSON path language to query JSON data with no effort.

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client.
The PostgreSQL database

PostgreSQL supports the storage of JSON data in your tables through the jsonb data types. This opens up many opportunities that go beyond the traditional possibilities of SQL. However, this functionality would be limited without a way to naturally and efficiently query JSON.

Here is where the PostgreSQL jsonpath feature comes in! This special data type allows you to specify expressions in the SQL/JSON path language to access, filter, and extract JSON items. In this article, you will learn what jsonpath is, why it is useful, what it has to offer, and how to use it in some examples.

Let’s dive in!

What is PostgreSQL jsonpath?

The jsonpath data type adds support for the PostgreSQL SQL/JSON path language, which involves expressions aimed at efficiently querying JSON data. You can think of that language as a sort of XPath but for JSON and in an SQL environment.

In other words, an SQL/JSON path expression consists of a sequence of elements allowed by the PostgreSQL jsonpath data type. When the DBMS encounter those expressions, it passes them to the internal path engine for execution. If the evaluation is successful, it returns the JSON element or set of elements matching the JSON query logic of the expression.

You cannot use jsonpath expressions directly in PostgreSQL queries, but you must pass them to the JSON functions that accept them as arguments. The engine will first execute the expressions and then call the function with their results.

SQL/JSON Path Language Syntax

A jsonpath expression follows the SQL/JSON path language and consists of a sequence of path elements. The allowed ones are:

JSON primitive types: text, numeric, true, false, or null.
Parentheses: To define the order of evaluation or for writing filter sub-expressions.
Path variables, accessors, operators and methods: Special elements to select, filter, or access JSON data.

We will now dig into what these mean.

A filter expression begins with a question mark and accepts a condition in round parentheses: ? (condition)

An expression can contain one or more filter expressions. These work similarly to the WHERE clause in SQL and are executed first. After that step, the result set will include only JSON items that satisfy the provided condition with a true value. In detail, an SQL/JSON condition can return one of three values: true, false, or unknown. The unknown value plays the same role as NULL in SQL.

The most popular filter predicates are:

==, != or <>:, <, <=, >, >=, &&, ||, !
is unknown: Tests whether a condition returns the unknown value.
like_regex: Tests whether the first operand matches the regular expression given by the second operand.
starts with: Tests whether the second operand is an initial substring of the first operand.

To provide a natural way of working with JSON data, the jsonpath syntax relies on some JavaScript conventions:

.: The dot character is used for accessing members.
[]: Square brackets are used for accessing elements in arrays.

Note that SQL/JSON arrays start from 0 and not from 1 like regular SQL arrays.

PostgreSQL jsonpath expressions must be written in queries as SQL strings. So, you have to enclose them in single quotes '. String values inside the expressions should be enclosed with double quotes ".

Take a look at an example of a jsonpath expression: $.user.addresses[0].city This selects the city associated with the first address of a user. The $ character corresponds to the root of the JSON value being queried.

You can use it in a query as follows:



1 SELECT jsonb_path_query("data", '$.user.addresses[0].city') as "city"
2 FROM "user_data"
3 WHERE user_id = 1

Remember that you cannot use the SQL/JSON path language directly in the SELECT clause. To execute the expression, you need to pass it to the jsonb_path_query() function. If you are wondering why the function has “jsonb” and not “json” in its name, check out our JSON vs JSONB article.

SQL/JSON path expressions are evaluated from left to right, following parentheses to determine the order of operations.

PostgreSQL jsonpath: Variables, Accessors, Operators and Methods

As mentioned before, jsonpath supports some special elements. Let’s explore them all.

Variables

$: Represents the root of JSON value being queried.
$: A named variable that can be set in the vars argument accepted by several JSON functions. (e.g., jsonb_path_exists('{"values":[1, 2, 3, 4, 5]}', '$.values[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') returns whether the JSON data has at least one value between 2 and 4)
@: Represents the result of the path evaluation in a filter expression.

Accessors

The most important accessors you should know are:

.: Returns an object member with the specified name. If the key name does not meet the JavaScript rules for an identifier, it must be enclosed in double quotes (e.g., ."player-config").
."$": Returns the value of the named variable that can be set by the vars parameter of several JSON processing functions.
.*: Returns the values of all members at the top level of the current object.
.**: Processes all levels of the current object's JSON hierarchy and returns all member values, regardless of their nesting level.
[index]: Returns the single array element specified by the index.
[start_index, end_index]: Returns a slice of the array based on the index range, including beginning and ending elements.
[*]: Returns all array elements.

Operators and methods

The most interesting are:

+, -, *, /, %: Addition, subtraction, multiplication, division, and modulo.
.type(): Returns the type of a JSON item.
.size(): Returns the size of the JSON item (number of elements in an array, or 1 if it is not an array).

jsonpath Examples

Assume you have the following JSON data stored in a PostgreSQL table:



1 {
2     "game": "VillageDay",
3     "players": [
4         {
5             "username": "Ninjohn",
6             "score": 31830,
7             "achievements": [
8                 "First Victory"
9             ]
10         },
11         {
12             "username": "JaneTheBest",
13             "score": 2714685,
14             "achievements": [
15                 "First Victory",
16                 "100 Doubles",
17                 "100 Victories",
18                 "100 Triples"
19             ]
20         },
21         {
22             "username": "Mary84",
23             "score": 0,
24             "achievements": [
25             ]
26         }
27     ]
28 }

Note that the result printed by DbVisualizer matches the JSON content presented above.

Time to see some PostgreSQL jsonpath expressions in action in real-world queries.

Retrieving all usernames



1 SELECT jsonb_path_query_array("data", '$.players[*].username') AS usernames
2 FROM "configs"
3 WHERE "id" = 1;

Use jsonb_path_query_array() instead of jsonb_path_query() for expressions that return an array instead of plain JSON values.

Executing the SQL/JSON path query in DbVisualizer.

Find the players who have achieved the “Victory” accomplishment in a tabular format



1 SELECT jsonb_path_query("data", '$.players[*] ? (@.achievements[*] == "First Victory")') AS player
2 FROM "configs"
3    
4 WHERE "id" = 1;

The query returns:

This time the result set is a table and not a single value.<br> — This time the result set is a table and not a single value.

Note the use of the filter expression.

When jsonb_path_query involves several items, it returns them in tabular format. This means you can use the result of this query in IN clauses or other SELECTs.

Retrieve the username of the players with a score greater than or equal to 1000



1 SELECT jsonb_path_query("data", '($.players[*] ? (@.score >= $min_score)).username', '{"min_score": 1000}') AS username
2 FROM "configs"
3 WHERE "id" = 1;

The resulting table matches the expected result.

Note the use of the $min_score named variable.

Get the number of players



1 SELECT jsonb_path_query("data", '$.players.size()') AS total_players
2 FROM "configs"
3    
4 WHERE id = 1;

Note the use of the size() method to get the elements in the players array.

Congrats! You are now a PostgreSQL jsonpath master!

Conclusion

PostgreSQL supports JSON data through jsonb data type, which is a perfect solution if you are looking for NoSQL-like functionality. jsonpath further extends those capabilities by offering a language that allows data in JSON format to be intuitively explored and accessed.

That language is not that complex, but some of operators and methods are not so easy to understand. Here is why you need to test your SQL/JSON queries in a database client that fully supports PostgreSQL, such as DbVisualizer.

This tool allows you to explore data from dozens of DBMSs while offering query optimization features, visual data exploration functionality, and full support for most database-specific features, including jsonpath. Download DbVisualizer for free now!

FAQ

How does the SQL/JSON path language differ from regular SQL in PostgreSQL?

The SQL/JSON path language in PostgreSQL is an extension of regular SQL that allows querying and extracting data from JSON documents within the database. While regular SQL focuses on relational data, SQL/JSON path language enables navigation through nested JSON structures and retrieval of specific JSON elements.

What are the two modes of handling structural errors in SQL/JSON path expressions?

The two modes of handling structural errors in SQL/JSON path expressions are:

LAX: If a path expression encounters a structural error, it returns a NULL value without raising an error. This is the default mode. Specify this behavior with the following syntax: lax .
STRICT: A structural error causes an error. Enable with mode with the following syntax: strict .

Can jsonpath be used with other PostgreSQL features, such as indexing, full-text search, or triggers?

Yes, jsonpath can be used with other PostgreSQL features. Functional indexes support efficient querying of data through JSON functions, full-text queries enable searching within JSON text, and triggers can use SQL/JSON path expressions.

What are some common mistakes when using the SQL/JSON path language?

Some common mistakes in SQL/JSON path language usage include incorrect path expressions, improper handling of null values, forgetting to use jsonb_path_query_array when expecting multiple results, and not considering the implications of the LAX or STRICT modes.

How does SQL/JSON Path Language compare to other JSON querying languages or libraries available in PostgreSQL?

The SQL/JSON Path Language in PostgreSQL and Python JSONPath are both used for querying JSON data. The first relies on SQL-like syntax, is integrated with PostgreSQL, and represents an ideal solution for database-related JSON querying. The second uses XPath-like syntax and is suitable for standalone Python apps.

About the author

Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

DEV Community