Postgres json_extract_path() function

You can use the json_extract_path function to extract the value at a specified path within a JSON document. This approach is performant compared to querying the entire JSON payload and processing it on the application side. It is particularly useful when dealing with nested JSON structures.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Function signature

json_extract_path(from_json JSON, VARIADIC path_elems TEXT[]) -> JSON

Example usage

To illustrate the json_extract_path function in Postgres, let's consider a scenario where we have a table storing information about books. Each book has a JSON column containing details such as title, author, and publication year. You can create the book table using the SQL statements shown below.

books

CREATE TABLE books (
 id INT,
 info JSON
);

INSERT INTO books (id, info) VALUES
 (1, '{"title": "The Catcher in the Rye", "author": "J.D. Salinger", "year": 1951}'),
 (2, '{"title": "To Kill a Mockingbird", "author": "Harper Lee", "year": 1960}'),
 (3, '{"title": "1984", "author": "George Orwell", "year": 1949}');

| id | info                                                                         |
|----|------------------------------------------------------------------------------|
| 1  | {"title": "The Catcher in the Rye", "author": "J.D. Salinger", "year": 1951} |
| 2  | {"title": "To Kill a Mockingbird", "author": "Harper Lee", "year": 1960}     |
| 3  | {"title": "1984", "author": "George Orwell", "year": 1949}                   |

Now, let's use the json_extract_path function to extract the title and author of each book:

SELECT id,
      json_extract_path(info, 'title') as title,
      json_extract_path(info, 'author') as author
FROM books;

This query returns the following values:

| id | title                    | author           |
|----|--------------------------|------------------|
| 1  | "The Catcher in the Rye" | "J.D. Salinger"  |
| 2  | "To Kill a Mockingbird"  | "Harper Lee"     |
| 3  | "1984"                   | "George Orwell"  |

Advanced examples

Consider a products table that stores information about products in an e-commerce system. The table schema and data are outlined below.

products

CREATE TABLE products (
 id INT,
 attributes JSON
);

INSERT INTO products (id, attributes) VALUES
 (1, '{"name": "Laptop", "specs": {"brand": "Dell", "RAM": "16GB", "storage": {"type": "SSD", "capacity": "512GB"}}, "tags": ["pc"]}'),
 (2, '{"name": "Smartphone", "specs": {"brand": "Google", "RAM": "8GB", "storage": {"type": "UFS", "capacity": "256GB"}}, "tags": ["android",
 "pixel"]}'),
 (3, '{"name": "Smartphone", "specs": {"brand": "Apple", "RAM": "8GB", "storage": {"type": "UFS", "capacity": "128GB"}}, "tags": ["ios", "iphone"]}');

| id     | attributes                                                                                                                                        |
|--------|---------------------------------------------------------------------------------------------------------------------------------------------------|
| 1      | {"name": "Laptop", "specs": {"brand": "Dell", "RAM": "16GB", "storage": {"type": "SSD", "capacity": "512GB"}}, "tags": ["pc"]}                    |
| 2      | {"name": "Smartphone", "specs": {"brand": "Google", "RAM": "8GB", "storage": {"type": "UFS", "capacity": "256GB"}}, "tags": ["android", "pixel"]} |
| 3      | {"name": "Smartphone", "specs": {"brand": "Apple", "RAM": "8GB", "storage": {"type": "UFS", "capacity": "128GB"}}, "tags": ["ios", "iphone"]}     |

Extract from nested JSON objects with `json_extract_path`

Let's use json_extract_path to retrieve information about the storage type and capacity for each product, demonstrating how to extract values from a nested JSON object.

SELECT
 id,
 json_extract_path(attributes, 'specs', 'storage', 'type') as storage_type,
 json_extract_path(attributes, 'specs', 'storage', 'capacity') as storage_capacity
FROM products;

This query returns the following values:

| id | storage_type | storage_capacity |
|----|--------------|------------------|
| 1  | "SSD"        | "512GB"          |
| 2  | "UFS"        | "256GB"          |
| 3  | "UFS"        | "128GB"          |

Extract from array with `json_extract_path`

Now, let's use json_extract_path to extract information about the associated tags as well, demonstrating how to extract values from a JSON array.

SELECT
 id,
 json_extract_path(attributes, 'specs', 'storage', 'type') as storage_type,
 json_extract_path(attributes, 'specs', 'storage', 'capacity') as storage_capacity,
 json_extract_path(attributes, 'tags', '0') as first_tag,
 json_extract_path(attributes, 'tags', '1') as second_tag
FROM products;

This query returns the following values:

| id | storage_type | storage_capacity | first_tag | second_tag |
|----|--------------|------------------|-----------|------------|
| 1  | "SSD"        | "512GB"          | "pc"      |  null      |
| 2  | "UFS"        | "256GB"          | "android" | "pixel"    |
| 3  | "UFS"        | "128GB"          | "ios"     | "iphone"   |

Use `json_extract_path` in Joins

Let's say you have two tables, employees and departments, and the employees table has a JSON column named details that contains information about each employee's department. You want to join these tables based on the department information stored in the JSON column. The table schemas and data used in this example are shown below.

departments

CREATE TABLE departments (
   department_id SERIAL PRIMARY KEY,
   department_name VARCHAR(255)
);

INSERT INTO departments (department_name) VALUES
   ('IT'),
   ('HR'),
   ('Marketing');

| department_id | department_name  |
|---------------|------------------|
|             1 | IT               |
|             2 | HR               |
|             3 | Marketing        |

employees

CREATE TABLE employees (
   employee_id SERIAL PRIMARY KEY,
   employee_name VARCHAR(255),
   details JSON
);

INSERT INTO employees (employee_name, details) VALUES
   ('John Doe', '{"department": "IT"}'),
   ('Jane Smith', '{"department": "HR"}'),
   ('Bob Johnson', '{"department": "Marketing"}');

| employee_id | employee_name |           details           |
|-------------|---------------|-----------------------------|
|           1 | John Doe      | {"department": "IT"}        |
|           2 | Jane Smith    | {"department": "HR"}        |
|           3 | Bob Johnson   | {"department": "Marketing"} |

You can use JOIN with json_extract_path to retrieve information:

SELECT
   employees.employee_name,
   departments.department_name
FROM
   employees
JOIN
   departments ON TRIM(BOTH '"' FROM json_extract_path(employees.details, 'department')::TEXT) = departments.department_name;

This query returns the following values:

| employee_name | department_name  |
|---------------|------------------|
| John Doe      | IT               |
| Jane Smith    | HR               |
| Bob Johnson   | Marketing        |

The json_extract_path function extracts the value of the department key from the JSON column in the employees table. The JOIN is then performed based on matching department names.

Additional considerations

Performance and Indexing

The json_extract_path function performs well when extracting data from JSON documents, especially compared to extracting data in application code. It allows performing the extraction directly in the database, avoiding transferring entire JSON documents to the application.

However, performance can degrade with highly nested JSON structures and very long text strings. In those cases, using the binary JSONB data type and the jsonb_extract_path function will likely offer better performance.

Indexing JSON documents can also significantly improve json_extract_path query performance when filtering data based on values extracted from JSON.

Alternative functions

json_extract_path_text - The regular json_extract_path function returns the extracted value as a JSON object or array, preserving its JSON structure, whereas the alternative json_extract_path_text function returns the extracted value as a plain text string, casting any JSON objects or arrays to their string representations.

Use the regular json_extract_path function when you need to apply JSON-specific functions or operators to the extracted value, requiring JSON data types. The alternative json_extract_path_text function is preferable if you need to work directly with the extracted value as a string, for text processing, concatenation, or comparison.
jsonb_extract_path - The jsonb_extract_path function works with the jsonb data type, which offers a binary representation of JSON data. This alternative function is generally faster than json_extract_path for most operations, as it's optimized for the binary jsonb format. This difference in performance is often more pronounced with larger JSON structures and frequent path extractions.

Resources

Need help?

Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.

Postgres json_extract_path() function

Try it on Neon!

Function signature

Example usage

Advanced examples

Extract from nested JSON objects with json_extract_path

Extract from array with json_extract_path

Use json_extract_path in Joins

Additional considerations

Performance and Indexing

Alternative functions

Resources

Need help?

Extract from nested JSON objects with `json_extract_path`

Extract from array with `json_extract_path`

Use `json_extract_path` in Joins