Purpose
JSON_OBJECT() converts a series of key-value pairs passed by using an SQL statement to a JSON object that contains all the input key-value pairs.
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]);
Parameters
The parameters in the syntax are described as follows:
Input forms of key-value pairs:
- 'key_name' : value_expression
- 'key_name' : "value_string"
- 'key_name' VALUE value_expression
- 'key_name' VALUE 'value_string'
- column_name_x
- table_alias.*
In the first four forms, key-value pairs are defined in
KEY : VALUEformat. In the fifth form, only one column name is specified, the column name is the key name, and the column content is the value. In the sixth form, the wildcard character * is used, which indicates that all columns of the table are input. This is equivalent to writing all columns of the table in the fifth form.FORMAT JSON: specifies that the return value of the expression is JSON data and no\"will be added to the output result.NULL|ABSENT ON NULL: This clause takes effect only when the calculation result of the expression isNULL.NULL ON NULL: specifies that a JSONNULLvalue will be used as the value of the given key.ABSENT ON NULL: specifies that the expression will be ignored.
WITH UNIQUE KEYS: specifies that the result cannot contain duplicate keys.STRICT: checks whether the output result is in JSON format.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: specifies the return value type.VARCHAR2[size]specifies the length of the return value.
Examples
# Use input arguments (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;
# Use the input argument (f) and the wildcard character.
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
}
# Use input arguments (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
# Use the FORMAT JSON clause.
SELECT JSON_OBJECT ('name' value 'Foo') FROM DUAL;
+-------------------------------+
| JSON_OBJECT('NAME'VALUE'FOO') |
+-------------------------------+
| {"name":"Foo"} |
+-------------------------------+
# Use input arguments (a) and (b) and keywords KEY and VALUE.
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"}
# Use input arguments (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"
}