TO_ JSON
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
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_
function does not support query type value or aJSON SELECT
statement as an argument.
Examples
Examples 1, 2, 7, and 8 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_
.
SELECT TO_JSON(letter) AS letter FROM tj_alphabet;
+----------+
| State |
+----------+
| "tau" |
| "beta" |
| "alpha" |
| "delta" |
| "gamma" |
+----------+
Example 2
Converting a table with TO_
.
When a table is passed the TO_
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..
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_
.
The TO_
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_
argument is optional except when using the ROW()
operator.cast type
is not specified, SingleStore implicitly converts the input value to a JSON string.TO_
function converts the input string value 1
to an integer:
SELECT TO_JSON('1':>INT(10)) AS CastOutput;
+------------+
| CastOutput |
+------------+
| 1 |
+------------+
Without casting, the TO_
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_
.
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_
.
If the table has a JSON column, the format will remain unchanged if the table is passed as an argument in the TO_
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}}} |
+------------------------------------------------------------------------------+
Example 7
Use a subquery with TO_
to obtain a subset of the table columns.
Create a result table by selecting only two columns from the table.
SELECT to_json(results_tbl.*) FROM (SELECT letter, id FROM tj_alphabet)
AS results_tbl;
+---------------------------------+
| to_json(results_tbl.*) |
+---------------------------------+
| {"id":"grk19","letter":"tau"} |
| {"id":"grk04","letter":"delta"} |
| {"id":"grk03","letter":"gamma"} |
| {"id":"grk02","letter":"beta"} |
| {"id":"grk01","letter":"alpha"} |
+---------------------------------+
Example 8
Create a result table by selecting only two columns from the table.
SELECT TO_JSON(ROW(tj_alphabet.id, tj_alphabet.letter):>RECORD(id
CHAR(6), letter VARCHAR(25))) AS Results FROM tj_alphabet;
+---------------------------------+
| Results |
+---------------------------------+
| {"id":"grk02","letter":"beta"} |
| {"id":"grk01","letter":"alpha"} |
| {"id":"grk19","letter":"tau"} |
| {"id":"grk04","letter":"delta"} |
| {"id":"grk03","letter":"gamma"} |
+---------------------------------+
Last modified: June 6, 2023