The data in PostgreSQL is stored in the form of a table. The stored data can be of any valid data type including JSON. This data type stores the values as key-value pairs. PostgreSQL provides many functions to retrieve the data from JSON columns. One of the most significant functions used to retrieve nested values from JSON values using its path is the “json_extract_path()” function. The content of this post will be the json_extract_path() function and its functioning. So let’s get started with the post to learn more about this function.

What Does json_extract_path() Function Do in PostgreSQL?

The PostgreSQL JSON function named the “json_extract_path()” fetches the nest JSON value from the JSON data on the basis of the path specified. The basic syntax of this function is:

json_extract_path(json_value JSON, VARIADIC Path TEXT[])

In the above syntax:

● The “json_extract_path()” function takes in 2 parameters.

● The first parameter i.e. “json_value” is the JSON value from which we will extract the nested value.

● The second parameter i.e. “VARIADIC Path TEXT[]” specifies the variadic list which illustrates the path of the value to extract.

The function will return the JSON value pointed out by the specified path from the “json_value” and will return NULL if no valid path is found.

We will discuss the topic with the help of examples to bring more clarity.

Example 1: The json_extract_path() Function in PostgreSQL

Below is an example to show the working of the json_extract_path() function in PostgreSQL and how the values are retrieved from the JSON array at the specified path. Consider the following query:

SELECT
  json_extract_path('["John", "Alex", ["Cate", "Smith"]]', '0') AS value_1,
  json_extract_path('["John", "Alex", ["Cate", "Smith"]]', '1') AS value_2,
  json_extract_path('["John", "Alex", ["Cate", "Smith"]]', '2') AS value_3;

In the above queries:

● We have specified the JSON values as “["John", "Alex", ["Cate", "Smith"]]” and the second parameter is the path or index(in this case). So for the first query, the function json_extract_path() function extracts the value at the 0th index in the JSON value i.e. "John".

● For the second query, the function json_extract_path() function extracts the value at the index 1 in the JSON value i.e. "Alex".

● For the third query, the function json_extract_path() function extracts the value at the index 2 in the JSON value i.e. ["Cate", "Smith"] array.

The query will return these outputs. You can see the output below:

We have seen that the last query has returned a nested array at index 2. We will get the individual nested values of that JSON array as well. The query for this can be written as:

SELECT
 json_extract_path('["John", "Alex", ["Cate", "Smith"]]', '2','0') AS   nested_value_1,
 json_extract_path('["John", "Alex", ["Cate", "Smith"]]', '2','1') AS   nested_value_2;

By executing the query, we will get to know that the output will be as per expectations as it is returning the nested values. The output is given as:

So this is how we can get nested values from JSON nested arrays.

Let’s consider another example.

Example 2: JSON Object

This example will illustrate how we can get a value from the JSON object at a specific path. The example contains the information of a customer, it contains details about the customer id, products, and their quantity present in the cart. Consider the following query:

SELECT
  json_extract_path('{"customer_id": 1, "cart_products": {"apple": 2, "pear": 3}}', 'customer_id') AS customer_id,
  json_extract_path('{"customer_id": 1, "cart_products": {"apple": 2, "pear": 3}}', 'cart_products') AS cart_products;

The above query gives the expected results which can be shown in the following output:

We can see that the query has given the JSON values at the specified path. In this case, we can get the nested values as well, as we have done in the above example. Consider the following query for this purpose:

SELECT
  json_extract_path
  ('{"customer_id": 1, "cart_products": {"apple": 2, "pear": 3}}', 'cart_products','apple')
  AS customer_id,
  json_extract_path
  ('{"customer_id": 1, "cart_products": {"apple": 2, "pear": 3}}', 'cart_products','pear') 
  AS cart_products;

The query will give the nested values from the nested JSON array. The output will be:

This is how we can use the json_extract_path() function to fetch the JSON values from JSON objects.

Conclusion

The json_extract_path() function is a JSON function that is used to extract the values from the JSON data. The path of the values that are to be fetched is specified as an argument in the function. In this article, we have seen what a json_extract_path() function actually does in PostgreSQL and how we can get JSON values and nested JSON values using this function with the help of examples.