Purpose
JSON_OBJECTAGG() constructs a JSON object from the input key-value pairs and returns the object. This function treats input arguments as key-value pairs, where key and value are column expressions.
Syntax
JSON_OBJECTAGG([KEY] key_expr [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:
WITH UNIQUE KEYS: specifies that the result cannot contain duplicate keys.NULL|ABSENT ON NULL: This clause takes effect only when the calculation result of the expression isNULL.NULL ON NULL: specifies that JSONNULLvalues will be used as elements of the current array.ABSENT ON NULL: specifies to ignore the array element.
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 default arguments.
CREATE TABLE t (a int, k VARCHAR(10), v VARCHAR(10));
INSERT INTO t VALUES (1, 'key1', '"alfa"');
INSERT INTO t VALUES (1, 'key3', 'null');
INSERT INTO t VALUES (1, 'key4', 'false');
INSERT INTO t VALUES (2, 'key6', '124');
obclient> SELECT a, JSON_OBJECTAGG(k VALUE v) FROM t GROUP BY a;
+------+--------------------------------------------------+
| A | JSON_OBJECTAGG(KVALUEV) |
+------+--------------------------------------------------+
| 1 | {"key1":"\"alfa\"","key3":"null","key4":"false"} |
| 2 | {"key6":"124"} |
+------+--------------------------------------------------+
# Use the FORMAT JSON clause.
obclient> SELECT a, JSON_OBJECTAGG(k VALUE v FORMAT JSON) FROM t GROUP BY a;
+------+------------------------------------------+
| A | JSON_OBJECTAGG(KVALUEVFORMATJSON) |
+------+------------------------------------------+
| 1 | {"key1":"alfa","key3":null,"key4":false} |
| 2 | {"key6":124} |
+------+------------------------------------------+
# Use the NULL|ABSENT ON NULL clause.
obclient> INSERT INTO t VALUES (2, 'key7', null);
obclient> SELECT a, JSON_OBJECTAGG(k VALUE v NULL ON NULL) FROM t GROUP BY a;
+------+--------------------------------------------------+
| A | JSON_OBJECTAGG(KVALUEVNULLONNULL) |
+------+--------------------------------------------------+
| 1 | {"key1":"\"alfa\"","key3":"null","key4":"false"} |
| 2 | {"key6":"124","key7":"null"} |
+------+--------------------------------------------------+
obclient> SELECT a, JSON_OBJECTAGG(k VALUE v ABSENT ON NULL) FROM t GROUP BY a;
+------+--------------------------------------------------+
| A | JSON_OBJECTAGG(KVALUEVABSENTONNULL) |
+------+--------------------------------------------------+
| 1 | {"key1":"\"alfa\"","key3":"null","key4":"false"} |
| 2 | {"key6":"124"} |
+------+--------------------------------------------------+
# Use the RETURNING clause.
obclient> SELECT a, JSON_OBJECTAGG(k VALUE v returning varchar2) FROM t GROUP BY a;
+------+--------------------------------------------------+
| A | JSON_OBJECTAGG(KVALUEVRETURNINGVARCHAR2) |
+------+--------------------------------------------------+
| 1 | {"key1":"\"alfa\"","key3":"null","key4":"false"} |
| 2 | {"key6":"124"} |
+------+--------------------------------------------------+
2 rows in set