Sign inTry Free

TO_JSON

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_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:

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

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

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

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

Was this article helpful?

On this page

Was this article helpful?