PostgreSQL json_extract_path() Function

The PostgreSQL json_extract_path() function extracts JSON nested value from a specified JSON value according to the specified path.

`json_extract_path()` Syntax

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

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

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() function returns a JSON value that is the value at the specified path in the specified JSON value from_json.

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() function returns null.

`json_extract_path()` Examples

JSON Array Example

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

SELECT
    json_extract_path('[1, 2, [3, 4]]', '0') AS "#[0]",
    json_extract_path('[1, 2, [3, 4]]', '1') AS "#[1]",
    json_extract_path('[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() 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('[1, 2, [3, 4]]', '2', '0') AS "#[2][0]",
    json_extract_path('[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:

json_extract_path('[1, 2, [3, 4]]', '2') returned [3, 4]
json_extract_path('[3, 4]', '0') returned 3

JSON object example

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

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

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

json_extract_path() Syntax