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 20103900ab6 update JSON_OBJECT (#2606)
20103900ab6 is described below
commit 20103900ab69046c5b246a48803f5b156e416765
Author: Jerry Hu <[email protected]>
AuthorDate: Thu Jul 10 15:22:22 2025 +0800
update JSON_OBJECT (#2606)
## Versions
- [x] dev
- [ ] 3.0
- [ ] 2.1
- [ ] 2.0
---
.../scalar-functions/json-functions/json-object.md | 241 +++++++++++----------
.../scalar-functions/json-functions/json-object.md | 231 +++++++++++---------
2 files changed, 259 insertions(+), 213 deletions(-)
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
b/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
index e3d646062c1..dac20fb48a8 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
@@ -6,125 +6,148 @@
---
## Description
-Generate a json object containing the specified Key-Value,
-an exception error is returned when Key is NULL or the number of parameters
are odd.
+
+Generate one JSON object containing specified Key-Value pairs. Returns an
error when the Key value is NULL or when an odd number of parameters is passed.
## Syntax
+
```sql
-JSON_OBJECT (<key>, <value>[,<key>, <value>, ...])
+JSON_OBJECT (<key>, <value>[, <key>, <value>, ...])
```
## Parameters
+### Variable parameters:
+- `<key>`: String type
+- `<value>`: Multiple types, Doris will automatically convert non-JSON type
parameters to JSON type through the [`TO_JSON`](./to-json.md) function.
-| Parameter | Description |
-|---------|------------------------------------------|
-| `<key>` | The Key value in the Key-Value of the generated json object. |
-| `<value>` | The Value value in the Key-Value of the generated json object. |
|
+## Notes
+- The number of parameters must be even, can be 0 parameters (returns an empty
JSON object).
+- By convention, the parameter list consists of alternating keys and values.
+- Keys are forcibly converted to text according to JSON definition.
+- If the passed Key is NULL, returns an exception error.
+- Value parameters are converted in a way that can be converted to JSON, must
be types supported by [`TO_JSON`](./to-json.md).
+- If the passed Value is NULL, the Value in the returned JSON object for that
Key-Value pair will be JSON null value.
+- If you want to support other types as values, you can use CAST to convert
them to JSON/String.
+- Doris currently does not deduplicate JSON objects, which means duplicate
keys are allowed. However, duplicate keys may cause unexpected results:
+ 1. Other systems may drop values corresponding to duplicate keys, or
report errors.
+ 2. The result returned by [`JSON_EXTRACT`](./json-extract.md) is uncertain.
-## Usage Notes
+## Return Value
-- By convention, the argument list consists of alternating keys and values.
-- Key arguments are coerced to text.
-- Value arguments are converted as per can convert to json, now we support
array/struct/map/json as value.
-
-## Return Values
-Return a json object. Special cases are as follows:
-* If no parameters are passed, return an empty json object.
-* If the number of parameters passed is odd, return an exception error.
-* If the passed Key is NULL, return an exception error.
-* If the passed Value is NULL, the Value value of the Key-Value pair in the
returned json object is NULL.
+[`JSON`](../../../basic-element/sql-data-types/semi-structured/JSON.md):
Returns a JSON object composed of the parameter list.
## Examples
+1. Case with no parameters
+ ```sql
+ select json_object();
+ ```
+ ```text
+ +---------------+
+ | json_object() |
+ +---------------+
+ | {} |
+ +---------------+
+ ```
+2. Unsupported value types
+ ```sql
+ select json_object('time',curtime());
+ ```
+ ```text
+ ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the
compatibility function signature: to_json(TIMEV2(0))
+ ```
+ Can be converted to String through cast
+ ```sql
+ select json_object('time', cast(curtime() as string));
+ ```
+ ```text
+ +------------------------------------------------+
+ | json_object('time', cast(curtime() as string)) |
+ +------------------------------------------------+
+ | {"time":"17:09:42"} |
+ +------------------------------------------------+
+ ```
+3. Non-String type keys will be converted to String
+ ```sql
+ SELECT json_object(123, 456);
+ ```
+ ```text
+ +-----------------------+
+ | json_object(123, 456) |
+ +-----------------------+
+ | {"123":456} |
+ +-----------------------+
+ ```
+4. Null cannot be used as key
+ ```sql
+ select json_object(null, 456);
+ ```
+ ```text
+ ERROR 1105 (HY000): errCode = 2, detailMessage = json_object key can't be
NULL: json_object(NULL, 456)
+ ```
+ Null can be used as value
+ ```sql
+ select json_object('key', null);
+ ```
+ ```text
+ +--------------------------+
+ | json_object('key', null) |
+ +--------------------------+
+ | {"key":null} |
+ +--------------------------+
+ ```
-```sql
-select json_object();
-```
-```text
-+---------------+
-| json_object() |
-+---------------+
-| {} |
-+---------------+
-```
-```sql
-select json_object('time',curtime());
-```
-```text
-+--------------------------------+
-| json_object('time', curtime()) |
-+--------------------------------+
-| {"time": "10:49:18"} |
-+--------------------------------+
-```
-```sql
-SELECT json_object('id', 87, 'name', 'carrot');
-```
-```text
-+-----------------------------------------+
-| json_object('id', 87, 'name', 'carrot') |
-+-----------------------------------------+
-| {"id": 87, "name": "carrot"} |
-+-----------------------------------------+
-```
-```sql
-select json_object('username',null);
-```
-```text
-+---------------------------------+
-| json_object('username', 'NULL') |
-+---------------------------------+
-| {"username": NULL} |
-+---------------------------------+
-```
-```sql
-select json_object(null,null);
-```
-```text
-ERROR 1105 (HY000): errCode = 2, detailMessage = json_object key can't be
NULL: json_object(NULL)
-```
-
-```sql
--- support array as object value
-SELECT json_object('id', 1, 'level', array('"aaa"','"bbb"'));
-```
-```text
-+------------------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast(array('"aaa"',
'"bbb"') as JSON), '6267') |
-+------------------------------------------------------------------------------------------------------+
-| {"id":1,"level":["\"aaa\"","\"bbb\""]}
|
-+------------------------------------------------------------------------------------------------------+
-```
-```sql
--- support map as object value
-SELECT json_object('id', 1, 'level', map('a', 'b', 'c', 'd'));
-```
-```text
-+------------------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast(map('a', 'b',
'c', 'd') as JSON), '6267') |
-+------------------------------------------------------------------------------------------------------+
-| {"id":1,"level":{"a":"b","c":"d"}}
|
-+------------------------------------------------------------------------------------------------------+
-```
-```sql
--- support struct as object value
-SELECT json_object('id', 1, 'level', named_struct('name', 'a', 'age', 1));
-```
-```text
-+------------------------------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level',
cast(named_struct('name', 'a', 'age', 1) as JSON), '6267') |
-+------------------------------------------------------------------------------------------------------------------+
-| {"id":1,"level":{"name":"a","age":1}}
|
-+------------------------------------------------------------------------------------------------------------------+
-```
-```sql
--- support json as object value
-SELECT json_object('id', 1, 'level', cast('{\"a\":\"b\"}' as JSON));
-```
-```text
-+------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast('{"a":"b"}' as
JSON), '6267') |
-+------------------------------------------------------------------------------------------+
-| {"id":1,"level":{"a":"b"}}
|
-+------------------------------------------------------------------------------------------+
-```
+5. JSON strings can be parsed into JSON objects via
[`JSON_PARSE`](./json-parse.md) before being passed to `JSON_OBJECT`
+ ```sql
+ select json_object(123, json_parse('{"key": "value"}'));
+ ```
+ ```text
+ +--------------------------------------------------+
+ | json_object(123, json_parse('{"key": "value"}')) |
+ +--------------------------------------------------+
+ | {"123":{"key":"value"}} |
+ +--------------------------------------------------+
+ ```
+ Otherwise it will be treated as a string
+ ```sql
+ select json_object(123,'{"key": "value"}');
+ ```
+ ```text
+ +-------------------------------------+
+ | json_object(123,'{"key": "value"}') |
+ +-------------------------------------+
+ | {"123":"{\"key\": \"value\"}"} |
+ +-------------------------------------+
+ ```
+6. Types not supported by [`TO_JSON`](./to-json.md)
+ ```sql
+ select json_object('key', map('abc', 'efg'));
+ ```
+ ```text
+ ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the
compatibility function signature: to_json(MAP<VARCHAR(3),VARCHAR(3)>)
+ ```
+ Can be converted to JSON via CAST statement before passing in:
+ ```sql
+ select json_object('key', cast(map('abc', 'efg') as json));
+ ```
+ ```text
+ +-----------------------------------------------------+
+ | json_object('key', cast(map('abc', 'efg') as json)) |
+ +-----------------------------------------------------+
+ | {"key":{"abc":"efg"}} |
+ +-----------------------------------------------------+
+ ```
+7. Case with duplicate keys
+ ```sql
+ select
+ json_object('key', 123, 'key', 4556) v1
+ , jsonb_extract(json_object('key', 123, 'key', 4556), '$.key') v2
+ , jsonb_extract(json_object('key', 123, 'key', 4556), '$.*') v3;
+ ```
+ ```text
+ +------------------------+------+------------+
+ | v1 | v2 | v3 |
+ +------------------------+------+------------+
+ | {"key":123,"key":4556} | 123 | [123,4556] |
+ +------------------------+------+------------+
+ ```
\ No newline at end of file
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
index 8eea0778505..35da7e1fc78 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-object.md
@@ -7,124 +7,147 @@
## 描述
-生成一个或者多个包含指定 Key-Value 对的 json object, 当 Key 值为 NULL 或者传入参数为奇数个时，返回异常错误。
+生成一个包含指定 Key-Value 对的 json object, 当 Key 值为 NULL 或者传入参数为奇数个时，返回异常错误。
## 语法
```sql
-JSON_OBJECT (<key>, <value>[,<key>, <value>, ...])
+JSON_OBJECT (<key>, <value>[, <key>, <value>, ...])
```
## 参数
-
-| 参数 | 描述 |
-|---------|------------------------------------------|
-| `<key>` | 指定生成的 json object 的 Key-Value 中的 Key 值 |
-| `<value>` | 指定生成的 json object 的 Key-Value 中的 Value 值 |
+### 可变参数：
+- <key> String 类型
+- <value> 多种类型，Doris 会自动将非 Json 类型的参数通过 [`TO_JSON`](./to-json.md) 函数将其转换为 Json
类型。
## 注意事项
-
+- 参数数量必须为偶数个，可以是 0 个参数（返回一个空的 Json 对象）。
- 按照惯例，参数列表由交替的键和值组成。
- Key 按照JSON 定义强制转换为文本。
-- Value 参数按照可以转换为 json 的方式进行转换，现在我们支持 array/struct/map/json 作为值
+- 如果传入的 Key 为 NULL，返回异常错误。
+- Value 参数按照可以转换为 json 的方式进行转换，必须是 [`TO_JSON`](./to-json.md) 支持的类型。
+- 如果传入的 Value 为 NULL，返回的 json object 中该 Key-Value 对的 Value 值为 Json 的 null 值。
+- 如果支持其他类型的作为 value，可以使用 CAST 将其转换为 Json/String.
+- Doris 目前没有对 Json 对象去重，也就是允许重复的 key。但是重复 key 可能会引起意外的结果:
+ 1. 其他系统中可能会丢掉重复的 key 对应的值，或者报错。
+ 2. [`JSON_EXTRACT`](./json-extract.md) 返回的结果不确定。
## 返回值
-返回一个 json object。特殊情况如下：
-* 如果没有传入参数，返回一个空的 json object。
-* 如果传入的参数个数为奇数个，返回异常错误。
-* 如果传入的 Key 为 NULL，返回异常错误。
-* 如果传入的 Value 为 NULL，返回的 json object 中该 Key-Value 对的 Value 值为 NULL。
+[`JSON`](../../../basic-element/sql-data-types/semi-structured/JSON.md)：
返回由参数列表组成的 JSON 对象。
## 示例
+1. 没有参数的情况
+ ```sql
+ select json_object();
+ ```
+ ```text
+ +---------------+
+ | json_object() |
+ +---------------+
+ | {} |
+ +---------------+
+ ```
+2. 不支持的 value 类型
+ ```sql
+ select json_object('time',curtime());
+ ```
+ ```text
+ ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the
compatibility function signature: to_json(TIMEV2(0))
+ ```
+ 可以通过 cast 将其转换为 String
+ ```sql
+ select json_object('time', cast(curtime() as string));
+ ```
+ ```text
+ +------------------------------------------------+
+ | json_object('time', cast(curtime() as string)) |
+ +------------------------------------------------+
+ | {"time":"17:09:42"} |
+ +------------------------------------------------+
+ ```
+3. 非 String 类型的 key 会被转换为 String
+ ```sql
+ SELECT json_object(123, 456);
+ ```
+ ```text
+ +-----------------------+
+ | json_object(123, 456) |
+ +-----------------------+
+ | {"123":456} |
+ +-----------------------+
+ ```
+4. Null 不能作为 key
+ ```sql
+ select json_object(null, 456);
+ ```
+ ```text
+ ERROR 1105 (HY000): errCode = 2, detailMessage = json_object key can't be
NULL: json_object(NULL, 456)
+ ```
+ Null 可以做为 value
+ ```sql
+ select json_object('key', null);
+ ```
+ ```text
+ +--------------------------+
+ | json_object('key', null) |
+ +--------------------------+
+ | {"key":null} |
+ +--------------------------+
+ ```
-```sql
-select json_object();
-```
-```text
-+---------------+
-| json_object() |
-+---------------+
-| {} |
-+---------------+
-```
-```sql
-select json_object('time',curtime());
-```
-```text
-+--------------------------------+
-| json_object('time', curtime()) |
-+--------------------------------+
-| {"time": "10:49:18"} |
-+--------------------------------+
-```
-```sql
-SELECT json_object('id', 87, 'name', 'carrot');
-```
-```text
-+-----------------------------------------+
-| json_object('id', 87, 'name', 'carrot') |
-+-----------------------------------------+
-| {"id": 87, "name": "carrot"} |
-+-----------------------------------------+
-```
-```sql
-select json_object('username',null);
-```
-```text
-+---------------------------------+
-| json_object('username', 'NULL') |
-+---------------------------------+
-| {"username": NULL} |
-+---------------------------------+
-```
-```sql
-select json_object(null,null);
-```
-```text
-ERROR 1105 (HY000): errCode = 2, detailMessage = json_object key can't be
NULL: json_object(NULL)
-```
-```sql
--- support array as object value
-SELECT json_object('id', 1, 'level', array('"aaa"','"bbb"'));
-```
-```text
-+------------------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast(array('"aaa"',
'"bbb"') as JSON), '6267') |
-+------------------------------------------------------------------------------------------------------+
-| {"id":1,"level":["\"aaa\"","\"bbb\""]}
|
-+------------------------------------------------------------------------------------------------------+
-```
-```sql
--- support map as object value
-SELECT json_object('id', 1, 'level', map('a', 'b', 'c', 'd'));
-```
-```text
-+------------------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast(map('a', 'b',
'c', 'd') as JSON), '6267') |
-+------------------------------------------------------------------------------------------------------+
-| {"id":1,"level":{"a":"b","c":"d"}}
|
-+------------------------------------------------------------------------------------------------------+
-```
-```sql
--- support struct as object value
-SELECT json_object('id', 1, 'level', named_struct('name', 'a', 'age', 1));
-```
-```text
-+------------------------------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level',
cast(named_struct('name', 'a', 'age', 1) as JSON), '6267') |
-+------------------------------------------------------------------------------------------------------------------+
-| {"id":1,"level":{"name":"a","age":1}}
|
-+------------------------------------------------------------------------------------------------------------------+
-```
-```sql
--- support json as object value
-SELECT json_object('id', 1, 'level', cast('{\"a\":\"b\"}' as JSON));
-```
-```text
-+------------------------------------------------------------------------------------------+
-| json_object('id', cast(1 as VARCHAR(65533)), 'level', cast('{"a":"b"}' as
JSON), '6267') |
-+------------------------------------------------------------------------------------------+
-| {"id":1,"level":{"a":"b"}}
|
-+------------------------------------------------------------------------------------------+
-```
+5. Json 字符串可以通过 [`JSON_PARSE`](./json-parse.md) 解析为 Json 对象后传入到 `JSON_OBJECT`
+ ```sql
+ select json_object(123, json_parse('{"key": "value"}'));
+ ```
+ ```text
+ +--------------------------------------------------+
+ | json_object(123, json_parse('{"key": "value"}')) |
+ +--------------------------------------------------+
+ | {"123":{"key":"value"}} |
+ +--------------------------------------------------+
+ ```
+ 否则将作为字符串处理
+ ```sql
+ select json_object(123,'{"key": "value"}');
+ ```
+ ```text
+ +-------------------------------------+
+ | json_object(123,'{"key": "value"}') |
+ +-------------------------------------+
+ | {"123":"{\"key\": \"value\"}"} |
+ +-------------------------------------+
+ ```
+6. [`TO_JSON`](./to-json.md) 不支持的类型
+ ```sql
+ select json_object('key', map('abc', 'efg'));
+ ```
+ ```text
+ ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the
compatibility function signature: to_json(MAP<VARCHAR(3),VARCHAR(3)>)
+ ```
+
+ 可以通过 CAST 语句将其转换为 Json 再传入：
+ ```sql
+ select json_object('key', cast(map('abc', 'efg') as json));
+ ```
+ ```text
+ +-----------------------------------------------------+
+ | json_object('key', cast(map('abc', 'efg') as json)) |
+ +-----------------------------------------------------+
+ | {"key":{"abc":"efg"}} |
+ +-----------------------------------------------------+
+ ```
+7. 重复 key 的情况
+ ```sql
+ select
+ json_object('key', 123, 'key', 4556) v1
+ , jsonb_extract(json_object('key', 123, 'key', 4556), '$.key') v2
+ , jsonb_extract(json_object('key', 123, 'key', 4556), '$.*') v3;
+ ```
+ ```text
+ +------------------------+------+------------+
+ | v1 | v2 | v3 |
+ +------------------------+------+------------+
+ | {"key":123,"key":4556} | 123 | [123,4556] |
+ +------------------------+------+------------+
+ ```
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
