Purpose
The JSON_OBJECT() function converts a series of key-value pairs from SQL input into a JSON object containing all the key-value pairs provided.
Syntax
JSON_OBJECT([KEY]'key_name': value_expr FORMAT JSON,
'key_name' VALUE value_expr
[ABSENT|NULL ON NULL,]
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[STRICT,]
[WITH UNIQUE KEYS]);
Syntax
The syntax of the JSON_OBJECT() function is as follows:
- The input for key-value pairs can be in the following formats:
- 'key_name' : value_expression
- 'key_name' : "value_string"
- 'key_name' VALUE value_expression
- 'key_name' VALUE 'value_string'
- column_name_x
- table_alias.*
The first four formats indicate that key-value pairs are defined using KEY : VALUE. The fifth format specifies a single column name as the key, with the column content as the value. The sixth format uses the wildcard "*" to represent all columns of the table, equivalent to specifying each column using the fifth format.
FORMAT JSON: Indicates that the generated value is JSON data, so there will be no quotes ('"') in the output.NULL|ABSENT ON NULL: Only effective when the parameter expression evaluates toNULL.NULL ON NULL: Uses the JSONNULLvalue as the value for the specified key.ABSENT ON NULL: Ignores the parameter.
WITH UNIQUE KEYS: Ensures that the result does not contain duplicate keys.STRICT: Validates that the output is valid JSON data.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: Specifies the return value type, whereVARCHAR2[size]can limit the return value length.
Examples
# Scenario (a) and (b)
SELECT JSON_OBJECT(
'name' : first_name || ' ' || last_name,
'email' : email,
'phone' : phone_number,
'hire_date' : hire_date
)
FROM employees
WHERE employee_id = 140;
# Scenario (f) with wildcard
SELECT JSON_OBJECT(*) FROM employees WHERE employee_id = 140;
---------------------------------------------------------------------
{
"EMPLOYEE_ID":140,
"FIRST_NAME":"Joshua",
"LAST_NAME":"Patel",
"EMAIL":"JPATEL",
"PHONE_NUMBER":"650.121.1834",
"HIRE_DATE":"2006-04-06T00:00:00",
"JOB_ID":"ST_CLERK",
"SALARY":2500,
"COMMISSION_PCT":null,
"MANAGER_ID":123,"DEPARTMENT_ID":50
}
# Scenario (c) and (d)
SELECT JSON_OBJECT('NAME' VALUE first_name, d.*)
FROM employees e, departments d
WHERE e.department_id = d.department_id AND e.employee_id =140
# Using the FORMAT JSON clause
SELECT JSON_OBJECT ('name' value 'Foo') FROM DUAL;
+-------------------------------+
| JSON_OBJECT('NAME'VALUE'FOO') |
+-------------------------------+
| {"name":"Foo"} |
+-------------------------------+
# Scenarios (a) and (b) using the KEY VALUE clause
SELECT JSON_OBJECT (
KEY 'deptno' VALUE d.department_id,
KEY 'deptname' VALUE d.department_name
) "Department Objects"
FROM departments d
ORDER BY d.department_id;
Department Objects
----------------------------------------
{"deptno":10,"deptname":"Administration"}
{"deptno":20,"deptname":"Marketing"}
{"deptno":30,"deptname":"Purchasing"}
{"deptno":40,"deptname":"Human Resources"}
{"deptno":50,"deptname":"Shipping"}
# Scenarios (1) and (2)
SELECT JSON_OBJECT(
'first_name' VALUE first_name,
'last_name' VALUE last_name,
'email' VALUE email,
'hire_date' VALUE hire_date
)
FROM employees
WHERE employee_id = 140;
---------------------------------------------------------------------------
{
"first_name":"Joshua",
"last_name":"Patel",
"email":"JPATEL",
"hire_date":"2006-04-06T00:00:00"
}