Postgres jsonb_extract_path() function
Extracts a JSONB sub-object at the specified path
You can use the jsonb_extract_path
function to extract the value at a specified path within a JSONB
document. This approach is more performant compared to querying the entire JSONB
payload and processing it on the application side. It is particularly useful when dealing with nested JSONB
structures.
Function signature
Example usage
To illustrate the jsonb_extract_path
function in Postgres, let's consider a scenario where we have a table storing information about books. Each book has a JSONB
column containing details such as title
, author
, and publication year
. You can create the book
table using the SQL statements shown below.
books
Now, let's use the jsonb_extract_path
function to extract the title
and author
of each book:
This query returns the following values:
Advanced examples
Consider a products
table that stores information about the products in an e-commerce system. The table schema and data are outlined below.
products
jsonb_extract_path
Extract value from nested JSONB object with Let's use jsonb_extract_path
to retrieve information about the storage type and capacity for each product, demonstrating how to extract values from a nested JSONB
object.
This query returns the following values:
jsonb_extract_path
Extract values from JSON array with Now, let's use jsonb_extract_path
to extract information about the associated tags as well, demonstrating how to extract values from a JSONB
array.
This query returns the following values:
jsonb_extract_path
Joining data with values extracted using Let's say you have two tables, employees
and departments
, and the employees
table has a JSONB
column named details
that contains information about each employee's department. You want to join these tables based on the department information stored in the JSONB
column.
The table schemas and data used in this example are shown below.
departments
employees
You can use JOIN
with jsonb_extract_path
to retrieve the value to join on:
This query returns the following values:
The jsonb_extract_path
function extracts the value of the department
key from the JSONB
column in the employees
table. The JOIN
is then performed based on matching department names.
jsonb_extract_path
Handling invalid path inputs to jsonb_extract_path
handles an invalid path by returning NULL
, as in the following example:
The query above, which specifies an invalid path ('speks'
instead of 'specs'
), returns NULL
as shown:
Additional considerations
Performance and Indexing
The jsonb_extract_path
function performs well when extracting data from JSONB
documents, especially compared to extracting data in application code. It allows performing the extraction directly in the database, avoiding transferring entire JSONB
documents to the application.
Indexing JSONB
documents can also significantly improve jsonb_extract_path
query performance when filtering data based on values extracted from JSON
.
Alternative functions
-
jsonb_extract_path_text - The regular
jsonb_extract_path
function returns the extracted value as aJSONB
object or array, preserving itsJSON
structure, whereas the alternativejsonb_extract_path_text
function returns the extracted value as a plain text string, casting anyJSONB
objects or arrays to their string representations.Use the regular
jsonb_extract_path
function when you need to applyJSONB
-specific functions or operators to the extracted value, requiringJSONB
data types. The alternativejsonb_extract_path_text
function is preferable if you need to work directly with the extracted value as a string, for text processing, concatenation, or comparison. -
json_extract_path - The
jsonb_extract_path
function works with theJSONB
data type, which offers a binary representation ofJSON
data, whereasjson_extract_path
takes aJSON
value as an input and returnsJSON
too. TheJSONB
variant is typically more performant at query time, which is even more pronounced with largerJSON
data payloads and frequent path extractions.
Resources
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.