PostgreSQL jsonb_extract_path() Function

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

jsonb_extract_path() Syntax

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

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

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

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

jsonb_extract_path() Examples

JSONB Array Example

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

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

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

JSONB object example

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

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

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