PL/SQL JSON_VALUE – Oracle PL/SQL Tutorial

Oracle PL/SQL is a powerful programming language used for managing and querying data stored in Oracle databases. One of the features of PL/SQL is the ability to handle JSON data using the JSON_VALUE function.

In PL/SQL, JSON data can be parsed and manipulated using a variety of functions and methods, including the JSON_VALUE function. The JSON_VALUE function is used to extract a specific value from a JSON document.

The syntax for the JSON_VALUE function is as follows:

JSON_VALUE(json_string, path)

Here, json_string is the JSON document from which the value is to be extracted, and path is the path to the desired value within the JSON document. The path argument is specified using dot notation, where each dot separates the names of nested objects and arrays.

The JSON_VALUE function in PL/SQL allows you to extract a specific value from a JSON document. It takes two arguments: the JSON document, and a JSON path expression that identifies the value you want to extract.

Here is an example of using JSON_VALUE in PL/SQL:

DECLARE
   json_text CLOB := '{"name": "John", "age": 30, "city": "New York"}';
   name_value VARCHAR2(20);
BEGIN
   name_value := JSON_VALUE(json_text, '$.name');
   DBMS_OUTPUT.PUT_LINE(name_value);
END;

In this example, we have a JSON document stored in a CLOB variable called json_text. We want to extract the value of the name property from the JSON document, so we use the JSON path expression $.name.

The result of the JSON_VALUE function is stored in the name_value variable, which is then printed to the console using the DBMS_OUTPUT.PUT_LINE function.

The output of this code will be:

John

In addition to extracting scalar values from a JSON document, JSON_VALUE can also be used to extract arrays and objects. For example, if we have a JSON array of numbers:

DECLARE
   json_text CLOB := '[1, 2, 3, 4, 5]';
   third_value NUMBER;
BEGIN
   third_value := JSON_VALUE(json_text, '$[2]');
   DBMS_OUTPUT.PUT_LINE(third_value);
END;

The JSON path expression $[2] will extract the third value from the array (remember that arrays in JSON are zero-indexed). The output of this code will be:

In summary, JSON_VALUE is a powerful function in Oracle PL/SQL that allows you to extract specific values from a JSON document using JSON path expressions. This feature makes it easy to work with JSON data in Oracle databases and integrate it with other PL/SQL functionality.

Learn PL/SQL