TO_JSON Function

TO_JSON

Description

The TO_JSON function is used to convert a specified expression (expr) into JSON formatted text. This function can handle various data types and map them to the corresponding JSON types. It is very useful for processing and outputting JSON data in SQL queries.

Type Mapping Relationships

The following are the correspondences between LakeHouse data types and JSON types:

struct and map: Converted to JSON object
array: Converted to JSON array
tinyint, smallint, int, bigint, float, double: Converted to JSON numeric
boolean: Converted to JSON boolean
string: Converted to JSON string
date, datetime: Converted to JSON string, formatted as date or datetime
null: Converted to JSON null value

It should be noted that for the decimal type, when converted to JSON, it will be output as double type, which may result in a loss of precision. Additionally, for the map<K, V> type, if K is not of string, char, or varchar type, the system will first convert it to a string form before outputting. This is because the keys of JSON objects must be of string type.

Function Syntax


TO_JSON(expr)

Parameter Description

expr: An expression of any type.

Return Result

Return type: string

Usage Example

The following example demonstrates how to use the TO_JSON function to handle different types of data:

Map an array to a JSON object:


SELECT TO_JSON(MAP(ARRAY(1, 2), 2));
-- Output: {"[1,2]":2}

Directly output the JSON string:


SELECT TO_JSON(JSON '{"a": 1}');
-- Output: {"a": 1}

Convert the structure to a JSON object:


SELECT TO_JSON(NAMED_STRUCT( 'id',1, 'name','John Doe' ));
-- Output: {"id":1,"name":"John Doe"}

Convert null values to JSON null:


SELECT TO_JSON(NULL);
--Output empty string
+---------------+
| TO_JSON(NULL) |
+---------------+
|               |
+---------------+

By the above example, you can see the flexibility and practicality of the TO_JSON function when dealing with different types of data. This makes handling JSON data in SQL queries simpler and more efficient.