JSON Constructors
JSON SQL constructors generate JSON objects from table rows.
JSON_OBJECT
The JSON_OBJECT constructor creates a JSON object from relational data.
JSON_OBJECT (
[JSON key and value [{,JSON key and value}...]
[NULL|ABSENT ON NULL]
[WITH|WITHOUT UNIQUE [KEYS]]
[RETURNING datatype]
)
where:
JSON key and value
Specifies the values in the table that you want to use for key and value in the key:value pair, using the following form:
[KEY] name VALUE value | name:value
The value used for name must be a string. A maximum of 100 key:value pairs is allowed. The KEY keyword is optional and has no affect on the returned object.
NULL ON NULL | ABSENT ON NULL
Specifies how nulls are handled. NULL ON NULL uses the string “null” if the input value is null. ABSENT ON NULL omits this key:value pair from the returned object. Default is NULL ON NULL.
WITH UNIQUE [KEYS] | WITHOUT UNIQUE [KEYS]
WITH UNIQUE returns an error if the object created by JSON_OBJECT has duplicate keys in its key:value pairs. For example, {"Name":"Frank","Name":"Bob"} generates an error.
WITHOUT UNIQUE does not enforce unique keys but does not require them. Default is WITHOUT UNIQUE.
RETURNING datatype
Specifies the data type of the result. Any string data type, including a long type, is permitted. Default is varchar(4000).
JSON_OBJECT is typically used in a select. For example:
Assume a table emp that contains the following rows:
+-------------+----------+-------+
|row |name | unit |
+-------------+----------+-------+
| 1|Frank | 1A |
| 2|Steve | 2A |
| 1|Bob | 1B |
| 2|null | 2B |
+-------------+----------+-------+
SELECT JSON_OBJECT (KEY 'id' VALUE emp.row,
KEY 'name' VALUE emp.name)
FROM emp;
returns four JSON objects:
{"id": 1, "name": "Frank"}
{"id": 2, "name": "Steve"}
{"id": 1, "name": "Bob"}
{"id": 2, "name": null}
If ABSENT ON NULL was specified in the above query, the row corresponding to unit 2B would be omitted and the result would only contain three rows.
JSON_OBJECTAGG
JSON_OBJECTAGG returns a JSON object where the key:value pairs of the object are an aggregate based on rows of a table.
JSON_OBJECTAGG (
[JSON key and value [{,JSON key and value}...]
[NULL|ABSENT ON NULL]
[WITH|WITHOUT UNIQUE [KEYS]]
[RETURNING datatype]
)
where the arguments have the same meaning as described in
JSON_OBJECT.
Example:
SELECT JSON_OBJECTAGG (name VALUE row ABSENT ON NULL) FROM emp;
returns the JSON object {"Frank":1, "Steve":2, "Bob":1}.
SELECT JSON_OBJECTAGG (name VALUE row NULL ON NULL) FROM emp;
generates an error because the last name is NULL, which is invalid.
JSON_OBJECTAGG can be used in GROUP BY clauses.
SELECT emp.row,
JSON_OBJECTAGG (name VALUE emp.unit ABSENT ON NULL) AS units
FROM emp GROUP BY emp.row
returns:
Row units
1 {"Frank":"1A","Bob":"1B"}
2 {"Steve":"2A"}
JSON_ARRAY
JSON_ARRAY constructs a JSON array from a list of data.
JSON_ARRAY (
[value [{,value}...]]
[NULL|ABSENT ON NULL]
[RETURNING datatype]
)
value
Specifies one or more constants, table columns, or expressions. Only 100 values can be used in a single JSON_ARRAY statement.
NULL ON NULL | ABSENT ON NULL
Specifies how nulls are handled. NULL ON NULL inserts nulls as SQL nulls. ABSENT ON NULL omits null values. Default is ABSENT ON NULL.
RETURNING datatype
Specifies the data type of the result. Datatype can be any string data type, including long types. Default is varchar(4000).
Example:
SELECT JSON_ARRAY ('test', emp.unit, emp.name) FROM emp WHERE "row" = 1
returns:
["test"“, "1A", "Frank"]
["test", "1B", "Steve"]
JSON_ARRAYAGG
JSON_ARRAYAGG constructs a JSON array as an aggregation of values from table rows.
JSON_ARRAYAGG (
value
ORDER BY sort_list
[NULL | ABSENT ON NULL]
[RETURNING datatype]
)
value
Specifies a constant, table column, or expression to be aggregated.
ORDER BY
Causes the items in the aggregate to be ordered in the output array. If value is a JSON_OBJECT or JSON_ARRAY constructor, the ORDER BY columns can include columns used by the JSON_OBJECT or JSON_ARRAY queries.
NULL ON NULL | ABSENT ON NULL
Specifies how nulls are handled. NULL ON NULL inserts nulls as SQL nulls. ABSENT ON NULL omits null values. Default is ABSENT ON NULL.
RETURNING datatype
Specifies the data type of the result. Datatype can be any string data type, including long types. Default is varchar(4000).
Examples
The query:
SELECT JSON_ARRAYAGG (unit) FROM emp;
returns: ["1A", "2A", "2A", "2B"]
SELECT JSON_OBJECT (
'row':emp.row,
'units':JSON_ARRAYAGG (
JSON_OBJECTAGG (emp.name VALUE emp.unit ABSENT ON NULL))
)
FROM emp
GROUP BY emp.row
returns two rows:
{"row" : 1, "units" : [{"Frank":"1A", "Bob:"1B"}]}
{"row" : 2, "units" : [{"Steve":"2A"}]}
The query:
SELECT JSON_OBJECT (
'row':emp.row,
'units':JSON_ARRAYAGG (
JSON_OBJECT (emp.name VALUE emp.unit ABSENT ON NULL) ORDER BY emp.unit DESC)
)
FROM emp
GROUP BY emp.row
returns two rows:
{"row" : 1, "units" : [{"Bob:"1B"},{"Frank":"1A"}]}
{"row" : 2, "units" : [{}, {"Steve":"2A"}]}
How JSON Dates Are Handled
All datetime data is converted to string.
The string output format is UTC (Z) based for a TIMESTAMP WITH any time zone:
YYYY-MM-DDThh:mm:ss.s[s[s[s[s[s]]]]Z
The output format for a TIMESTAMP WITHOUT TIME ZONE omits the Z:
YYYY-MM-DDThh:mm:ss.s[s[s[s[s[s]]]]
For example, running JSON_OBJECT() on an ingresdate column produces the following output:
+----------------------------------------------+
|json |
+----------------------------------------------+
|{"ingresdate col":"2018-12-14T20:56:48Z"} |
|{"ingresdate col":"5 hrs"} |
|{"ingresdate col":"5 hrs"} |
|{"ingresdate col":"1 days"} |
|{"ingresdate col":"2540 days"} |
|{"ingresdate col":"5 hrs"} |
|{"ingresdate col":"5 hrs"} |
+----------------------------------------------+