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 : VALUE format. 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 parameters (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 parameter (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 parameters (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 parameters (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 parameters (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"
}