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.