> 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 **Info:** The `jsonb_path_query_array()` function works the same across any PostgreSQL deployment, so you can apply what you learn here to Postgres anywhere you run it. If you're an enterprise looking for managed Postgres built for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers high performance, strong security, and native integration with the Lakehouse. If you're a developer or startup who needs to ship and scale fast, [Neon](https://neon.com) gives you the best Postgres platform to build on. **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/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/json-functions/json-extract) - [JSONB Operators](https://neon.com/postgresql/json-functions/jsonb-operators) - [jsonb_agg](https://neon.com/postgresql/json-functions/jsonb_agg) - [jsonb_array_elements](https://neon.com/postgresql/json-functions/jsonb_array_elements) - [jsonb_array_elements_text](https://neon.com/postgresql/json-functions/jsonb_array_elements_text) - [jsonb_array_length](https://neon.com/postgresql/json-functions/jsonb_array_length) - [jsonb_build_array](https://neon.com/postgresql/json-functions/jsonb_build_array) - [jsonb_build_object](https://neon.com/postgresql/json-functions/jsonb_build_object) - [jsonb_each](https://neon.com/postgresql/json-functions/jsonb_each) - [jsonb_each_text](https://neon.com/postgresql/json-functions/jsonb_each_text) - [jsonb_extract_path](https://neon.com/postgresql/json-functions/jsonb_extract_path) - [jsonb_extract_path_text](https://neon.com/postgresql/json-functions/jsonb_extract_path_text) - [jsonb_insert](https://neon.com/postgresql/json-functions/jsonb_insert) - [jsonb_object](https://neon.com/postgresql/json-functions/jsonb_object) - [jsonb_object_agg](https://neon.com/postgresql/json-functions/jsonb_object_agg) - [jsonb_object_keys](https://neon.com/postgresql/json-functions/jsonb_object_keys) - [JSON Path](https://neon.com/postgresql/json-functions/json-path) - [jsonb_path_exists](https://neon.com/postgresql/json-functions/jsonb_path_exists) - [jsonb_path_query](https://neon.com/postgresql/json-functions/jsonb_path_query) - [jsonb_path_query_first](https://neon.com/postgresql/json-functions/jsonb_path_query_first) - [jsonb_pretty](https://neon.com/postgresql/json-functions/jsonb_pretty) - [jsonb_set](https://neon.com/postgresql/json-functions/jsonb_set) - [jsonb_strip_nulls](https://neon.com/postgresql/json-functions/jsonb_strip_nulls) - [jsonb_to_record](https://neon.com/postgresql/json-functions/jsonb_to_record) - [jsonb_populate_record](https://neon.com/postgresql/json-functions/jsonb_populate_record) - [jsonb_populate_recordset](https://neon.com/postgresql/json-functions/jsonb_populate_recordset) - [jsonb_typeof](https://neon.com/postgresql/json-functions/jsonb_typeof) - [row_to_json](https://neon.com/postgresql/json-functions/row_to_json) - [to_jsonb](https://neon.com/postgresql/json-functions/to_jsonb)