PL/SQL JSON_QUERY – Oracle PL/SQL Tutorial

JSON_QUERY is a function in Oracle PL/SQL that is used to extract a JSON value from a JSON document. It takes two arguments – the JSON document and a JSON path expression that specifies the location of the value to be extracted.

The JSON document can be a JSON object, JSON array, or a scalar JSON value. The JSON path expression is a string that specifies the location of the value to be extracted using a syntax similar to XPath.

Here is the basic syntax of the JSON_QUERY function:

JSON_QUERY(json_document, json_path_expression)

The json_document is the JSON data to be queried and the json_path_expression is the JSON path to the data to be extracted.

For example, consider the following JSON document:

{
   "employees":[
      {"firstName":"John", "lastName":"Doe", "age":28},
      {"firstName":"Jane", "lastName":"Doe", "age":24},
      {"firstName":"Jim", "lastName":"Smith", "age":32}
   ]
}

To extract the first name of the first employee, you can use the following query:

SELECT JSON_QUERY(json_string, '$.employees[0].firstName') AS first_name
FROM json_table(json_string, '$' COLUMNS (json_string VARCHAR2(4000) PATH '$')) t;

Here, the path expression $ refers to the root of the JSON document, employees[0] refers to the first element in the employees array, and firstName refers to the firstName field.

The output of the above query will be:

John

If the JSON path expression does not match any data in the JSON document, the JSON_QUERY function will return a null value.

In addition to extracting a single value, JSON_QUERY can also extract multiple values by using wildcards and filters in the JSON path expression. It also supports various data types such as strings, numbers, and Boolean values.

Overall, JSON_QUERY is a useful function in Oracle PL/SQL for working with JSON data, allowing you to extract specific values from complex JSON documents easily.

Learn PL/SQL