> This page location: JSON Functions > jsonb_extract_path > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL jsonb_extract_path() Function **Summary**: in this tutorial, you will learn how to use the PostgreSQL `jsonb_extract_path()` function to extract a JSON subobject at the specified path. ## Introduction to the PostgreSQL jsonb_extract_path() function The `jsonb_extract_path()` function allows you to extract a JSON sub-object from a JSONB value at a specified path. Here's the basic syntax of the `jsonb_extract_path()` function: ```sql jsonb_extract_path(target jsonb, VARIADIC path_elems text[]) ``` In this syntax: - `target` is a JSONB data from which you want to extract data. - `path_elems` is a list of paths that you want to locate the elements in the JSONB data for extraction. Note that the path is not a JSON path. The syntax for the `path_elems` parameter is as follows: - `'key'`: Access a specific key in the JSON object. - '`array_index`': Access an element in a JSON array using its index. To navigate through the nested objects or array, you can chain these path components together. Suppose you have the following JSON object: ```json { "employee": { "name": "John Doe", "age": 22, "contacts": [ {"type": "email", "value": "john.doe@example.com"}, {"type": "phone", "value": "408-123-456"} ] } } ``` Here are some examples of the path expressions: - `'employee'` returns the entire `employee` object. - `['employee', 'name']` returns the name within the employee object, which is `"John Doe"`. - `['employee', 'contacts', '0', 'value']` returns the value in the first element of the contacts array, which is `john.doe@example.com` ## PostgreSQL jsonb_extract_path() function examples Let's take some examples of using the `jsonb_extract_path()` function. ### Setting up a sample table First, [create a new table](../postgresql-tutorial/postgresql-create-table) called `documents`: ```sql CREATE TABLE documents( id SERIAL PRIMARY KEY, data JSONB ); ``` Second, [insert two rows](../postgresql-tutorial/postgresql-insert) into the `documents` table: ```sql INSERT INTO documents(data) VALUES ('{"employee":{"name":"John Doe","age":22,"contacts":[{"type":"email","value":"john.doe@example.com"},{"type":"phone","value":"408-123-456"}]}}'), ('{"employee":{"name":"Jane Doe","age":21,"contacts":[{"type":"email","value":"jane.doe@example.com"},{"type":"phone","value":"408-123-789"}]}}'); ``` ### Basic jsonb_extract_path() function examples The following example uses the `jsonb_extract_path()` function to extract the employee object: ```sql SELECT jsonb_extract_path(data, 'employee') employee FROM documents; ``` Output: ```text employee ------------------------------------------------------------------------------------------------------------------------------------------- {"age": 22, "name": "John Doe", "contacts": [{"type": "email", "value": "john.doe@example.com"}, {"type": "phone", "value": "408-123-456"}]} {"age": 21, "name": "Jane Doe", "contacts": [{"type": "email", "value": "jane.doe@example.com"}, {"type": "phone", "value": "408-123-789"}]} (2 rows) ``` The following example uses the `jsonb_extract_path()` function to extract the names of employees: ```sql SELECT jsonb_extract_path(data, 'employee', 'name') name FROM documents; ``` Output: ```text name ------------ "John Doe" "Jane Doe" (2 rows) ``` The following example uses the `jsonb_extract_path()` function to extract the emails of employees: ```sql SELECT jsonb_extract_path( data, 'employee', 'contacts', '0', 'value' ) email FROM documents; ``` Output: ```text email --------------------- "john.doe@example.com" "jane.doe@example.com" (2 rows) ``` ## Summary - Use the `jsonb_extract_path()` function to extract JSON sub-object at the specified path. --- ## Related docs (JSON Functions) - [Extracting JSON Data](https://neon.com/postgresql/postgresql-json-functions/postgresql-json-extract) - [JSONB Operators](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb-operators) - [jsonb_agg](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_agg) - [jsonb_array_elements](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_array_elements) - [jsonb_array_elements_text](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_array_elements_text) - [jsonb_array_length](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_array_length) - [jsonb_build_array](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_build_array) - [jsonb_build_object](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_build_object) - [jsonb_each](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_each) - [jsonb_each_text](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_each_text) - [jsonb_extract_path_text](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_extract_path_text) - [jsonb_insert](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_insert) - [jsonb_object](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_object) - [jsonb_object_agg](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_object_agg) - [jsonb_object_keys](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_object_keys) - [JSON Path](https://neon.com/postgresql/postgresql-json-functions/postgresql-json-path) - [jsonb_path_exists](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_exists) - [jsonb_path_query](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_query) - [jsonb_path_query_array](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_query_array) - [jsonb_path_query_first](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_query_first) - [jsonb_pretty](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_pretty) - [jsonb_set](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_set) - [jsonb_strip_nulls](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_strip_nulls) - [jsonb_to_record](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_to_record) - [jsonb_populate_record](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_populate_record) - [jsonb_populate_recordset](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_populate_recordset) - [jsonb_typeof](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_typeof) - [row_to_json](https://neon.com/postgresql/postgresql-json-functions/postgresql-row_to_json) - [to_jsonb](https://neon.com/postgresql/postgresql-json-functions/postgresql-to_jsonb)