This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 102a02fae50 docs: add json and json conversion documentation (#2680)
102a02fae50 is described below
commit 102a02fae50165637a4d0c462060f6d6db1c126b
Author: Mryange <[email protected]>
AuthorDate: Fri Aug 1 09:09:31 2025 +0800
docs: add json and json conversion documentation (#2680)
## Versions
- [x] dev
- [ ] 3.0
- [ ] 2.1
- [ ] 2.0
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [ ] Checked by AI
- [ ] Test Cases Built
---
.../sql-data-types/conversion/json-conversion.md | 381 ++++++++++++++++++++
.../sql-data-types/semi-structured/JSON.md | 245 ++++++++++++-
.../sql-data-types/conversion/json-conversion.md | 383 +++++++++++++++++++++
.../sql-data-types/semi-structured/JSON.md | 241 ++++++++++++-
sidebars.json | 1 +
5 files changed, 1245 insertions(+), 6 deletions(-)
diff --git
a/docs/sql-manual/basic-element/sql-data-types/conversion/json-conversion.md
b/docs/sql-manual/basic-element/sql-data-types/conversion/json-conversion.md
new file mode 100644
index 00000000000..12bf3bc0d1e
--- /dev/null
+++ b/docs/sql-manual/basic-element/sql-data-types/conversion/json-conversion.md
@@ -0,0 +1,381 @@
+---
+{
+ "title": "Cast to/from JSON",
+ "language": "en"
+}
+---
+
+The JSON type in Doris uses binary encoding for storage rather than text
storage, providing more efficient processing and storage. There is a one-to-one
mapping between JSON types and Doris internal types.
+
+## Cast to JSON
+
+### FROM String
+
+When casting a string to JSON, the string content must conform to valid JSON
syntax as defined in [RFC7159](https://datatracker.ietf.org/doc/html/rfc7159).
The parser will validate the string and convert it to the corresponding JSON
binary format.
+
+#### String Parsing Rules
+
+- If the string contains a valid JSON structure (object, array, number,
boolean, or null), it will be parsed as that JSON type:
+ ```sql
+ mysql> SELECT CAST('[1,2,3,4]' AS JSON); -- Output: [1,2,3,4] (parsed as
JSON array)
+ +---------------------------+
+ | CAST('[1,2,3,4]' AS JSON) |
+ +---------------------------+
+ | [1,2,3,4] |
+ +---------------------------+
+ ```
+
+- To create a JSON string value (where the string itself is treated as a JSON
string value rather than being parsed), use the `TO_JSON` function:
+ ```sql
+ mysql> SELECT TO_JSON('[1,2,3,4]'); -- Output: "[1,2,3,4]" (a JSON string
with quotes)
+ +----------------------+
+ | TO_JSON('[1,2,3,4]') |
+ +----------------------+
+ | "[1,2,3,4]" |
+ +----------------------+
+ ```
+
+#### Numeric Parsing Rules
+
+When parsing numeric values from a JSON string:
+
+- If a number contains a decimal point, it will be converted to a JSON Double
type:
+ ```sql
+ mysql> SELECT JSON_TYPE(CAST('{"key":123.45}' AS JSON), '$.key');
+ +------------------------------------------------------+
+ | JSON_TYPE(CAST('{"key":123.45}' AS JSON), '$.key') |
+ +------------------------------------------------------+
+ | double |
+ +------------------------------------------------------+
+ ```
+
+- If a number is an integer, it will be stored as the smallest compatible
integer type:
+ ```sql
+ mysql> SELECT JSON_TYPE(CAST('{"key":123456789}' AS JSON), '$.key');
+ +---------------------------------------------------------+
+ | JSON_TYPE(CAST('{"key":123456789}' AS JSON), '$.key') |
+ +---------------------------------------------------------+
+ | int |
+ +---------------------------------------------------------+
+ ```
+
+ ```sql
+ mysql> SELECT JSON_TYPE(CAST('{"key":1234567891234}' AS JSON), '$.key');
+ +-------------------------------------------------------------+
+ | JSON_TYPE(CAST('{"key":1234567891234}' AS JSON), '$.key') |
+ +-------------------------------------------------------------+
+ | bigint |
+ +-------------------------------------------------------------+
+ ```
+
+- If an integer exceeds the Int128 range, it will be stored as a double, which
may result in precision loss:
+ ```sql
+ mysql> SELECT
JSON_TYPE(CAST('{"key":12345678901234567890123456789012345678901234567890}' AS
JSON), '$.key');
+
+--------------------------------------------------------------------------------------------------+
+ |
JSON_TYPE(CAST('{"key":12345678901234567890123456789012345678901234567890}' AS
JSON), '$.key') |
+
+--------------------------------------------------------------------------------------------------+
+ | double
|
+
+--------------------------------------------------------------------------------------------------+
+ ```
+
+#### Error Handling
+
+When parsing a string to JSON:
+- In strict mode (default), invalid JSON syntax will cause an error
+- In non-strict mode, invalid JSON syntax will return NULL
+
+```sql
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST('{"invalid JSON' AS JSON);
++-----------------------------+
+| CAST('{"invalid JSON' AS JSON) |
++-----------------------------+
+| NULL |
++-----------------------------+
+
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST('{"invalid JSON' AS JSON);
+ERROR 1105 (HY000): errCode = 2, detailMessage =
(127.0.0.1)[INVALID_ARGUMENT]Failed to parse json string: {"invalid JSON, ...
+```
+
+### FROM Other Doris Types
+
+The following Doris types can be directly cast to JSON without loss of
precision:
+
+| Doris Type | JSON Type |
+|------------|-----------|
+| BOOLEAN | Bool |
+| TINYINT | Int8 |
+| SMALLINT | Int16 |
+| INT | Int32 |
+| BIGINT | Int64 |
+| LARGEINT | Int128 |
+| FLOAT | Float |
+| DOUBLE | Double |
+| DECIMAL | Decimal |
+| STRING | String |
+| ARRAY | Array |
+| STRUCT | Object |
+
+#### Examples
+
+```sql
+-- Integer array to JSON
+mysql> SELECT CAST(ARRAY(123,456,789) AS JSON);
++----------------------------------+
+| CAST(ARRAY(123,456,789) AS JSON) |
++----------------------------------+
+| [123,456,789] |
++----------------------------------+
+
+-- Decimal array to JSON (preserves precision)
+mysql> SELECT CAST(ARRAY(12345678.12345678,0.00000001,12.000000000000000001)
AS JSON);
++--------------------------------------------------------------------------+
+| CAST(ARRAY(12345678.12345678,0.00000001,12.000000000000000001) AS JSON) |
++--------------------------------------------------------------------------+
+| [12345678.123456780000000000,0.000000010000000000,12.000000000000000001] |
++--------------------------------------------------------------------------+
+```
+
+#### Types Not Directly Supported
+
+Types not in the table above cannot be directly cast to JSON:
+
+```sql
+mysql> SELECT CAST(MAKEDATE(2021, 1) AS JSON);
+ERROR 1105 (HY000): CAST AS JSONB can only be performed between JSONB, String,
Number, Boolean, Array, Struct types. Got Date to JSONB
+```
+
+Solution: First cast to a compatible type, then to JSON:
+
+```sql
+mysql> SELECT CAST(CAST(MAKEDATE(2021, 1) AS BIGINT) AS JSON);
++---------------------------------------------------+
+| CAST(CAST(MAKEDATE(2021, 1) AS BIGINT) AS JSON) |
++---------------------------------------------------+
+| 20210101 |
++---------------------------------------------------+
+```
+
+## Cast from JSON
+
+:::caution Behavior Change
+Before version 4.0, Doris had more relaxed requirements for JSON CAST behavior
and didn't handle overflow situations properly.
+
+Starting from version 4.0, overflow in JSON CAST will result in an error in
strict mode or return null in non-strict mode.
+:::
+
+### TO Boolean
+
+JSON Bool, Number, and String types can be cast to BOOLEAN:
+
+```sql
+-- From JSON Bool
+mysql> SELECT CAST(CAST('true' AS JSON) AS BOOLEAN);
++---------------------------------------+
+| CAST(CAST('true' AS JSON) AS BOOLEAN) |
++---------------------------------------+
+| 1 |
++---------------------------------------+
+
+-- From JSON Number
+mysql> SELECT CAST(CAST('123' AS JSON) AS BOOLEAN);
++--------------------------------------+
+| CAST(CAST('123' AS JSON) AS BOOLEAN) |
++--------------------------------------+
+| 1 |
++--------------------------------------+
+
+-- From JSON String (must contain valid boolean representation)
+mysql> SELECT CAST(TO_JSON('true') AS BOOLEAN);
++----------------------------------+
+| CAST(TO_JSON('true') AS BOOLEAN) |
++----------------------------------+
+| 1 |
++----------------------------------+
+```
+
+### TO Numeric Types
+
+JSON Bool, Number, and String types can be cast to numeric types (TINYINT,
SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL):
+
+```sql
+-- From JSON Number to INT
+mysql> SELECT CAST(CAST('123' AS JSON) AS INT);
++----------------------------------+
+| CAST(CAST('123' AS JSON) AS INT) |
++----------------------------------+
+| 123 |
++----------------------------------+
+
+-- From JSON Bool to numeric types
+mysql> SELECT CAST(CAST('true' AS JSON) AS INT), CAST(CAST('false' AS JSON) AS
DOUBLE);
++-----------------------------------+--------------------------------------+
+| CAST(CAST('true' AS JSON) AS INT) | CAST(CAST('false' AS JSON) AS DOUBLE) |
++-----------------------------------+--------------------------------------+
+| 1 | 0 |
++-----------------------------------+--------------------------------------+
+```
+
+Numeric overflow rules apply when casting to smaller types:
+
+```sql
+-- In strict mode, overflow causes error
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST(TO_JSON(12312312312312311) AS INT);
+ERROR 1105 (HY000): Cannot cast from jsonb value type 12312312312312311 to
doris type INT
+
+-- In non-strict mode, overflow returns NULL
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST(TO_JSON(12312312312312311) AS INT);
++-----------------------------------------+
+| CAST(TO_JSON(12312312312312311) AS INT) |
++-----------------------------------------+
+| NULL |
++-----------------------------------------+
+```
+
+### TO String
+
+Any JSON type can be cast to STRING, which produces the JSON text
representation:
+
+```sql
+mysql> SELECT CAST(CAST('{"key1":"value1","key2":123}' AS JSON) AS STRING);
++----------------------------------------------------------+
+| CAST(CAST('{"key1":"value1","key2":123}' AS JSON) AS STRING) |
++----------------------------------------------------------+
+| {"key1":"value1","key2":123} |
++----------------------------------------------------------+
+
+mysql> SELECT CAST(CAST('true' AS JSON) AS STRING);
++--------------------------------------+
+| CAST(CAST('true' AS JSON) AS STRING) |
++--------------------------------------+
+| true |
++--------------------------------------+
+```
+
+### TO Array
+
+JSON Array and String types can be cast to Doris ARRAY types:
+
+```sql
+mysql> SELECT CAST(TO_JSON(ARRAY(1,2,3)) AS ARRAY<INT>);
++-------------------------------------------+
+| CAST(TO_JSON(ARRAY(1,2,3)) AS ARRAY<INT>) |
++-------------------------------------------+
+| [1, 2, 3] |
++-------------------------------------------+
+
+-- Type conversion within array elements
+mysql> SELECT CAST(TO_JSON(ARRAY(1.2,2.3,3.4)) AS ARRAY<INT>);
++-------------------------------------------------+
+| CAST(TO_JSON(ARRAY(1.2,2.3,3.4)) AS ARRAY<INT>) |
++-------------------------------------------------+
+| [1, 2, 3] |
++-------------------------------------------------+
+
+-- Convert string to array
+mysql> SELECT CAST(TO_JSON("['123','456']") AS ARRAY<INT>);
++----------------------------------------------+
+| CAST(TO_JSON("['123','456']") AS ARRAY<INT>) |
++----------------------------------------------+
+| [123, 456] |
++----------------------------------------------+
+```
+
+Elements in arrays are converted individually following the standard cast
rules:
+
+```sql
+-- In non-strict mode, invalid elements become NULL
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST(TO_JSON(ARRAY(10,20,200)) AS ARRAY<TINYINT>);
++---------------------------------------------------+
+| CAST(TO_JSON(ARRAY(10,20,200)) AS ARRAY<TINYINT>) |
++---------------------------------------------------+
+| [10, 20, null] |
++---------------------------------------------------+
+
+-- In strict mode, invalid elements cause error
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST(TO_JSON(ARRAY(10,20,200)) AS ARRAY<TINYINT>);
+ERROR 1105 (HY000): Cannot cast from jsonb value type 200 to doris type TINYINT
+```
+
+### TO Struct
+
+JSON Object and String types can be cast to Doris STRUCT types:
+
+```sql
+mysql> SELECT CAST(CAST('{"key1":123,"key2":"456"}' AS JSON) AS
STRUCT<key1:INT,key2:STRING>);
++------------------------------------------------------------------------------+
+| CAST(CAST('{"key1":123,"key2":"456"}' AS JSON) AS
STRUCT<key1:INT,key2:STRING>) |
++------------------------------------------------------------------------------+
+| {"key1":123, "key2":"456"}
|
++------------------------------------------------------------------------------+
+
+mysql> SELECT CAST(TO_JSON('{"key1":123,"key2":"456"}') AS
STRUCT<key1:INT,key2:STRING>);
++----------------------------------------------------------------------------+
+| CAST(TO_JSON('{"key1":123,"key2":"456"}') AS STRUCT<key1:INT,key2:STRING>) |
++----------------------------------------------------------------------------+
+| {"key1":123, "key2":"456"} |
++----------------------------------------------------------------------------+
+```
+
+Fields in the struct are converted individually according to the specified
types:
+
+```sql
+mysql> SELECT CAST(CAST('{"key1":[123.45,678.90],"key2":[12312313]}' AS JSON)
AS STRUCT<key1:ARRAY<DOUBLE>,key2:ARRAY<BIGINT>>);
++--------------------------------------------------------------------------------------------------------------------------+
+| CAST(CAST('{"key1":[123.45,678.90],"key2":[12312313]}' AS JSON) AS
STRUCT<key1:ARRAY<DOUBLE>,key2:ARRAY<BIGINT>>) |
++--------------------------------------------------------------------------------------------------------------------------+
+| {"key1":[123.45, 678.9], "key2":[12312313]}
|
++--------------------------------------------------------------------------------------------------------------------------+
+```
+
+The field count and names must match between JSON and struct definition:
+
+```sql
+-- In non-strict mode, mismatched fields return NULL
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST(CAST('{"key1":123,"key2":456}' AS JSON) AS
STRUCT<key1:INT>);
++-------------------------------------------------------------------------+
+| CAST(CAST('{"key1":123,"key2":456}' AS JSON) AS STRUCT<key1:INT>) |
++-------------------------------------------------------------------------+
+| NULL |
++-------------------------------------------------------------------------+
+
+-- In strict mode, mismatched fields cause error
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST(CAST('{"key1":123,"key2":456}' AS JSON) AS
STRUCT<key1:INT>);
+ERROR 1105 (HY000): jsonb_value field size 2 is not equal to struct size 1
+```
+
+### JSON Null Handling
+
+JSON null is distinct from SQL NULL:
+
+- When a JSON field contains a null value, casting it to any Doris type
produces a SQL NULL:
+
+```sql
+mysql> SELECT CAST(CAST('null' AS JSON) AS INT);
++----------------------------------+
+| CAST(CAST('null' AS JSON) AS INT) |
++----------------------------------+
+| NULL |
++----------------------------------+
+```
+
+## Type Conversion Summary
+
+| JSON Type | Can Be Cast To |
+|-----------|---------------|
+| Bool | BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DOUBLE, FLOAT,
DECIMAL, STRING |
+| Null | (Always converted to SQL NULL) |
+| Number | BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DOUBLE, FLOAT,
DECIMAL, STRING |
+| String | BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DOUBLE, FLOAT,
DECIMAL, STRING, ARRAY, STRUCT |
+| Array | STRING, ARRAY |
+| Object | STRING, STRUCT |
+
+### keywords
+JSON, JSONB, CAST, conversion, to_json
diff --git
a/docs/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
b/docs/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
index 03a615378e8..e3e43ce2cbd 100644
--- a/docs/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
+++ b/docs/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
@@ -5,7 +5,15 @@
}
---
-## JSON
+## JSON Introduction
+
+JSON (JavaScript Object Notation) is an open standard file format and data
interchange format that uses human-readable text to store and transmit data.
According to the official specification
[RFC7159](https://datatracker.ietf.org/doc/html/rfc7159), JSON supports the
following basic types:
+- Bool
+- Null
+- Number
+- String
+- Array
+- Object
The JSON data type stores [JSON](https://www.rfc-editor.org/rfc/rfc8785) data
efficiently in a binary format and allows access to its internal fields through
JSON functions.
@@ -15,7 +23,70 @@ Compared to storing JSON strings in a regular STRING type,
the JSON type has two
1. JSON format validation during data insertion.
2. More efficient binary storage format, enabling faster access to JSON
internal fields using functions like `json_extract`, compared to `get_json_xx`
functions.
-**Note**: In version 1.2.x, the JSON type was named JSONB. To maintain
compatibility with MySQL, it was renamed to JSON starting from version 2.0.0.
Older tables can still use the previous name.
+:::caution Note
+In version 1.2.x, the JSON type was named JSONB. To maintain compatibility
with MySQL, it was renamed to JSON starting from version 2.0.0. Older tables
can still use the previous name.
+:::
+
+## JSON Number Precision Issues
+
+When using JSON, special attention is needed regarding number precision:
+- In most systems, the Number type is implemented based on IEEE 754-2008
binary 64-bit (double-precision) floating-point numbers (e.g., double type in
C++)
+- Since the JSON specification doesn't strictly define the underlying type for
Number, and JSON data is exchanged between different systems as text, precision
loss may occur
+
+For a JSON string like `{"abc": 18446744073709551616}`:
+
+```sql
+-- Conversion result in MySQL
+cast('{"abc": 18446744073709551616}' as json)
+-- Result: {"abc": 1.8446744073709552e19}
+```
+
+```javascript
+// Conversion result in JavaScript
+console.log(JSON.parse('{"abc": 18446744073709551616}'));
+// Result: {abc: 18446744073709552000}
+```
+
+To ensure numeric precision is preserved when exchanging data between systems,
large numbers should be stored as strings, e.g., `{"abc":
"18446744073709551616"}`.
+
+## JSON Type in Doris
+
+Doris supports data types that conform to the JSON standard specification and
uses an efficient JSONB (JSON Binary) format for binary encoding storage.
+
+### Supported Types
+
+Doris JSONB supports all standard JSON types. The main difference is that
Doris provides more fine-grained extensions for the Number type to more
accurately map to Doris's internal types.
+
+| JSON Type | Subtype | Corresponding Doris Type |
+|----------|-------|-----------------|
+| Bool | - | BOOLEAN |
+| Null | - | (No direct equivalent, represents JSON null value) |
+| Number | Int8 | TINYINT |
+| | Int16 | SMALLINT |
+| | Int32 | INT |
+| | Int64 | BIGINT |
+| | Int128 | LARGEINT |
+| | Double | DOUBLE |
+| | Float | FLOAT |
+| | Decimal | DECIMAL |
+| String | - | STRING |
+| Array | - | ARRAY |
+| Object | - | STRUCT |
+
+### Important Notes:
+- Meaning of Null:
+ - Null in JSON is a valid value representing "empty value". This is
different from SQL's NULL, which represents "unknown" or "missing".
+ - CAST('null' AS JSON) results in a JSONB column containing a JSON null
value, which itself is not NULL at the SQL level.
+ - CAST('null' AS JSON) IS NULL returns false (0), because the column
contains a known JSON null value, which is not a SQL NULL.
+
+## Operations and Limitations
+- Comparison and Arithmetic:
+ - JSONB columns cannot be directly compared with other data types (including
other JSONB columns) or used in arithmetic operations.
+ - Solution: Use JSON_EXTRACT function to extract scalar values (like INT,
DOUBLE, STRING, BOOLEAN) from JSONB, then convert them to the corresponding
native Doris types for comparison or calculation.
+- Sorting and Grouping:
+ - JSONB columns do not support ORDER BY and GROUP BY operations.
+- Implicit Conversion:
+ - Input Only: When inputting data into a JSONB column, STRING type can be
implicitly converted to JSONB (provided the string content is valid JSON text).
Other Doris types cannot be implicitly converted to JSONB.
### Syntax
@@ -37,7 +108,7 @@ INSERT INTO table_name(id, json_column_name) VALUES (1,
'{"k1": "100"}')
14 [123, 456]
```
-- When the all special character with `'\'` such as `'\r'`, `'\t'` appears in
JSON, you need to use the replace function to replace `"\"` with `"\\"`, for
example, you need replace `"\n"` to `"\\n"`
+- When the special character with `'\'` such as `'\r'`, `'\t'` appears in
JSON, you need to use the replace function to replace `"\"` with `"\\"`, for
example, you need replace `"\n"` to `"\\n"`
**Query:**
@@ -62,7 +133,173 @@ SELECT CAST(json_extract(json_column_name, '$.k1') AS INT)
FROM table_name;
The JSON type currently cannot be used for `GROUP BY`, `ORDER BY`, or
comparison operations.
:::
-### example
+## JSON Input
+
+Convert a string that conforms to JSON syntax to JSONB using CAST.
+
+```sql
+-- Simple scalar/basic values (numeric types, bool, null, string)
+mysql> SELECT cast('5' as json);
++-------------------+
+| cast('5' as json) |
++-------------------+
+| 5 |
++-------------------+
+
+-- Arrays with zero or more elements (elements don't need to be the same type)
+mysql> SELECT cast('[1, 2, "foo", null]' as json);
++-------------------------------------+
+| cast('[1, 2, "foo", null]' as json) |
++-------------------------------------+
+| [1,2,"foo",null] |
++-------------------------------------+
+
+-- Objects containing key-value pairs
+-- Note that object keys must always be quoted strings
+mysql> SELECT cast('{"bar": "baz", "balance": 7.77, "active": false}' as json);
++------------------------------------------------------------------+
+| cast('{"bar": "baz", "balance": 7.77, "active": false}' as json) |
++------------------------------------------------------------------+
+| {"bar":"baz","balance":7.77,"active":false} |
++------------------------------------------------------------------+
+
+-- Arrays and objects can be nested arbitrarily
+mysql> SELECT cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as
json);
++---------------------------------------------------------------------+
+| cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as json) |
++---------------------------------------------------------------------+
+| {"foo":[true,"bar"],"tags":{"a":1,"b":null}} |
++---------------------------------------------------------------------+
+```
+
+Doris's JSONB doesn't preserve semantically irrelevant details like whitespace.
+
+```sql
+mysql> -- The input text and JSON output may not look the same
+mysql> SELECT cast('[1, 2]' as json);
++----------------------------------------+
+| cast('[1, 2]' as json) |
++----------------------------------------+
+| [1,2] |
++----------------------------------------+
+```
+
+### Key Differences and Notes:
+- CAST(string AS JSON): Used to parse strings that conform to JSON syntax.
+- CAST(string AS JSON): For Number types, it will only parse Int8, Int16,
Int32, Int64, Int128, and Double types, not Decimal type.
+- Unlike most other JSON implementations, Doris's JSONB type supports up to
Int128 precision. Numbers exceeding Int128 precision may overflow.
+- If the input number string is 12.34, it will be parsed as a Double; if
there's no decimal point, it will be parsed as an integer (if the size exceeds
Int128 range, it will be converted to Double but with precision loss)
+
+## Using to_json to Convert Doris Internal Types to JSONB Type
+
+```sql
+mysql> 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" |
++------------+---------------+------------------+
+
+mysql> 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]] |
++-------------------------------------------+
+
+mysql> 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 |
++----------------------------------------------------------------------+
+
+mysql> 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"} |
++------------------------------------------+
+
+mysql> 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] |
++----------------------------------------------------------------+
+```
+
+to_json only supports converting Doris types that map to JSONB types.
+For example, DECIMAL can be used with to_json.
+However, DATE cannot; it needs to be converted to STRING first, then use
to_json.
+
+## JSONB Output
+
+When converting to plain text for interaction with other systems, Doris's
JSONB type ensures generation of valid JSON text:
+
+1. Null values:
+ - Output as null (without quotes)
+2. Boolean values:
+ - true → output true
+ - false → output false
+3. Numeric types:
+ - All numeric values output directly
+ - Example: 5 → output 5, 3.14 → output 3.14
+4. Strings:
+ - Output in double quotes: "<content>"
+ - Special characters are escaped:
+ - " → \"
+ - \ → \\
+ - / → \/
+ - Backspace → \b
+ - Form feed → \f
+ - Newline → \n
+ - Carriage return → \r
+ - Tab → \t
+ - Other control characters (ASCII < 32) convert to Unicode escape
sequences: \uXXXX
+5. Objects:
+ - Format: {<key-value pairs list>}
+ - Key-value pair format: "<key>": <value>
+ - Multiple key-value pairs separated by commas
+6. Arrays:
+ - Format: [<element list>]
+ - Multiple elements separated by commas
+7. Nested structure handling:
+ - Objects and arrays support unlimited nesting levels
+ - Each nesting level processed recursively using the same rules
+
+## Number Precision Issues
+
+When converting Doris internal types to JSONB using to_json, no precision loss
occurs.
+When using Doris internal JSON functions, if the return value is also a JSONB
type, no precision loss occurs.
+However, converting Doris JSONB to plain text and then back to JSONB can cause
precision loss.
+
+Example: Doris JSON type object
+```
+Object{
+ "a": (Decimal 18446744073709551616.123)
+}
+```
+
+Converted to plain text:
+```
+{"a": 18446744073709551616.123}
+```
+
+When plain text is converted back to Doris JSON type:
+```
+Object{
+ "a": (Double 18446744073709552000) // precision loss
+}
+```
+
+## Configuration and Limitations
+- JSON supports 1,048,576 bytes (1 MB) by default
+- Size limit can be adjusted via the BE configuration parameter
string_type_length_soft_limit_bytes
+- Maximum adjustment up to 2,147,483,643 bytes (approximately 2 GB)
+- In Doris JSON type Objects, key length cannot exceed 255 bytes
+
+## Usage Example
A tutorial for JSON datatype including create table, load data and query.
#### create database and table
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/conversion/json-conversion.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/conversion/json-conversion.md
new file mode 100644
index 00000000000..46dfcb7b962
--- /dev/null
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/conversion/json-conversion.md
@@ -0,0 +1,383 @@
+---
+{
+ "title": "转换为/从 JSON 类型",
+ "language": "zh-CN"
+}
+---
+
+Doris 中的 JSON 类型采用二进制编码存储，而不是文本存储，提供更高效的处理和存储方式。JSON 类型与 Doris 内部类型存在一一对应的关系。
+
+## 转换为 JSON
+
+### FROM String
+
+当将字符串转换为 JSON 时，字符串内容必须符合
[RFC7159](https://datatracker.ietf.org/doc/html/rfc7159) 定义的有效 JSON
语法。解析器会验证字符串并将其转换为相应的 JSON 二进制格式。
+
+#### 字符串解析规则
+
+- 如果字符串包含有效的 JSON 结构（对象、数组、数字、布尔值或 null），将解析为对应的 JSON 类型：
+ ```sql
+ mysql> SELECT CAST('[1,2,3,4]' AS JSON); -- 输出：[1,2,3,4]（解析为 JSON 数组）
+ +---------------------------+
+ | CAST('[1,2,3,4]' AS JSON) |
+ +---------------------------+
+ | [1,2,3,4] |
+ +---------------------------+
+ ```
+
+- 要创建 JSON 字符串值（将字符串本身视为 JSON 字符串值而不是解析它），请使用 `TO_JSON` 函数：
+ ```sql
+ mysql> SELECT TO_JSON('[1,2,3,4]'); -- 输出："[1,2,3,4]"（带引号的 JSON 字符串）
+ +----------------------+
+ | TO_JSON('[1,2,3,4]') |
+ +----------------------+
+ | "[1,2,3,4]" |
+ +----------------------+
+ ```
+
+#### 数字解析规则
+
+从 JSON 字符串解析数值时：
+
+- 如果数字包含小数点，将转换为 JSON Double 类型：
+ ```sql
+ mysql> SELECT JSON_TYPE(CAST('{"key":123.45}' AS JSON), '$.key');
+ +------------------------------------------------------+
+ | JSON_TYPE(CAST('{"key":123.45}' AS JSON), '$.key') |
+ +------------------------------------------------------+
+ | double |
+ +------------------------------------------------------+
+ ```
+
+- 如果数字是整数形式，将存储为最小兼容整数类型：
+ ```sql
+ mysql> SELECT JSON_TYPE(CAST('{"key":123456789}' AS JSON), '$.key');
+ +---------------------------------------------------------+
+ | JSON_TYPE(CAST('{"key":123456789}' AS JSON), '$.key') |
+ +---------------------------------------------------------+
+ | int |
+ +---------------------------------------------------------+
+ ```
+
+ ```sql
+ mysql> SELECT JSON_TYPE(CAST('{"key":1234567891234}' AS JSON), '$.key');
+ +-------------------------------------------------------------+
+ | JSON_TYPE(CAST('{"key":1234567891234}' AS JSON), '$.key') |
+ +-------------------------------------------------------------+
+ | bigint |
+ +-------------------------------------------------------------+
+ ```
+
+- 特别地，如果整数超出 Int128 范围，会使用 double 类型存储，这时会丢失精度：
+ ```sql
+ mysql> SELECT
JSON_TYPE(CAST('{"key":12345678901234567890123456789012345678901234567890}' AS
JSON), '$.key');
+
+--------------------------------------------------------------------------------------------------+
+ |
JSON_TYPE(CAST('{"key":12345678901234567890123456789012345678901234567890}' AS
JSON), '$.key') |
+
+--------------------------------------------------------------------------------------------------+
+ | double
|
+
+--------------------------------------------------------------------------------------------------+
+ ```
+
+#### 错误处理
+
+将字符串解析为 JSON 时：
+- 在严格模式（默认）下，无效的 JSON 语法会导致错误
+- 在非严格模式下，无效的 JSON 语法会返回 NULL
+
+```sql
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST('{"invalid JSON' AS JSON);
++-----------------------------+
+| CAST('{"invalid JSON' AS JSON) |
++-----------------------------+
+| NULL |
++-----------------------------+
+
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST('{"invalid JSON' AS JSON);
+ERROR 1105 (HY000): errCode = 2, detailMessage =
(127.0.0.1)[INVALID_ARGUMENT]Failed to parse json string: {"invalid JSON, ...
+```
+
+### FROM 其他 Doris 类型
+
+以下 Doris 类型可以直接转换为 JSON 而不丢失精度：
+
+| Doris 类型 | JSON 类型 |
+|------------|-----------|
+| BOOLEAN | Bool |
+| TINYINT | Int8 |
+| SMALLINT | Int16 |
+| INT | Int32 |
+| BIGINT | Int64 |
+| LARGEINT | Int128 |
+| FLOAT | Float |
+| DOUBLE | Double |
+| DECIMAL | Decimal |
+| STRING | String |
+| ARRAY | Array |
+| STRUCT | Object |
+
+#### 示例
+
+```sql
+-- 整数数组转 JSON
+mysql> SELECT CAST(ARRAY(123,456,789) AS JSON);
++----------------------------------+
+| CAST(ARRAY(123,456,789) AS JSON) |
++----------------------------------+
+| [123,456,789] |
++----------------------------------+
+
+-- Decimal 数组转 JSON（保留原始精度）
+mysql> SELECT CAST(ARRAY(12345678.12345678,0.00000001,12.000000000000000001)
AS JSON);
++--------------------------------------------------------------------------+
+| CAST(ARRAY(12345678.12345678,0.00000001,12.000000000000000001) AS JSON) |
++--------------------------------------------------------------------------+
+| [12345678.123456780000000000,0.000000010000000000,12.000000000000000001] |
++--------------------------------------------------------------------------+
+```
+
+#### 不直接支持的类型
+
+上表中未列出的类型不能直接转换为 JSON：
+
+```sql
+mysql> SELECT CAST(MAKEDATE(2021, 1) AS JSON);
+ERROR 1105 (HY000): CAST AS JSONB can only be performed between JSONB, String,
Number, Boolean, Array, Struct types. Got Date to JSONB
+```
+
+解决方案：先转换为兼容类型，再转为 JSON：
+
+```sql
+mysql> SELECT CAST(CAST(MAKEDATE(2021, 1) AS BIGINT) AS JSON);
++---------------------------------------------------+
+| CAST(CAST(MAKEDATE(2021, 1) AS BIGINT) AS JSON) |
++---------------------------------------------------+
+| 20210101 |
++---------------------------------------------------+
+```
+
+## 从 JSON 转换
+
+
+:::caution 行为变更
+在 4.0 版本之前，Doris 对 JSON CAST 的行为比较宽松，不会处理溢出行为。
+
+从 4.0 版本开始，在 JSON CAST 中出现溢出行为，在严格模式下报错，非严格模式下返回 NULL。
+:::
+
+### TO Boolean
+
+JSON Bool、Number 和 String 类型可以转换为 BOOLEAN：
+
+```sql
+-- 从 JSON Bool 转换
+mysql> SELECT CAST(CAST('true' AS JSON) AS BOOLEAN);
++---------------------------------------+
+| CAST(CAST('true' AS JSON) AS BOOLEAN) |
++---------------------------------------+
+| 1 |
++---------------------------------------+
+
+-- 从 JSON Number 转换
+mysql> SELECT CAST(CAST('123' AS JSON) AS BOOLEAN);
++--------------------------------------+
+| CAST(CAST('123' AS JSON) AS BOOLEAN) |
++--------------------------------------+
+| 1 |
++--------------------------------------+
+
+-- 从 JSON String 转换（必须包含有效的布尔值表示）
+mysql> SELECT CAST(TO_JSON('true') AS BOOLEAN);
++----------------------------------+
+| CAST(TO_JSON('true') AS BOOLEAN) |
++----------------------------------+
+| 1 |
++----------------------------------+
+```
+
+### TO 数值类型
+
+JSON Bool、Number 和 String
类型可以转换为数值类型（TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、DECIMAL）：
+
+```sql
+-- 从 JSON Number 转换为 INT
+mysql> SELECT CAST(CAST('123' AS JSON) AS INT);
++----------------------------------+
+| CAST(CAST('123' AS JSON) AS INT) |
++----------------------------------+
+| 123 |
++----------------------------------+
+
+-- 从 JSON Bool 转换为数值类型
+mysql> SELECT CAST(CAST('true' AS JSON) AS INT), CAST(CAST('false' AS JSON) AS
DOUBLE);
++-----------------------------------+--------------------------------------+
+| CAST(CAST('true' AS JSON) AS INT) | CAST(CAST('false' AS JSON) AS DOUBLE) |
++-----------------------------------+--------------------------------------+
+| 1 | 0 |
++-----------------------------------+--------------------------------------+
+```
+
+当转换为较小类型时，适用数值溢出规则：
+
+```sql
+-- 在严格模式下，溢出会导致错误
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST(TO_JSON(12312312312312311) AS INT);
+ERROR 1105 (HY000): Cannot cast from jsonb value type 12312312312312311 to
doris type INT
+
+-- 在非严格模式下，溢出返回 NULL
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST(TO_JSON(12312312312312311) AS INT);
++-----------------------------------------+
+| CAST(TO_JSON(12312312312312311) AS INT) |
++-----------------------------------------+
+| NULL |
++-----------------------------------------+
+```
+
+### TO String
+
+任何 JSON 类型都可以转换为 STRING，生成 JSON 文本表示：
+
+```sql
+mysql> SELECT CAST(CAST('{"key1":"value1","key2":123}' AS JSON) AS STRING);
++----------------------------------------------------------+
+| CAST(CAST('{"key1":"value1","key2":123}' AS JSON) AS STRING) |
++----------------------------------------------------------+
+| {"key1":"value1","key2":123} |
++----------------------------------------------------------+
+
+mysql> SELECT CAST(CAST('true' AS JSON) AS STRING);
++--------------------------------------+
+| CAST(CAST('true' AS JSON) AS STRING) |
++--------------------------------------+
+| true |
++--------------------------------------+
+```
+
+### TO Array
+
+JSON Array , String 类型可以转换为 Doris ARRAY 类型：
+
+```sql
+mysql> SELECT CAST(TO_JSON(ARRAY(1,2,3)) AS ARRAY<INT>);
++-------------------------------------------+
+| CAST(TO_JSON(ARRAY(1,2,3)) AS ARRAY<INT>) |
++-------------------------------------------+
+| [1, 2, 3] |
++-------------------------------------------+
+
+-- 数组元素内的类型转换
+mysql> SELECT CAST(TO_JSON(ARRAY(1.2,2.3,3.4)) AS ARRAY<INT>);
++-------------------------------------------------+
+| CAST(TO_JSON(ARRAY(1.2,2.3,3.4)) AS ARRAY<INT>) |
++-------------------------------------------------+
+| [1, 2, 3] |
++-------------------------------------------------+
+
+-- 把字符串转换成数组
+mysql> SELECT CAST(TO_JSON("['123','456']") AS ARRAY<INT>);
++----------------------------------------------+
+| CAST(TO_JSON("['123','456']") AS ARRAY<INT>) |
++----------------------------------------------+
+| [123, 456] |
++----------------------------------------------+
+```
+
+数组中的元素按标准转换规则单独转换：
+
+```sql
+-- 在非严格模式下，无效元素变为 NULL
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST(TO_JSON(ARRAY(10,20,200)) AS ARRAY<TINYINT>);
++---------------------------------------------------+
+| CAST(TO_JSON(ARRAY(10,20,200)) AS ARRAY<TINYINT>) |
++---------------------------------------------------+
+| [10, 20, null] |
++---------------------------------------------------+
+
+-- 在严格模式下，无效元素导致错误
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST(TO_JSON(ARRAY(10,20,200)) AS ARRAY<TINYINT>);
+ERROR 1105 (HY000): Cannot cast from jsonb value type 200 to doris type TINYINT
+```
+
+### TO Struct
+
+JSON Object，String 类型可以转换为 Doris STRUCT 类型：
+
+```sql
+mysql> SELECT CAST(CAST('{"key1":123,"key2":"456"}' AS JSON) AS
STRUCT<key1:INT,key2:STRING>);
++------------------------------------------------------------------------------+
+| CAST(CAST('{"key1":123,"key2":"456"}' AS JSON) AS
STRUCT<key1:INT,key2:STRING>) |
++------------------------------------------------------------------------------+
+| {"key1":123, "key2":"456"}
|
++------------------------------------------------------------------------------+
+
+
+mysql> SELECT CAST(TO_JSON('{"key1":123,"key2":"456"}') AS
STRUCT<key1:INT,key2:STRING>);
++----------------------------------------------------------------------------+
+| CAST(TO_JSON('{"key1":123,"key2":"456"}') AS STRUCT<key1:INT,key2:STRING>) |
++----------------------------------------------------------------------------+
+| {"key1":123, "key2":"456"} |
++----------------------------------------------------------------------------+
+```
+
+结构中的字段根据指定的类型单独转换：
+
+```sql
+mysql> SELECT CAST(CAST('{"key1":[123.45,678.90],"key2":[12312313]}' AS JSON)
AS STRUCT<key1:ARRAY<DOUBLE>,key2:ARRAY<BIGINT>>);
++--------------------------------------------------------------------------------------------------------------------------+
+| CAST(CAST('{"key1":[123.45,678.90],"key2":[12312313]}' AS JSON) AS
STRUCT<key1:ARRAY<DOUBLE>,key2:ARRAY<BIGINT>>) |
++--------------------------------------------------------------------------------------------------------------------------+
+| {"key1":[123.45, 678.9], "key2":[12312313]}
|
++--------------------------------------------------------------------------------------------------------------------------+
+```
+
+JSON 和结构定义之间的字段计数和名称必须匹配：
+
+```sql
+-- 在非严格模式下，字段不匹配返回 NULL
+mysql> SET enable_strict_cast = false;
+mysql> SELECT CAST(CAST('{"key1":123,"key2":456}' AS JSON) AS
STRUCT<key1:INT>);
++-------------------------------------------------------------------------+
+| CAST(CAST('{"key1":123,"key2":456}' AS JSON) AS STRUCT<key1:INT>) |
++-------------------------------------------------------------------------+
+| NULL |
++-------------------------------------------------------------------------+
+
+-- 在严格模式下，字段不匹配导致错误
+mysql> SET enable_strict_cast = true;
+mysql> SELECT CAST(CAST('{"key1":123,"key2":456}' AS JSON) AS
STRUCT<key1:INT>);
+ERROR 1105 (HY000): jsonb_value field size 2 is not equal to struct size 1
+```
+
+### JSON Null 处理
+
+JSON null 与 SQL NULL 不同：
+
+- 当 JSON 字段包含 null 值时，转换为任何 Doris 类型都会产生 SQL NULL：
+
+```sql
+mysql> SELECT CAST(CAST('null' AS JSON) AS INT);
++----------------------------------+
+| CAST(CAST('null' AS JSON) AS INT) |
++----------------------------------+
+| NULL |
++----------------------------------+
+```
+
+## 类型转换总结表
+
+| JSON 类型 | 可转换为 |
+|-----------|---------------|
+| Bool | BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DOUBLE, FLOAT,
DECIMAL, STRING |
+| Null | (始终转换为 SQL NULL) |
+| Number | BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DOUBLE, FLOAT,
DECIMAL, STRING |
+| String | BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DOUBLE, FLOAT,
DECIMAL, STRING, ARRAY, STRUCT |
+| Array | STRING, ARRAY |
+| Object | STRING, STRUCT |
+
+### keywords
+JSON, JSONB, CAST, 类型转换，to_json
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
index c6df13786d1..3e8fe1a3280 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/JSON.md
@@ -5,6 +5,16 @@
}
---
+## JSON 介绍
+
+JSON（JavaScript Object Notation）是一种开放标准的文件格式和数据交换格式，使用可读性强的纯文本来存储和传输数据。根据官方规范
[RFC7159](https://datatracker.ietf.org/doc/html/rfc7159)，JSON 支持以下基本类型：
+- Bool（布尔值）
+- Null（空值）
+- Number（数字）
+- String（字符串）
+- Array（数组）
+- Object（对象）
+
JSON 数据类型，用二进制格式高效存储 [JSON](https://www.rfc-editor.org/rfc/rfc8785) 数据，通过 JSON
函数访问其内部字段。
默认支持 1048576 字节（1 MB），可调大到 2147483643 字节（2 GB），可通过 BE
配置`string_type_length_soft_limit_bytes` 调整。
@@ -17,6 +27,67 @@ JSON 数据类型，用二进制格式高效存储 [JSON](https://www.rfc-editor
在1.2.x版本中，JSON 类型的名字是 JSONB，为了尽量跟 MySQL 兼容，从 2.0.0 版本开始改名为 JSON，老的表仍然可以使用。
:::
+## JSON 数字精度问题
+
+在使用 JSON 时，需要特别注意数字精度问题：
+- 大多数系统中 Number 类型的实现基于 IEEE 754-2008 二进制64位（双精度）浮点数（如 C++ 中的 double 类型）
+- 由于 JSON 规范没有强制规定 Number 的底层类型，且不同系统间通过纯文本交换 JSON 数据，可能导致精度损失
+
+对于 JSON 字符串 `{"abc": 18446744073709551616}`：
+
+```sql
+-- MySQL 中的转换结果
+cast('{"abc": 18446744073709551616}' as json)
+-- 结果: {"abc": 1.8446744073709552e19}
+```
+
+```javascript
+// JavaScript 中的转换结果
+console.log(JSON.parse('{"abc": 18446744073709551616}'));
+// 结果: {abc: 18446744073709552000}
+```
+
+如需在不同系统间保证数字精度不损失，应将大数值存储为字符串格式，如 `{"abc": "18446744073709551616"}`。
+
+## Doris 中的 JSON 类型
+
+Doris 支持符合 JSON 标准规范的数据类型，并采用高效的 JSONB（JSON Binary）格式进行二进制编码存储。
+
+### 支持的类型
+
+Doris JSONB 支持所有 JSON 标准类型。主要区别在于 Doris 对 Number 类型进行了更精细的扩展，以便更精确地映射到 Doris
的内部类型。
+
+| JSON 类型 | 子类型 | 对应的 Doris 类型 |
+|----------|-------|-----------------|
+| Bool | - | BOOLEAN |
+| Null | - | (无直接对应，表示 JSON null 值) |
+| Number | Int8 | TINYINT |
+| | Int16 | SMALLINT |
+| | Int32 | INT |
+| | Int64 | BIGINT |
+| | Int128 | LARGEINT |
+| | Double | DOUBLE |
+| | Float | FLOAT |
+| | Decimal | DECIMAL |
+| String | - | STRING |
+| Array | - | ARRAY |
+| Object | - | STRUCT |
+
+### 重要说明：
+- Null 的含义:
+ - JSON 中的 null 是一个有效的值，表示"空值"。这与 SQL 的 NULL（表示"未知"或"缺失"）是不同的概念。
+ - CAST('null' AS JSON) 得到一个包含 JSON null 值的 JSONB 列，该列本身在 SQL 层面是非 NULL 的。
+ - CAST('null' AS JSON) IS NULL 的结果是 false (0)，因为该列包含一个已知的 JSON null
值，他不是一个SQL的NULL。
+
+## 运算与限制
+- 比较与运算：
+ - JSONB 列不能直接与其他数据类型（包括其他 JSONB 列）进行比较或进行算术运算。
+ - 解决方案： 使用 JSON_EXTRACT 函数提取出 JSONB 中的标量值（如 INT, DOUBLE, STRING,
BOOLEAN），然后将其转换为对应的 Doris 原生类型进行比较或运算。
+- 排序与分组：
+ - JSONB 列不支持 ORDER BY 和 GROUP BY 操作。
+- 隐式转换：
+ - 仅限输入： 在将数据输入到 JSONB 列时，STRING 类型可以隐式转换为 JSONB（前提是字符串内容是有效的 JSON 文本）。其他
Doris 类型不能隐式转换为 JSONB。
+
### 语法
**定义**
@@ -63,10 +134,176 @@ JSON 类型暂时不能用于 GROUP BY，ORDER BY，比较大小
:::
-### 使用示例
+## JSONB的输入
+
+将符合 JSON 语法的字符串使用CAST转换为 JSONB。
+
+```sql
+-- 简单标量/基本值(数字类型，bool，null，字符串)
+mysql> SELECT cast('5' as json);
++-------------------+
+| cast('5' as json) |
++-------------------+
+| 5 |
++-------------------+
+
+-- 有零个或者更多元素的数组（元素不需要为同一类型）
+mysql> SELECT cast('[1, 2, "foo", null]' as json);
++-------------------------------------+
+| cast('[1, 2, "foo", null]' as json) |
++-------------------------------------+
+| [1,2,"foo",null] |
++-------------------------------------+
+
+-- 包含键值对的对象
+-- 注意对象键必须总是带引号的字符串
+mysql> SELECT cast('{"bar": "baz", "balance": 7.77, "active": false}' as json);
++------------------------------------------------------------------+
+| cast('{"bar": "baz", "balance": 7.77, "active": false}' as json) |
++------------------------------------------------------------------+
+| {"bar":"baz","balance":7.77,"active":false} |
++------------------------------------------------------------------+
+
+-- 数组和对象可以被任意嵌套
+mysql> SELECT cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as
json);
++---------------------------------------------------------------------+
+| cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as json) |
++---------------------------------------------------------------------+
+| {"foo":[true,"bar"],"tags":{"a":1,"b":null}} |
++---------------------------------------------------------------------+
+```
+
+Doris的JSONB不保留语义上无关紧要的细节，如空格。
+
+```sql
+mysql> -- 输入的文本，和JSON的输出可能并不一样
+mysql> SELECT cast('[1, 2]' as json);
++----------------------------------------+
+| cast('[1, 2]' as json) |
++----------------------------------------+
+| [1,2] |
++----------------------------------------+
+```
+
+### 关键区别与注意：
+- CAST(string AS JSON)：用于解析符合 JSON 语法的字符串。
+- CAST(string AS JSON)：对于Number类型，只会解析出Int8,Int16,Int32,Int64,Int128,Double
类型，不会解析出Decimal类型。
+- 和其他大部分的JSON实现不同，Doris 的JSONB类型最高支持Int128的精度，对于超出Int128的精度的数字，会有溢出的问题。
+-
如果输入的Number字符串为12.34会解析成一个Double，如果没有小数点会解析成一个整数(如果整数的大小超出了Int128的范围，会转换为Double存储，但这有精度损失)
+
+## 使用to_json 将Doris内部的类型转换到JSONB类型
+
+```sql
+mysql> 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" |
++------------+---------------+------------------+
+
+mysql> 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]] |
++-------------------------------------------+
+
+mysql> 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 |
++----------------------------------------------------------------------+
+
+mysql> 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"} |
++------------------------------------------+
+
+mysql> 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] |
++----------------------------------------------------------------+
+```
+
+to_json 只支持将和JSONB类型有映射的Doris类型转换到JSONB。
+例如DECIMAL 可以使用to_json。
+但是DATE不可以，需要先将其转换为 STRING 类型，然后再使用 to_json。
+
+## JSONB的输出
+
+Doris 的 JSONB 类型在转换成纯文本与其他系统交互时，会保证生成合法的 JSON 文本：
+
+1. Null 值：
+ - 输出为 null（不带引号）
+2. 布尔值：
+ - true → 输出 true
+ - false → 输出 false
+3. 数字类型：
+ - 所有数值类型直接输出其数值
+ - 例如：5 → 输出 5，3.14 → 输出 3.14
+4. 字符串：
+ - 输出在双引号内："<内容>"
+ - 特殊字符进行转义处理：
+ - " → \"
+ - \ → \\
+ - / → \/
+ - 退格符 → \b
+ - 换页符 → \f
+ - 换行符 → \n
+ - 回车符 → \r
+ - 制表符 → \t
+ - 其他控制字符（ASCII < 32）转换为 Unicode 转义序列：\uXXXX
+5. 对象（Object）：
+ - 格式：{<键值对列表>}
+ - 键值对格式："<键>": <值>
+ - 多个键值对用逗号分隔
+6. 数组（Array）：
+ - 格式：[<元素列表>]
+ - 多个元素用逗号分隔
+7. 嵌套结构处理：
+ - 对象和数组支持无限层级嵌套
+ - 每个嵌套层级按相同规则递归处理
+
+## 数字精度问题
+
+使用to_json将Doris 内部类型转换为 JSONB 类型时，不会出现精度损失。
+使用Doris内部的JSON函数，如果返回值也是 JSONB 类型，不会出现精度损失。
+但是如果将Doris的JSONB转换成纯文本再转换成JSONB的话，会出现精度损失问题。
+
+示例：Doris JSON 类型的对象
+```
+Object{
+ "a": (Decimal 18446744073709551616.123)
+}
+```
+
+转换为纯文本：
+```
+{"a": 18446744073709551616.123}
+```
+
+当纯文本转回 Doris JSON 类型：
+```
+Object{
+ "a": (Double 18446744073709552000) // 精度丢失
+}
+```
+
+## 配置和限制
+- JSON 默认支持 1,048,576 字节（1 MB）大小
+- 可通过 BE 配置 string_type_length_soft_limit_bytes 参数调整大小限制
+- 最大可调整至 2,147,483,643 字节（约 2 GB）
+- Doris JSON 类型的 Object 中，key 长度不能超过 255 个字节
+
+## 使用示例
用一个从建表、导数据、查询全周期的例子说明JSON数据类型的功能和用法。
-#### 创建库表
+### 创建库表
```
CREATE DATABASE testdb;
diff --git a/sidebars.json b/sidebars.json
index 10b6d718f8f..e4291268ee2 100644
--- a/sidebars.json
+++ b/sidebars.json
@@ -1034,6 +1034,7 @@
"sql-manual/basic-element/sql-data-types/conversion/float-double-conversion",
"sql-manual/basic-element/sql-data-types/conversion/int-conversion",
"sql-manual/basic-element/sql-data-types/conversion/ip-conversion",
+
"sql-manual/basic-element/sql-data-types/conversion/json-conversion",
"sql-manual/basic-element/sql-data-types/conversion/time-conversion"
]
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
