PostgreSQL json_extract_path_text() Function

The PostgreSQL json_extract_path_text() function extracts JSON nested value from a specified JSON 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 JSON.

json_extract_path_text() Syntax

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

json_extract_path_text(from_json JSON, VARIADIC path TEXT[]) -> TEXT

Parameters

from_json

Required. The JSON value from which to extract.

path

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

Return value

The PostgreSQL json_extract_path_text() function returns a text that is the textual representation of the JSON value at the specified path in the specified JSON 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 JSON value.

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

json_extract_path_text() Examples

JSON Array Example

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

SELECT
    json_extract_path_text('[1, 2, [3, 4]]', '0') AS "#[0]",
    json_extract_path_text('[1, 2, [3, 4]]', '1') AS "#[1]",
    json_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 json_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
    json_extract_path_text('[1, 2, [3, 4]]', '2', '0') AS "#[2][0]",
    json_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. json_extract_path_text('[1, 2, [3, 4]]', '2') returned [3, 4]
  2. json_extract_path_text('[3, 4]', '0') returned 3
  3. converted 3 to text and returned

JSON Object Example

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

SELECT
    json_extract_path_text('{"a": 1, "b": {"x": 2, "y": 3}}', 'a') AS "#.a",
    json_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
    json_extract_path_text('{"a": 1, "b": {"x": 2, "y": 3}}', 'b', 'x') AS "#.b.x",
    json_extract_path_text('{"a": 1, "b": {"x": 2, "y": 3}}', 'b', 'y') AS "#.b.y";

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