PL/SQL JSON_ARRAY - Oracle PL/SQL Tutorial

In Oracle PL/SQL, the JSON_ARRAY function is used to create a JSON array. It takes zero or more arguments as input and returns a JSON array that contains those values.

The syntax for the JSON_ARRAY function is as follows:

JSON_ARRAY(value_1, value_2, ..., value_n)

Each value can be of any data type supported by Oracle PL/SQL, including strings, numbers, booleans, nulls, and dates.

For example, to create a JSON array of numbers, you can use the following code:

SELECT JSON_ARRAY(1, 2, 3, 4, 5) as json_arr FROM dual;

This will return the following JSON array:

[1, 2, 3, 4, 5]

To create a JSON array of strings, you can use the following code:

SELECT JSON_ARRAY('apple', 'banana', 'cherry', 'durian') as json_arr FROM dual;

This will return the following JSON array:

["apple", "banana", "cherry", "durian"]

You can also use the JSON_ARRAY function with other JSON functions in Oracle PL/SQL to create more complex JSON structures. For example, you can use the JSON_OBJECT function to create a JSON object and then use the JSON_ARRAY function to create an array of those objects:

SELECT JSON_ARRAY(
         JSON_OBJECT('name' VALUE 'John', 'age' VALUE 25),
         JSON_OBJECT('name' VALUE 'Mary', 'age' VALUE 30),
         JSON_OBJECT('name' VALUE 'Bob', 'age' VALUE 35)
       ) as json_arr
FROM dual;

This will return the following JSON array:

[{"name":"John","age":25},{"name":"Mary","age":30},{"name":"Bob","age":35}]

In summary, the JSON_ARRAY function in Oracle PL/SQL is used to create a JSON array from a list of values. It is a simple and powerful tool for generating JSON data structures in your database applications.

Learn PL/SQL