PostgreSQL jsonb_extract_path_text() Function

The PostgreSQL jsonb_extract_path_text() function extracts JSONB nested value from a specified JSONB value according to the specified path and returns the result as text.

This function is similar to the jsonb_extract_path() function, except that this function returns a value of type text instead of type JSONB.

jsonb_extract_path_text() Syntax

This is the syntax of the PostgreSQL jsonb_extract_path_text() function:

jsonb_extract_path_text(from_json JSONB, VARIADIC path TEXT[]) -> TEXT

Parameters

from_json

Required. The JSONB value from which to extract.

path

Required. It is a variadic parameter list and indicates the paths to extract.

Return value

The PostgreSQL jsonb_extract_path_text() function returns a text that is the textual representation of the JSONB value at the specified path in the specified JSONB value.

If you provide multiple paths in parameters list, the previous path should contain the latter path, so that the value may exist in the JSONB value.

If the path does not exist, the jsonb_extract_path_text() function returns null.

jsonb_extract_path_text() Examples

JSONB Array Example

This example shows how to use the PostgreSQL jsonb_extract_path_text() function to extract values ​​from a JSONB array at a specified index.

SELECT
    jsonb_extract_path_text('[1, 2, [3, 4]]', '0') AS "#[0]",
    jsonb_extract_path_text('[1, 2, [3, 4]]', '1') AS "#[1]",
    jsonb_extract_path_text('[1, 2, [3, 4]]', '2') AS "#[2]";

 #[0] | #[1] |  #[2]
------+------+--------
 1    | 2    | [3, 4]

Here, we extracted the values ​​at indices 0, 1, and 2, and the jsonb_extract_path_text() function gives the correct feedback respectively.

What should we do if we wanted to extract the value in the nested array at index 2? Please use the following statement:

SELECT
    jsonb_extract_path_text('[1, 2, [3, 4]]', '2', '0') AS "#[2][0]",
    jsonb_extract_path_text('[1, 2, [3, 4]]', '2', '1') AS "#[2][1]";

 #[2][0] | #[2][1]
---------+---------
 3       | 4

Here, we used '2'and '0' in the variadic parameter path, that is equivalent to the following calculation steps:

  1. jsonb_extract_path_text('[1, 2, [3, 4]]', '2') returned [3, 4]
  2. jsonb_extract_path_text('[3, 4]', '0') returned 3
  3. converted 3 to text and returned

JSONB Object Example

This example shows how to use the PostgreSQL jsonb_extract_path_text() function to extract values ​​from a JSONB object at a specified path.

SELECT
    jsonb_extract_path_text('{"a": 1, "b": {"x": 2, "y": 3}}', 'a') AS "#.a",
    jsonb_extract_path_text('{"a": 1, "b": {"x": 2, "y": 3}}', 'b') AS "#.b";

 #.a |       #.b
-----+------------------
 1   | {"x": 2, "y": 3}

Of course, you can also extract values nested in subobjects by specifying multiple paths, such as:

SELECT
    jsonb_extract_path_text('{"a": 1, "b": {"x": 2, "y": 3}}', 'b', 'x') AS "#.b.x",
    jsonb_extract_path_text('{"a": 1, "b": {"x": 2, "y": 3}}', 'b', 'y') AS "#.b.y";

 #.b.x | #.b.y
-------+-------
 2     | 3