Postgres json_extract_path() function
Extracts a JSON sub-object at the specified path
You can use the json_extract_path
function to extract the value at a specified path within a JSON
document. This approach is performant compared to querying the entire JSON
payload and processing it on the application side. It is particularly useful when dealing with nested JSON
structures.
Function signature
Example usage
To illustrate the json_extract_path
function in Postgres, let's consider a scenario where we have a table storing information about books. Each book has a JSON
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 json_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 products in an e-commerce system. The table schema and data are outlined below.
products
json_extract_path
Extract from nested JSON objects with Let's use json_extract_path
to retrieve information about the storage type and capacity for each product, demonstrating how to extract values from a nested JSON
object.
This query returns the following values:
json_extract_path
Extract from array with Now, let's use json_extract_path
to extract information about the associated tags as well, demonstrating how to extract values from a JSON
array.
This query returns the following values:
json_extract_path
in Joins
Use Let's say you have two tables, employees
and departments
, and the employees
table has a JSON
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 JSON
column. The table schemas and data used in this example are shown below.
departments
employees
You can use JOIN
with json_extract_path
to retrieve information:
This query returns the following values:
The json_extract_path
function extracts the value of the department
key from the JSON
column in the employees
table. The JOIN
is then performed based on matching department names.
Additional considerations
Performance and Indexing
The json_extract_path
function performs well when extracting data from JSON
documents, especially compared to extracting data in application code. It allows performing the extraction directly in the database, avoiding transferring entire JSON
documents to the application.
However, performance can degrade with highly nested JSON
structures and very long text strings. In those cases, using the binary JSONB
data type and the jsonb_extract_path
function will likely offer better performance.
Indexing JSON
documents can also significantly improve json_extract_path
query performance when filtering data based on values extracted from JSON
.
Alternative functions
-
json_extract_path_text - The regular
json_extract_path
function returns the extracted value as aJSON
object or array, preserving itsJSON
structure, whereas the alternativejson_extract_path_text
function returns the extracted value as a plain text string, casting anyJSON
objects or arrays to their string representations.Use the regular
json_extract_path
function when you need to applyJSON
-specific functions or operators to the extracted value, requiringJSON
data types. The alternativejson_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. -
jsonb_extract_path
- Thejsonb_extract_path
function works with thejsonb
data type, which offers a binary representation ofJSON
data. This alternative function is generally faster thanjson_extract_path
for most operations, as it's optimized for the binaryjsonb
format. This difference in performance is often more pronounced with largerJSON
structures 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.