JSON_ARRAY()

JSON_ARRAY() evaluates a (possibly empty) list of values and returns a JSON array containing those values. It supports all MatrixOne data types including numeric, string, temporal, UUID, JSON, vector, binary, bit, enum, and year types.

Description

JSON_ARRAY() takes zero or more arguments and returns a JSON array. Each argument is converted to its JSON representation:

• NULL values become JSON null.

• Numeric types (INT, BIGINT, FLOAT, DOUBLE, DECIMAL) become JSON numbers.

• Boolean values become JSON true or false.

• String types (CHAR, VARCHAR, TEXT) become JSON strings.

• Temporal types (DATE, TIME, DATETIME, TIMESTAMP) become JSON strings.

• UUID values become JSON strings.

• JSON values are embedded directly.

• VECF32 and VECF64 vectors become JSON arrays of numbers.

• BIT, YEAR, ENUM, BINARY, and VARBINARY types are converted to their string representations.

Calling JSON_ARRAY() with no arguments returns an empty JSON array [].

Syntax

> JSON_ARRAY([val [, val] ...])

Arguments

Arguments	Description
val	Optional. One or more values to include in the array. Each value is converted to its JSON representation.

Examples

DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;

-- Empty array
SELECT JSON_ARRAY();

-- Simple values
SELECT JSON_ARRAY(1, 2, 3);
SELECT JSON_ARRAY(1, 'abc', NULL, true);
SELECT JSON_ARRAY('hello', 'world');

-- Temporal values
SELECT JSON_ARRAY(CAST('2021-02-01' AS DATE), CAST('11:11:11' AS TIME), CAST('2021-02-01 11:11:11' AS DATETIME));

-- Decimal types
SELECT JSON_ARRAY(CAST(12345.67 AS DECIMAL(10,2)), CAST(9876.54321 AS DECIMAL(30,10)));

-- UUID
SELECT JSON_ARRAY(CAST('550e8400-e29b-41d4-a716-446655440000' AS UUID));

-- Vector types
SELECT JSON_ARRAY(CAST('[1.0,2.0,3.0]' AS VECF32(3)));
SELECT JSON_ARRAY(CAST('[1.5,2.5,3.5]' AS VECF64(3)));

-- Nested JSON from other JSON functions
SELECT JSON_ARRAY(JSON_EXTRACT('{"a":1}', '$.a'), JSON_EXTRACT('{"b":2}', '$.b'));

-- BIT and YEAR types
SELECT JSON_ARRAY(CAST(1 AS BIT(1)), CAST(0 AS BIT(1)));
SELECT JSON_ARRAY(CAST('2021' AS YEAR), CAST('1999' AS YEAR));

-- Table-based example
CREATE TABLE jat (
    id INT,
    b BOOL, bi BIGINT, f FLOAT, d DOUBLE,
    d64 DECIMAL(10, 3), d128 DECIMAL(30, 10),
    vc VARCHAR(100), t TEXT,
    td DATE, tt TIME, tdt DATETIME,
    uid UUID, js JSON,
    vf32 VECF32(3), vf64 VECF64(3)
);
INSERT INTO jat VALUES
(1, true, 11111111111111, 0.1, 0.2222222, 3.14, 3.14159265359,
 'vvvv', 'tttttttt',
 '2021-02-01', '11:11:11', '2021-02-01 11:11:11',
 '550e8400-e29b-41d4-a716-446655440000',
 '{"a": 1, "b": [1, 2, 3], "c": {"d": "hello"}}',
 CAST('[1.0,2.0,3.0]' AS VECF32(3)), CAST('[1.5,2.5,3.5]' AS VECF64(3)))
;

SELECT id, JSON_ARRAY(id, b, bi, f, d, d64, d128, vc, t, td, tt, tdt, uid, js, vf32, vf64) FROM jat;

DROP TABLE jat;
DROP DATABASE dbfuncs;

See Also