JSON data type is also used to store data in key-value pairs. We can fetch the JSON data using some built-in functions that are being provided by PostgreSQL. In this article, we will be drawing a comparison between the two significant functions used to extract the JSON nested value present on a specific path. These functions are; json_extract_path() Vs json_extract_path_text() function. Let’s see what they do and how they are different.

PostgreSQL json_extract_path() Function

This PostgreSQL JSON function, the json_extract_path() function, retrieves the nest JSON value from the JSON value present on the path that is specified in the function. 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 arguments.

● The “json_value” specifies the JSON value from which the nested value will be extracted.

● The “VARIADIC Path TEXT[]” is the list that gives us the path to the value we want to extract.

The json_extract_path() function returns the nested “JSON value” specified by the path from the “json_value”. If the path specified is not valid, the function gives NULL.

PostgreSQL json_extract_path_text() Function

This PostgreSQL JSON function, the “json_extract_path_text()” function, retrieves the nest JSON value from the JSON value present on the path that is specified in the function and returns the result in the form of text. The basic syntax of this function is:

json_extract_path_text(json_value JSON, VARIADIC Path TEXT[])

In the above syntax:

● The “json_extract_path_text()” function takes in 2 arguments.

● The “json_value” specifies the JSON value from which the nested value will be extracted.

● The “VARIADIC Path TEXT[]” is the list that gives us the path to the value we want to extract.

The json_extract_path_text() function returns the nested JSON value specified by the path from the “json_value” as a text. This simply implies that the return type of the json_extract_path_text() function is TEXT. If no valid path is found, the function returns NULL.

PostgreSQL json_extract_path() Vs json_extract_path_text() - What's the Difference?

In the above sections, the working of both functions is given that is they their working is almost the same. Both functions refer to the specified path in the specified JSON value. But there is a distinction in both functions. The difference is in the return data type of both of the functions i.e.

● The PostgreSQL json_extract_path() function returns the JSON value of the JSON data type.

● While the PostgreSQL json_extract_path_text() function returns the value in the TEXT data type.

Let’s implement both functions to see the difference.

Example : The json_extract_path() Vs json_extract_path_text() Function in PostgreSQL

Consider an example that illustrates the working and difference between the json_extract_path() function and json_extract_path_text() function in PostgreSQL and how the values are retrieved from the JSON array at the specified path. Consider the query:

SELECT
  json_extract_path('["Peter", "Katherine", ["Oliver", "Mendis"]]', '0')
  AS val_extract_path,
  json_extract_path_text('["Peter", "Katherine", ["Oliver", "Mendis"]]', '0')
  AS val_extract_path_text;

By implementing these queries we will get the value at index 0 in JSON value given as the first parameter. Both queries will return the same output but the data type of both will be different. The output of the above queries is:

We can see that the returned data type of both functions is different. The json_extract_path() function returns the value in JSON data type while the json_extract_path_text() function returns the value in TEXT data type. This is the difference between both functions.

Conclusion

The PostgreSQL json_extract_path() function and json_extract_path_text() function both are JSON functions that are used to extract the nested values from JSON values specified at a certain path. That JSON value and the path both of these are provided to the functions as arguments. These functions return the same value but the difference between both these functions is in the returned data type. The json_extract_path() function returns the value in JSON data type while the json_extract_path_text() function returns the value in TEXT data type. This article covered the basic concept of both of these functions and their distinctions and differences.