TO_JSON

Description

Converts Doris internal data types to JSONB type. This function allows for converting compatible Doris data types into JSON representation without precision loss.

Syntax

TO_JSON(value)

Parameters

value - The value to be converted to JSONB type.

The following types have direct mapping to JSONB types and can be converted directly:

• Numeric types: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL
• Boolean type: BOOLEAN
• String type: STRING, VARCHAR, CHAR
• Complex types: ARRAY, STRUCT

Additionally, the function supports converting the following types:

• Date types: DATETIME, DATE, TIME
• IP types: IPV4, IPV6
• Complex types: MAP

For DATETIME, DATE, TIME, IPV4, IPV6 types that don't have corresponding JSONB types, they will be converted to STRING type. For MAP type, it will be converted to JSONB Object type. The Map keys must be STRING type, as JSON standard requires Object keys to be strings.

Return Value

Returns a value of JSONB type.

When the input value is SQL NULL, the function returns SQL NULL (not a JSON null value). When NULL values appear within arrays or structs, they are converted to JSON null values.

Examples

Basic scalar values

SELECT to_json(1), to_json(3.14), to_json("12345");

+------------+---------------+------------------+
| to_json(1) | to_json(3.14) | to_json("12345") |
+------------+---------------+------------------+
| 1          | 3.14          | "12345"          |
+------------+---------------+------------------+

Date types

SELECT 
     to_json(cast('2020-01-01' as date)) , 
     to_json(cast('2020-01-01 12:00:00' as datetime)),
     to_json(cast('2020-01-01 12:00:00.123' as datetime(3))),
     to_json(cast('2020-01-01 12:00:00.123456' as datetime(6))),
     to_json(cast('8:23:45' as time));

+-------------------------------------+--------------------------------------------------+---------------------------------------------------------+------------------------------------------------------------+----------------------------------+
| to_json(cast('2020-01-01' as date)) | to_json(cast('2020-01-01 12:00:00' as datetime)) | to_json(cast('2020-01-01 12:00:00.123' as datetime(3))) | to_json(cast('2020-01-01 12:00:00.123456' as datetime(6))) | to_json(cast('8:23:45' as time)) |
+-------------------------------------+--------------------------------------------------+---------------------------------------------------------+------------------------------------------------------------+----------------------------------+
| "2020-01-01"                        | "2020-01-01 12:00:00"                            | "2020-01-01 12:00:00.123"                               | "2020-01-01 12:00:00.123456"                               | "08:23:45"                       |
+-------------------------------------+--------------------------------------------------+---------------------------------------------------------+------------------------------------------------------------+----------------------------------+

IP types

SELECT 
     to_json(cast('192.168.0.1' as ipv4)) , 
     to_json(cast('2001:0db8:85a3:0000:0000:8a2e:0370:7334' as ipv6));

+--------------------------------------+------------------------------------------------------------------+
| to_json(cast('192.168.0.1' as ipv4)) | to_json(cast('2001:0db8:85a3:0000:0000:8a2e:0370:7334' as ipv6)) |
+--------------------------------------+------------------------------------------------------------------+
| "192.168.0.1"                        | "2001:db8:85a3::8a2e:370:7334"                                   |
+--------------------------------------+------------------------------------------------------------------+

Array conversion

SELECT to_json(array(array(1,2,3),array(4,5,6)));

+-------------------------------------------+
| to_json(array(array(1,2,3),array(4,5,6))) |
+-------------------------------------------+
| [[1,2,3],[4,5,6]]                         |
+-------------------------------------------+

SELECT to_json(array(12,34,null));

+----------------------------+
| to_json(array(12,34,null)) |
+----------------------------+
| [12,34,null]               |
+----------------------------+

Accessing array elements in resulting JSON

SELECT json_extract(to_json(array(array(1,2,3),array(4,5,6))), '$.[1].[2]');

+----------------------------------------------------------------------+
| json_extract(to_json(array(array(1,2,3),array(4,5,6))), '$.[1].[2]') |
+----------------------------------------------------------------------+
| 6                                                                    |
+----------------------------------------------------------------------+

Struct conversion

SELECT to_json(struct(123,array(4,5,6),"789"));

+------------------------------------------+
| to_json(struct(123,array(4,5,6),"789"))  |
+------------------------------------------+
| {"col1":123,"col2":[4,5,6],"col3":"789"} |
+------------------------------------------+

Accessing object properties in resulting JSON

SELECT json_extract(to_json(struct(123,array(4,5,6),"789")),"$.col2");

+----------------------------------------------------------------+
| json_extract(to_json(struct(123,array(4,5,6),"789")),"$.col2") |
+----------------------------------------------------------------+
| [4,5,6]                                                        |
+----------------------------------------------------------------+

MAP conversion

SELECT to_json(map("1",2,"abc",3));  

+-----------------------------+
| to_json(map("1",2,"abc",3)) |
+-----------------------------+
| {"1":2,"abc":3}             |
+-----------------------------+

SELECT to_json(map(1,2));  

to_json only support map with string-like key type

Handling NULL values

-- SQL NULL as input returns SQL NULL
SELECT to_json(null);

+---------------+
| to_json(null) |
+---------------+
| NULL          |
+---------------+

-- NULL values within arrays become JSON null values
SELECT to_json(array(12,34,null));

+----------------------------+
| to_json(array(12,34,null)) |
+----------------------------+
| [12,34,null]               |
+----------------------------+

Unsupported Doris Types

SELECT to_json(makedate(2025,5));

ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the compatibility function signature: to_json(DATE)

-- Convert to string first and then apply to_json
SELECT to_json(cast(makedate(2025,5) as string));

+-------------------------------------------+
| to_json(cast(makedate(2025,5) as string)) |
+-------------------------------------------+
| "2025-01-05"                              |
+-------------------------------------------+

Notes

1. Some types do not have direct JSON mapping (like DATE). For these types, you need to convert them to STRING first, then use TO_JSON.

2. When converting Doris internal types to JSONB using TO_JSON, there is no precision loss, unlike when converting through text representation.

3. JSONB objects in Doris have a size limitation of 1,048,576 bytes (1 MB) by default, which can be adjusted through the BE configuration string_type_length_soft_limit_bytes up to 2,147,483,643 bytes (approximately 2 GB).

4. In Doris JSON objects, keys cannot exceed 255 bytes in length.