intro
Write jsonpath expressions in PostgreSQL through the SQL/JSON path language to query JSON data with no effort.
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:
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:
To provide a natural way of working with JSON data, the jsonpath syntax relies on some JavaScript conventions:
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
Accessors
The most important accessors you should know are:
Operators and methods
The most interesting are:
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
}
In detail, you have it in the first row of a configs table in the games database:
Time to see some PostgreSQL jsonpath expressions in action in real-world queries.
1. 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.
This query will produce: ["Ninjohn", "JaneTheBest", "Mary84"]
2. 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:
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.
3. 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;
This will get the following result:
Note the use of the $min_score named variable.
4. Get the number of players
1
SELECT jsonb_path_query("data", '$.players.size()') AS total_players
2
FROM "configs"
3
4
WHERE id = 1;
This returns: 3
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:
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.
