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.

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 a SELECT statement as an argument.

Examples

Examples 1, 2, 7, and 8 utilize the table and values below:

SQL

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.

SQL

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.

SQL

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.

SQL

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, SingleStore 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:

SQL

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:

SQL

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.

SQL

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.

SQL

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_JSON 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