JSON_ARRAY

MySQL

JSON_ARRAY([value[, value] …])

• value: The value parameter refers to the value you want to include in the JSON array. This could be an integer, a string, a boolean, another JSON array, or a JSON object. These values can be obtained from various data types in SQL, including character strings, numeric data, date-time data, and binary data. The order of these values in the array corresponds directly to the order in which they are inserted in the array.
• value…: The ellipsis indicates that multiple values can be provided to the function, each separated by a comma. Like the first value, subsequent values will be included in the JSON array in the order they are provided. If no values are provided, the function returns an empty JSON array.

Example

SELECT JSON_ARRAY('foo', 'bar', 'baz');

Output

["foo", "bar", "baz"]

Explanation

In the given example, the JSON_ARRAY() function in MySQL is used to create a JSON array containing 3 elements: ‘foo’, ‘bar’, ‘baz’. The function returns this as a single JSON array.

PostgreSQL

JSON_ARRAY(json_element[, json_element]…)

• json_element: Represents the elements that will be included in the JSON array. This can be any SQL value, including scalar values (numbers, strings, booleans) or structured values (arrays and maps). Multiple json_elements can be included, separated by commas.
• …: An ellipsis in function documentation like JSON_ARRAY(json_element[, json_element]…) refers to the parameter’s repeatability. This means that the json_element parameter can be repeated any number of times, allowing you to include any number of elements in the JSON array.

Example

SELECT
    JSON_ARRAY('John', 'Jane', 'Julia') AS names;

Output

[ 'John', 'Jane', 'Julia' ]

Explanation

The JSON_ARRAY function in the example creates a JSON array that includes the strings ‘John’, ‘Jane’, and ‘Julia’. The function returns this array, appearing under the column alias names.

Oracle

JSON_ARRAY(value_expr [, value_expr ]…[ FORMAT JSON ][ RETURNING { VARCHAR2(4000) | CLOB | BLOB } ][ ON NULL { NULL ON NULL | ABSENT ON NULL } ][ ON ERROR { ERROR ON ERROR | NULL ON ERROR | EMPTY ARRAY ON ERROR } ])

• value_expr: An expression that represents the JSON values to be included in the array. The expression values can be repeated any number of times.
• format json: An optional parameter ensuring that the specified values are properly formatted JSON strings. If not specified, Oracle will treat input strings as ordinary text.
• returning {varchar2(4000) | clob | blob}: Optional clause that specifies the data type of the returned JSON array. VARCHAR2 returns the JSON array as a string of up to 4000 bytes, CLOB returns it as a character large object, and BLOB as a binary large object.
• on null {null on null | absent on null}: Optional clause to define how null values are handled. NULL ON NULL includes null values in the array as JSON null values, while ABSENT ON NULL completely omits null values from the array.
• on error {error on error | null on error | empty array on error}: Optional clause to define the behaviour in case of errors. ERROR ON ERROR raises an error, NULL ON ERROR inserts null into the array, and EMPTY ARRAY ON ERROR generates an empty array.

Example

SELECT JSON_ARRAY('Oracle', 'SQL') AS JSON_Example FROM dual;

Output

["Oracle","SQL"]

Explanation

The code utilizes the JSON_ARRAY function of Oracle SQL. The function takes a series of values, and formats them into a JSON formatted array string. The return value is ‘Oracle’ and ‘SQL’ as elements within a JSON array.