TO_JSON
Converts a table column, an entire table, a scalar value, or a single row to a JSON object.
Syntax
TO_JSON (element[:>cast type])
Arguments
element
: A table column, an entire table, a scalar value, or a single row.cast type
: Data type in which to convert the input element, specified using the:>
operator. For more information, see CAST or CONVERT. It is optional.
Return Type
JSON object.
Remarks
The
TO_JSON
function does not support query type value or aSELECT
statement as an argument.
Examples
Examples 1 and 2 utilize the table and values below:
CREATE TABLE tj_alphabet (id char(6), letter varchar(25), lang varchar(15), position int 250)); INSERT INTO tj_alphabet VALUES ('grk02','beta', 'Greek', '2'), ('grk01','alpha', 'Greek', '1'), ('grk19','tau', 'Greek', '19'), ('grk04','delta', 'Greek', '4'), ('grk03','gamma', 'Greek', '3');
Note
In all the example below, the output column order may be different from the column order in the table.
Example 1
Converting a single column using TO_JSON
.
SELECT TO_JSON(letter) AS letter FROM tj_alphabet; **** +----------+ | State | +----------+ | "tau" | | "beta" | | "alpha" | | "delta" | | "gamma" | +----------+
Example 2
Converting a table with TO_JSON
.
When a table is passed the TO_JSON
function as an argument, it returns a JSON object in a single column with multiple rows and each column in the table as a key value. The .*
symbol is used with the table name to obtain these results.
SELECT TO_JSON(tj_alphabet.*) as Details FROM tj_alphabet; **** +-------------------------------------------------------------+ | Details | +-------------------------------------------------------------+ | {"id":"grk19","lang":"Greek","letter":"tau","position":19} | | {"id":"grk04","lang":"Greek","letter":"delta","position":4} | | {"id":"grk03","lang":"Greek","letter":"gamma","position":3} | | {"id":"grk02","lang":"Greek","letter":"beta","position":2} | | {"id":"grk01","lang":"Greek","letter":"alpha","position":1} | +-------------------------------------------------------------+
Example 3
Converting a scalar value with TO_JSON
.
The TO_JSON
function takes a single value such as integer, text, and other scalar values as an argument and returns a JSON object.
SELECT TO_JSON('alpha and "beta"':>text) AS ScalarOutput; **** +----------------------+ | ScalarOutput | +----------------------+ | "alpha and \"beta\"" | +----------------------+
Example 4
Using a cast type in the TO_JSON
argument is optional except when using the ROW()
operator. If the cast type
is not specified, SingleStoreDB implicitly converts the input value to a JSON string.For example, with casting, the TO_JSON
function converts the input string value 1
to an integer:
SELECT TO_JSON('1':>INT(10)) AS CastOutput; **** +------------+ | CastOutput | +------------+ | 1 | +------------+
Without casting, the TO_JSON
function outputs the input string value 1
as a JSON string:
SELECT TO_JSON('1') AS CastOutput; **** +------------+ | CastOutput | +------------+ | "1" | +------------+
Example 5
Converting a single table row/record with TO_JSON
.
When using the ROW()
operator, the input value must be cast to a record type.
SELECT TO_JSON(ROW(1,2):>RECORD(a INT, b INT)) AS RowOutput; **** +---------------+ | RowOutput | +---------------+ | {"a":1,"b":2} | +---------------+
Example 6
Convert a table with a JSON column by using TO_JSON
.
If the table has a JSON column, the format will remain unchanged if the table is passed as an argument in the TO_JSON
function.
CREATE TABLE alphabet (ID INTEGER PRIMARY KEY, letter JSON NOT NULL); INSERT INTO alphabet VALUES (1,'{ "Language": "Greek", "Letters": {"alpha": "beta","gamma": 24}}'); SELECT TO_JSON(alphabet.*) from alphabet; **** +------------------------------------------------------------------------------+ | TO_JSON(alphabet.*) | +------------------------------------------------------------------------------+ | {"ID":1,"letter":{"Language":"Greek","Letters":{"alpha":"beta","gamma":24}}} | +------------------------------------------------------------------------------+