Postgres json_extract_path_text() Function
Extracts a JSON sub-object at the specified path as text
The json_extract_path_text
function is designed to simplify extracting text from JSON
data in Postgres. This function is similar to json_extract_path
— it also produces the value at the specified path from a JSON
object but casts it to plain text before returning. This makes it more straightforward for text manipulation and comparison operations.
Function signature
The function accepts a JSON
object and a variadic list of elements that specify the path to the desired value.
Example usage
Let's consider a users
table with a JSON
column named profile
containing various user details.
Here's how we can create the table and insert some sample data:
To extract and view the email addresses of all users, we can run the following query:
This query returns the following:
Advanced examples
json_extract_path_text
in Joins
Use Let's say we have another table, hobbies
, that includes additional information such as difficulty level and the average cost to practice each hobby.
We can create the hobbies
table with some sample data with the following statements:
The users
table we created previously has a JSON
column named profile
that contains information about each user's preferred hobbies. A fun exercise could be to find if a user has any hobbies that are easy to get started with. Then we can recommend they engage with it more often.
To fetch this list, we can run the query below.
We use json_extract_path_text
to extract the list of hobbies for each user, and then check if the name of an easy hobby is present in the list.
This query returns the following:
json_extract_path_text
Extracting values from JSON arrays with json_extract_path_text
can also be used to extract values from JSON
arrays.
For instance, to extract the first and second hobbies for everyone, we can run the following query:
This query returns the following:
Additional considerations
Performance and indexing
Performance considerations for json_extract_path_text
are similar to those for json_extract_path
. It is efficient for extracting data but can be impacted by large JSON
objects or complex queries. Indexing JSON
fields can improve performance in some cases.
Alternative functions
- json_extract_path - This is a similar function that can extract data from a
JSON
object at the specified path. The difference is that it returns aJSON
object, whilejson_extract_path_text
always returns text. The right function to use depends on what you want to use the output data for. - jsonb_extract_path_text - This is a similar function that can extract data from a
JSON
object at the specified path. It is more efficient but works only with data of the typeJSONB
.
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.