# TO\_JSON

Converts a table column, an entire table, a scalar value, a single row, or a PSQL array or record to a JSON value.

## 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/conditional-functions/cast-or-convert.md). 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;


```

```output

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


```

```output

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


```

```output

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

```

```output

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


```

```output

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


```

```output

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


```

```output

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


```

```output

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


```

```output

+---------------------------------+
| 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-udf.md) 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();

```

```output

+-----------------+
| 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());

```

```output

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

```

## Example 11

Use `TO_JSON` to convert a PSQL ARRAY of `ARRAY`s 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 `ARRAY`s to a JSON value.

```sql
SELECT TO_JSON(my_nested_array());

```

```output

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

```

***

Modified at: September 18, 2025

Source: [/db/v9.1/reference/sql-reference/json-functions/to-json/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/to-json/)

(An index of the documentation is available at /llms.txt)