PostgreSQL jsonb_extract_path_text() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_extract_path_text() function to extract a JSON subobject at the specified path.

Introduction to the PostgreSQL jsonb_extract_path_text() function

The jsonb_extract_path_text() function allows you to extract a JSON subobject as text from a JSONB value at a specified path.

The following shows the basic syntax of the jsonb_extract_path_text() function:

jsonb_extract_path_text(
    target jsonb, 
    VARIADIC path_elems text[]
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• target is a JSONB data from which you want to extract data as text.
• path_elems is a list of paths that you want to locate the elements in the JSONB data for extraction.

Here’s the syntax for the path_elems parameter:

• 'key': Access a specific key in a JSON object.
• ‘array_index‘: Access an element in a JSON array using its index.

Additionally, you can chain these path components together to navigate through the nested objects or arrays.

For example, suppose you have the following JSON object:

{
  "employee": {
    "name": "John Doe",
    "age": 22,
    "contacts": [
      {"type": "email", "value": "[email protected]"},
      {"type": "phone", "value": "408-123-456"}
    ]
  }
}Code language: SQL (Structured Query Language) (sql)

Here are some examples of the path expressions:

• 'employee' returns the entire employee object.
• ['employee', 'name'] returns the name within the employee object, which is "John Doe".
• ['employee', 'contacts', '0', 'value'] returns the value in the first element of the contacts array, which is [email protected]

PostgreSQL jsonb_extract_path_text() function examples

Let’s explore some examples of using the jsonb_extract_path_text() function.

Setting up a sample table

First, create a new table called documents:

CREATE TABLE documents(
   id SERIAL PRIMARY KEY,
   data JSONB
);Code language: SQL (Structured Query Language) (sql)

Second, insert two rows into the documents table:

INSERT INTO documents(data)
VALUES
  ('{"employee":{"name":"John Doe","age":22,"contacts":[{"type":"email","value":"[email protected]"},{"type":"phone","value":"408-123-456"}]}}'),
  ('{"employee":{"name":"Jane Doe","age":21,"contacts":[{"type":"email","value":"[email protected]"},{"type":"phone","value":"408-123-789"}]}}');Code language: SQL (Structured Query Language) (sql)

Basic jsonb_extract_path_text() function examples

The following example uses the jsonb_extract_path_text() function to extract the employee object:

SELECT 
  jsonb_extract_path_text(data, 'employee') employee 
FROM 
  documents;Code language: SQL (Structured Query Language) (sql)

Output:

                                                                 employee
-------------------------------------------------------------------------------------------------------------------------------------------
 {"age": 22, "name": "John Doe", "contacts": [{"type": "email", "value": "[email protected]"}, {"type": "phone", "value": "408-123-456"}]}
 {"age": 21, "name": "Jane Doe", "contacts": [{"type": "email", "value": "[email protected]"}, {"type": "phone", "value": "408-123-789"}]}
(2 rows)Code language: SQL (Structured Query Language) (sql)

The following example uses the jsonb_extract_path_text() function to extract the names of employees:

SELECT 
  jsonb_extract_path_text(data, 'employee', 'name') name 
FROM 
  documents;Code language: SQL (Structured Query Language) (sql)

Output:

   name
----------
 John Doe
 Jane Doe
(2 rows)Code language: SQL (Structured Query Language) (sql)

The following example uses the jsonb_extract_path_text() function to extract the emails of employees:

SELECT 
  jsonb_extract_path_text(
    data, 'employee', 'contacts', '0', 
    'value'
  ) email 
FROM 
  documents;Code language: SQL (Structured Query Language) (sql)

Output:

       email
-------------------
 [email protected]
 [email protected]
(2 rows)
Code language: SQL (Structured Query Language) (sql)

Summary

• Use the jsonb_extract_path_text() function to extract JSON subobject as text at the specified path.