Oracle PL/SQL JSON_OBJECT is a built-in function in Oracle Database 12c and above that generates a JSON object from a set of key-value pairs. This function allows developers to easily create JSON objects within PL/SQL code, making it a powerful tool for building applications that interact with JSON data.
Syntax
The syntax for the JSON_OBJECT function is straightforward. It takes in a list of key-value pairs, where the keys are strings and the values can be any valid PL/SQL expression. For example, the following code generates a JSON object with two key-value pairs:
JSON_OBJECT('name' VALUE 'John', 'age' VALUE 30)
This code generates a JSON object with the keys “name” and “age”, and their respective values “John” and 30.
Example
The JSON_OBJECT function also supports nested objects and arrays. For example, the following code generates a JSON object with a nested object:
SELECT JSON_OBJECT(
'name' VALUE 'John',
'address' VALUE JSON_OBJECT(
'street' VALUE '123 Main St',
'city' VALUE 'Anytown',
'state' VALUE 'CA'
)
) FROM DUAL;
This code generates a JSON object with two keys, “name” and “address”. The “address” key has a nested object with three keys: “street”, “city”, and “state”.
{"name":"John","address":{"street":"123 Main St","city":"Anytown","state":"CA"}}
In addition to the JSON_OBJECT function, Oracle also provides a number of other functions for working with JSON data in PL/SQL, such as JSON_ARRAY, JSON_QUERY, and JSON_TABLE. These functions allow developers to manipulate and query JSON data within their PL/SQL code, making it easier to integrate JSON data into their applications.
In summary, Oracle PL/SQL JSON_OBJECT is a powerful tool for generating JSON objects within PL/SQL code. With its support for nested objects and arrays, developers can easily create complex JSON structures within their applications.
