JSON_OBJECT()

JSON_OBJECT() takes a list of key-value pairs and returns a JSON object containing those members. Keys from typed scalars such as DATE, TIME, and DATETIME are converted to their string representation.

Description

JSON_OBJECT() takes an even number of arguments alternating between keys and values. Each key is converted to a string, and each value is converted to its JSON representation. If a key appears multiple times, the last occurrence wins (later values overwrite earlier ones under the same key).

Calling JSON_OBJECT() with no arguments returns an empty JSON object {}.

An odd number of arguments is an error.

Syntax

> JSON_OBJECT(key, val [, key, val] ...)

Arguments

Arguments	Description
key	Required for each pair. A string or typed scalar that names the JSON member.
val	Required for each pair. The value to associate with the key. Converted to its JSON representation.

Examples

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

-- Empty object
SELECT JSON_OBJECT();

-- Simple key-value pairs
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
SELECT JSON_OBJECT('a', 1, 'b', 'abc', 'c', NULL, 'd', true);

-- Numeric and decimal
SELECT JSON_OBJECT('d64', CAST(12345.67 AS DECIMAL(10,2)), 'd128', CAST(9876.54321 AS DECIMAL(30,10)));

-- Typed scalar keys (DATE, TIME, DATETIME)
SELECT JSON_OBJECT(CAST('2021-02-01' AS DATE), 'date_val');
SELECT JSON_OBJECT(CAST('11:11:11' AS TIME), 'time_val');
SELECT JSON_OBJECT(CAST('2021-02-01 11:11:11' AS DATETIME), 'dt_val');

-- UUID and YEAR
SELECT JSON_OBJECT('uid', CAST('550e8400-e29b-41d4-a716-446655440000' AS UUID));
SELECT JSON_OBJECT('year', CAST('2021' AS YEAR));

-- BIT and binary types
SELECT JSON_OBJECT('bit1', CAST(1 AS BIT(1)), 'bit0', CAST(0 AS BIT(1)));
SELECT JSON_OBJECT('binary', CAST('hello' AS BINARY(10)), 'varbin', CAST('world' AS VARBINARY));

-- Vectors
SELECT JSON_OBJECT('vec32', CAST('[1.0,2.0,3.0]' AS VECF32(3)), 'vec64', CAST('[1.5,2.5,3.5]' AS VECF64(3)));

-- Nested JSON
SELECT JSON_OBJECT('arr', JSON_ARRAY(1, 2, 3), 'obj', JSON_OBJECT('x', 1, 'y', 2));

-- Key overwrite: last key wins
SELECT JSON_OBJECT('id', 1, 'id', 2);
SELECT JSON_OBJECT('a', 1, 'a', 'replaced', 'a', NULL);

-- Table-based example with all types
CREATE TABLE jot (
    id INT,
    b BOOL, bi BIGINT, f FLOAT, d DOUBLE,
    d64 DECIMAL(10, 3), d128 DECIMAL(30, 10), d256 DECIMAL(50, 20),
    vc VARCHAR(100), t TEXT,
    td DATE, tt TIME, tdt DATETIME,
    uid UUID, js JSON,
    vf32 VECF32(3), vf64 VECF64(3)
);
INSERT INTO jot VALUES
(1, true, 11111111111111, 0.1, 0.2222222, 3.14, 3.14159265359, 3.14159265358979323846,
 '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_OBJECT('id', id, 'b', b, 'bi', bi, 'f', f, 'd', d, 'd64', d64, 'd128', d128, 'vc', vc, 't', t, 'date', td, 'time', tt, 'dt', tdt, 'uid', uid, 'js', js, 'vf32', vf32, 'vf64', vf64) FROM jot;

DROP TABLE jot;
DROP DATABASE dbfuncs;