Introduction to the PostgreSQL jsonb_extract_path_text() function

Thejsonb_extract_path_text() function allows you to extract aJSON subobject as text from a JSONB value at a specified path.

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

jsonb_extract_path_text(    target jsonb,    VARIADIC path_elemstext[])

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 thepath_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":"john.doe@example.com"},      {"type":"phone","value":"408-123-456"}    ]  }}

Here are some examples of the path expressions:

• 'employee' returns the entireemployee 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 thecontacts array, which isjohn.doe@example.com

PostgreSQL jsonb_extract_path_text() function examples

Let's explore some examples of using thejsonb_extract_path_text() function.

Setting up a sample table

First,create a new table calleddocuments:

CREATE TABLE documents(   idSERIAL PRIMARY KEY,   data JSONB);

Second,insert two rows into thedocuments table:

INSERT INTO documents(data)VALUES  ('{"employee":{"name":"John Doe","age":22,"contacts":[{"type":"email","value":"john.doe@example.com"},{"type":"phone","value":"408-123-456"}]}}'),  ('{"employee":{"name":"Jane Doe","age":21,"contacts":[{"type":"email","value":"jane.doe@example.com"},{"type":"phone","value":"408-123-789"}]}}');

Basic jsonb_extract_path_text() function examples

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

SELECT  jsonb_extract_path_text(data,'employee') employeeFROM  documents;

Output:

employee------------------------------------------------------------------------------------------------------------------------------------------- {"age": 22, "name": "John Doe", "contacts": [{"type": "email", "value": "john.doe@example.com"}, {"type": "phone", "value": "408-123-456"}]} {"age": 21, "name": "Jane Doe", "contacts": [{"type": "email", "value": "jane.doe@example.com"}, {"type": "phone", "value": "408-123-789"}]}(2 rows)

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

SELECT  jsonb_extract_path_text(data,'employee','name')nameFROM  documents;

Output:

name---------- John Doe Jane Doe(2 rows)

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

SELECT  jsonb_extract_path_text(    data,'employee','contacts','0',    'value'  ) emailFROM  documents;

Output:

email------------------- john.doe@example.com jane.doe@example.com(2 rows)

Summary

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

Last updated onFebruary 25, 2024

On this page

• Introduction to the PostgreSQL jsonb_extract_path_text() function
• PostgreSQL jsonb_extract_path_text() function examples
• Summary