Important

The SingleStore 9.1 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 9.1.

TO_JSON

Syntax

SQL

TO_JSON (element[:>cast type])

Arguments

element: A table column, an entire table, a scalar value, a single row, a PSQL array or record.
- Nested PSQL structures such as PSQL array of arrays and arrays of records are supported.
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 value.

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 value 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 value.

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 remains 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.

SQL

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.

SQL

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"} |
+---------------------------------+

Example 9

Create a function which uses TO_JSON to convert a PSQL ARRAY to a JSON value.

SQL

DELIMITER //
CREATE OR REPLACE FUNCTION array_to_json() RETURNS JSON AS
DECLARE
    a ARRAY(INT) = [1,2];
BEGIN
    RETURN TO_JSON(a);
END //

DELIMITER ;

Call the function.

SQL

SELECT array_to_json();

+-----------------+
| array_to_json() |
+-----------------+
| [1,2]           |
+-----------------+

Example 10

Use TO_JSON to convert a PSQL ARRAY of TEXT to a JSON value.

Create a function which creates a PSQL ARRAY of TEXT.

SQL

DELIMITER //
CREATE OR REPLACE FUNCTION my_create_array() RETURNS ARRAY(TEXT) AS
DECLARE
    a ARRAY(TEXT) = ["hello","goodbye"];
BEGIN
    RETURN a;
END //

DELIMITER ;

Use the TO_JSON function to convert that ARRAY to a JSON value.

SQL

SELECT TO_JSON(my_create_array());

+----------------------------+
| TO_JSON(my_create_array()) |
+----------------------------+
| ["hello","goodbye"]        |
+----------------------------+

Example 11

Use TO_JSON to convert a PSQL ARRAY of ARRAYs to a JSON value.

Createa a function that creates a PSQL ARRAY of ARRAYs.

SQL

DELIMITER //

CREATE OR REPLACE FUNCTION my_nested_array() RETURNS ARRAY(ARRAY(INT)) AS
DECLARE
    a ARRAY(ARRAY(INT)) = [[1,2],[3,4]];
BEGIN
    RETURN a;
END //

DELIMITER ;

Use the TO_JSON function to convert the ARRAY of ARRAYs to a JSON value.

SQL

SELECT TO_JSON(my_nested_array());

+----------------------------+
| TO_JSON(my_nested_array()) |
+----------------------------+
| [[1,2],[3,4]]              |
+----------------------------+