> This page location: JSON Functions > jsonb_path_query_array > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL jsonb_path_query_array() Function **Summary**: in this tutorial, you will learn how to use the PostgreSQL `jsonb_path_query_array()` function to query JSONB data using a JSON path and return matched elements as a JSON array. ## Introduction to PostgreSQL jsonb_path_query_array() function The `jsonb_path_query_array()` function allows you to query [JSONB](../postgresql-tutorial/postgresql-json) data using a [JSON path expression](https://neon.com/postgresql/postgresql-json-functions/postgresql-json-path). Here's the basic syntax of the `jsonb_path_query_array()` function: ```sql jsonb_path_query_array(jsonb_data, json_path) ``` In this syntax: - First, specify the `jsonb_data` that you want to query. - Second, provide a `json_path` that you want to match elements within the `jsonb_data`. The `jsonb_path_query_array()` function returns the matched elements as a JSON array. If the function does not find any matched element, it returns an empty array. If either argument is `NULL`, the function returns `NULL`. ## PostgreSQL jsonb_path_query_array() function example Let's explore some examples of using the `jsonb_path_query_array()` function ### 1) Basic PostgreSQL jsonb_path_query_array() function example The following example uses the `jsonb_path_query_array()` function to get the employee names as an array: ```sql SELECT jsonb_path_query_array( '{"employees": [{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]}', '$.employees[*].name' ); ``` Output: ```text jsonb_path_query_array ------------------------ ["Alice", "Bob"] (1 row) ``` In this example, the JSON path expression `$.employees[*].name` locates the value of the `name` key of all elements in the `employees` array. ### 2) Using jsonb_path_query_array() function with table data First, [create a new table](../postgresql-tutorial/postgresql-create-table) called `employees`: ```sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, data JSONB ); ``` In the `employees` table, the `data` column has the type of `JSONB`. Second, [insert some rows](../postgresql-tutorial/postgresql-insert-multiple-rows) into the `employees` table: ```sql INSERT INTO employees (data) VALUES ('{"name": "Alice", "age": 30, "pets": [{"type": "cat", "name": "Fluffy"}, {"type": "dog", "name": "Buddy"}]}'), ('{"name": "Bob", "age": 35, "pets": [{"type": "dog", "name": "Max"}]}'), ('{"name": "Charlie", "age": 40, "pets": [{"type": "rabbit", "name": "Snowball"}]}') RETURNING *; ``` Output: ```text id | data ----+------------------------------------------------------------------------------------------------------------- 1 | {"age": 30, "name": "Alice", "pets": [{"name": "Fluffy", "type": "cat"}, {"name": "Buddy", "type": "dog"}]} 2 | {"age": 35, "name": "Bob", "pets": [{"name": "Max", "type": "dog"}]} 3 | {"age": 40, "name": "Charlie", "pets": [{"name": "Snowball", "type": "rabbit"}]} (3 rows) ``` Third, use the `jsonb_path_query_array()` function to retrieve the pet names of employees as a JSON array: ```sql SELECT jsonb_path_query_array(data, '$.pets[*].name') AS employee_pet_names FROM employees; ``` Output: ```text employee_pet_names --------------------- ["Fluffy", "Buddy"] ["Max"] ["Snowball"] (3 rows) ``` ### 3) Handling missing paths If the specified path doesn't exist in the `JSONB` data, the `jsonb_path_query_array()` function returns an empty array. For example: ```sql SELECT jsonb_path_query_array(data, '$.address') FROM employees; ``` Output: ```text jsonb_path_query_array ------------------------ [] [] [] (3 rows) ``` In this example, the employee object doesn't have an `address` key, so the result is an empty array. ## Summary - Use the `jsonb_path_query_array()` function to query JSONB data using a JSON path and return matched elements as a JSON array. --- ## 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](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_extract_path) - [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_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)