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_JSONfunction does not support query type value or aSELECTstatement 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}}} |
+------------------------------------------------------------------------------+