json_extract_path()

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

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

json_extract_path() was added in PostgreSQL 9.3.

Usage

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

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():

postgres=# SELECT json_extract_path(
             $$
               {"foo":
                 {"bar": "bip"},
                "bar":
                 {"bat": "bop", "baz": "zoo"}}
             $$,
             'bar',
             'baz'
           );
 json_extract_path 
-------------------
 "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_text(), jsonb_extract_path()