json_extract_path_text()

A function for extracting a JSON sub-object at the specified path as text

json_extract_path_text() is a system function for extracting a JSON sub-object at the specified path as text. It is equivalent to the operator #>>.

json_extract_path_text() was added in PostgreSQL 9.3.

Usage

json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text

NULL is returned if no valid path is found.

This function provides an alternative to the operator #>>, which is easier to use when e.g. generating a parameterized list of path values.

Change history

Examples

Basic usage example for json_extract_path_text():

postgres=# SELECT json_extract_path_text(
             $$
               {"foo":
                 {"bar": "bip"},
                "bar":
                 {"bat": "bop", "baz": "zoo"}}
             $$,
             'bar',
             'baz'
           );
 json_extract_path_text 
------------------------
 zoo
(1 row)

This is equivalent to:

postgres=# SELECT $$
               {"foo":
                 {"bar": "bip"},
                "bar":
                 {"bat": "bop", "baz": "zoo"}}
           $$::json #>> '{bar, baz}';
 ?column? 
----------
 zoo
(1 row)

Categories

JSON, System function

See also

json_extract_path(), jsonb_extract_path_text()