Outdated Version

You are viewing an older version of this section. View current production version.

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