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 described as follows:
- The input of key-value pairs can be in the following forms:
- '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 forms indicate that the key-value pairs are defined using KEY : VALUE. In the fifth form, only the column name is provided, where the column name serves as the key and the column content as the value. The sixth form uses the wildcard "*" to represent all columns of the table, effectively including all columns as if they were specified using the fifth form.
FORMAT JSON: Declares that the generated value is JSON data, so no quotes (""") will be present 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 given key.ABSENT ON NULL: Ignores the parameter.
WITH UNIQUE KEYS: Restricts the result to contain no 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 where the input is (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 where the input is (f) and the wildcard is used
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 where the input is (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 keyword
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"
}