Oracle JSON_OBJECT() Function

Oracle JSON_OBJECT() is a built-in function that returns a JSON object containing all the key-value pairs specified by the arguments.

Oracle `JSON_OBJECT()` Syntax

Here is the syntax for the Oracle JSON_OBJECT() function:

JSON_OBJECT
  ( [ {[ KEY ] key_expr VALUE val_expr | expr [ ":" expr} [ FORMAT JSON ]
     ,]...
    [ JSON_on_null_clause ] [ JSON_returning_clause ]
    [ STRICT ]
    [ WITH UNIQUE KEYS ]  )

Parameters

key_expr

Required. Specifies the property key name using a case-sensitive text literal.

val_expr

Required. Specifies the value of the property. You can specify any expression that evaluates to a SQL numeric literal, text literal, date, or timestamp. Date and timestamp data types are printed as JSON strings in ISO date format in the generated JSON object or array. If expr evaluates to a numeric literal, the generated property value is a JSON numeric value; otherwise, the generated property value is a case-sensitive JSON string value enclosed in double quotes.

FORMAT JSON

Optional. You can specify FORMAT JSON to indicate that the input string is JSON and is therefore not quoted in the output.

JSON_on_null_clause

Optional. You can use this clause to specify the behavior of the function when expr evaluates to null. You can use the following two clauses:

NULL ON NULL - If specified, the function returns a JSON null value.
ABSENT ON NULL - If specified, the function omits the value from the JSON array. This is the default value.

JSON_returning_clause

Optional. You can use this clause to specify the type of the return value. Use one of the following values:

VARCHAR2, which specifies a size in bytes or characters. The default is bytes. If you omit this clause or specify a clause with no size value specified, JSON_ARRAY returns a character string of type VARCHAR2(4000). Note that when you specify the VARCHAR2 data type in other places in SQL, you need to specify a size. However, in the JSON_returning_clause, you can omit the size.
CLOB, to return a character large object containing single-byte or multibyte characters.
BLOB, to return a binary large object in the AL32UTF8 character set.

STRICT

Optional. Specify the STRICT clause to validate that the output of the JSON generation function is valid JSON. If the check fails, a syntax error is raised.

WITH UNIQUE KEYS

Optional. It guarantees that the generated JSON object has unique keys.

Return Value

The Oracle JSON_OBJECT() function returns a JSON object with members specified by the arguments as key-value pairs.

Oracle `JSON_OBJECT()` Examples

Here are some examples that demonstrate the usage of the Oracle JSON_OBJECT() function.

Basic Usage

The following statement demonstrates the basic usage of the Oracle JSON_OBJECT() function:

SELECT JSON_OBJECT(KEY 'name' VALUE 'Tim', KEY 'age' VALUE 20) Result
FROM dual;

输出：

RESULT
__________________________
{"name":"Tim","age":20}

You can use colons to separate JSON_OBJECT entries.

SELECT JSON_OBJECT('name' : 'Tim', 'age' : 20) Result
FROM dual;

输出：

RESULT
__________________________
{"name":"Tim","age":20}

`FORMAT JSON`

The following example takes a JSON array and a JSON object as input parameters:

SELECT JSON_OBJECT('a' : '[1,2]', 'b' : '{"x":1}') Result
FROM dual;

输出：

RESULT
________________________________
{"a":"[1,2]","b":"{\"x\":1}"}

You can see that they are both quoted, which is not what you want. You can solve this problem by using the FORMAT JSON clause:

SELECT JSON_OBJECT(
    'a' : '[1,2]' FORMAT JSON,
    'b' : '{"x":1}' FORMAT JSON) Result
FROM dual;

输出：

RESULT
__________________________
{"a":[1,2],"b":{"x":1}}

Converting Rows to JSON Objects

If you have a test table with the following rows:

SELECT * FROM test;

输出：

X
____
a
b

You can use a wildcard to convert each row in the table to a JSON object:

SELECT JSON_OBJECT(*) Result
FROM test;

输出：

RESULT
____________
{"X":"a"}
{"X":"b"}

Conclusion

Oracle’s JSON_OBJECT() is a built-in function that returns a JSON object containing all key-value pairs specified by the arguments.

Oracle JSON_OBJECT() Syntax