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

For example 1 and example 2, consider a table Customers with the following values:

CREATE TABLE Customers (ID INTEGER PRIMARY KEY, Name VARCHAR(250), State VARCHAR(250));;
INSERT INTO Customers VALUES
(10, 'James', 'Illinois'),
(12, 'Mary', 'New York'),
(14, 'Bob', 'Illinois'),
(16, 'Romy', 'Florida'),
(18, 'Teresa', 'New York');

Example 1: Converting a single column with TO_JSON

SELECT TO_JSON(State) AS State FROM Customers;
****
+-------------+
| State       |
+-------------+
| "Illinois"  |
| "New York"  |
| "Florida"   |
| "New York"  |
| "Illinois"  |
+-------------+

Note: The order in which the columns appear in the output can be different from the order in which the columns exist in the table.

Example 2: Converting a table with TO_JSON

When a table is passed as an argument to the TO_JSON function, it returns a single column JSON object with multiple rows, with each column in the table as a key value. It is required to use .* symbol along with the table name to reference the table.

SELECT TO_JSON(Customers.*) as Details FROM Customers;
****
+------------------------------------------------+
| Details                                        |
+------------------------------------------------+
| {"State":"Illinois","ID":10,"Name":"James"}    |
| {"State":"New York","ID":12,"Name":"Mary"}     |
| {"State":"Florida","ID":16,"Name":"Romy"}      |
| {"State":"New York","ID":18,"Name":"Teresa"}   |
| {"State":"Illinois","ID":14,"Name":"Bob"}      |
+------------------------------------------------+

Note: The order in which the columns appear in the output can be different from the order in which the columns exist in the table.

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('Joe said "Hello"':>text) AS ScalarOutput;
****
+----------------------+
| ScalarOutput         |
+----------------------+
| "Joe said \"Hello\"" |
+----------------------+

Note: It is optional to use cast type in the TO_JSON argument (except if the TO_JSON argument uses the ROW() operator. See Example 4 for details). If the cast type is not specified, SingleStore DB 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 4: Converting a single table row/record with TO_JSON

With a ROW() operator, it is mandatory to cast the input value to a record type.

SELECT TO_JSON(ROW(1,2):>RECORD(a INT, b INT)) AS RowOutput;
****
+---------------+
| RowOutput     |
+---------------+
| {"a":1,"b":2} |
+---------------+

Example 5: Converting a table having JSON column with TO_JSON

If a table has a JSON column, then the format of the JSON column remains unchanged when the table is passed as an argument to the TO_JSON function.

CREATE TABLE CustInfo (ID INTEGER PRIMARY KEY, Orders JSON NOT NULL);

INSERT INTO CustInfo VALUES (1,'{ "Name": "Albert Joe", "Items": {"Type": "Business","Turnover": 200}}');

SELECT TO_JSON(CustInfo.*) from CustInfo;
****
+------------------------------------------------------------------------------------+
| TO_JSON(CustInfo.*)                                                               |
+------------------------------------------------------------------------------------+
| {"ID":1,"Orders":{"Items":{"Turnover":200,"Type":"Business"},"Name":"Albert Joe"}} |
+------------------------------------------------------------------------------------+

Note: The order in which the columns appear in the output can be different from the order in which the columns exist in the table.

Was this Article Helpful?

Thanks For Your Feedback!