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 e75595c48a2 update json_extract (#2632)
e75595c48a2 is described below
commit e75595c48a2be643f6f2468a654a2f985c5b5784
Author: Jerry Hu <[email protected]>
AuthorDate: Fri Jul 18 11:44:58 2025 +0800
update json_extract (#2632)
## Versions
- [x] dev
- [ ] 3.0
- [ ] 2.1
- [ ] 2.0
---
.../json-functions/json-extract.md | 324 ++++++++++-----------
.../json-functions/json-extract.md | 321 ++++++++++----------
2 files changed, 309 insertions(+), 336 deletions(-)
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
b/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
index 5d495bb58fc..89caa880fc5 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
@@ -6,182 +6,168 @@
---
## Description
-JSON_EXTRACT is a series of functions that extract the field specified by
json_path from JSON data and provide different series of functions according to
the type of the field to be extracted.
-
-* JSON_EXTRACT returns the VARCHAR type for a json string of the VARCHAR type.
-* JSON_EXTRACT_NO_QUOTES returns the VARCHAR type for a json string of the
VARCHAR type, if the value of a JSON field is a string, the double quotes will
be removed.
-* JSON_EXTRACT_ISNULL returns the BOOLEAN type indicating whether it is a json
null.
-* JSON_EXTRACT_BOOL returns the BOOLEAN type.
-* JSON_EXTRACT_INT returns the INT type.
-* JSON_EXTRACT_BIGINT returns the BIGINT type.
-* JSON_EXTRACT_LARGEINT returns the LARGEINT type.
-* JSON_EXTRACT_DOUBLE returns the DOUBLE type.
-* JSON_EXTRACT_STRING returns the STRING type.
-
-:::tip
-The `JSON_EXTRACT_NO_QUOTES` function is supported since version 3.0.6.
-:::
-
-## Alias
-* JSONB_EXTRACT is the same as JSON_EXTRACT.
-* JSON_EXTRACT_NO_QUOTES is the same as JSON_EXTRACT_NO_QUOTES.
-* JSONB_EXTRACT_ISNULL is the same as JSON_EXTRACT_ISNULL.
-* JSONB_EXTRACT_BOOL is the same as JSON_EXTRACT_BOOL.
-* JSONB_EXTRACT_INT is the same as JSON_EXTRACT_INT.
-* JSONB_EXTRACT_BIGINT is the same as JSON_EXTRACT_BIGINT.
-* JSONB_EXTRACT_LARGEINT is the same as JSON_EXTRACT_LARGEINT.
-* JSONB_EXTRACT_DOUBLE is the same as JSON_EXTRACT_DOUBLE.
-* JSONB_EXTRACT_STRING is the same as JSON_EXTRACT_STRING.
+Extract the field specified by json_path from JSON type data.
## Syntax
```sql
-JSON_EXTRACT (<json_str>, <path>[, path] ...)
-```
-```sql
-JSON_EXTRACT_NO_QUOTES (<json_str>, <path>[, path] ...)
-```
-```sql
-JSON_EXTRACT_ISNULL (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_BOOL (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_INT (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_BIGINT (<json_str>, <path>)
+JSON_EXTRACT (<json_object>, <path>[, <path2>, ...])
```
-```sql
-JSON_EXTRACT_LARGEINT (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_DOUBLE (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_STRING (<json_str>, <path>)
-```
-Alias functions have the same syntax and usage as the above functions, except
for the function names.
-
## Parameters
-| Parameter | Description |
-|--------------|-----------------------------|
-| `<json_str>` | The JSON-type parameter or field to be extracted. |
-| `<path>` | The JSON path to extract the target element from the target
JSON. |
-json path syntax:
-- '$' for json document root
-- '.k1' for element of json object with key 'k1'
- - If the key column value contains ".", double quotes are required in
json_path, For example: SELECT json_extract('{"k1.a":"abc","k2":300}',
'$."k1.a"');
-- '[i]' for element of json array at index i
- - Use '$[last]' to get the last element of json_array, and '$[last-1]' to
get the penultimate element, and so on.
-
-## Return Values
-According to the type of the field to be extracted, return the data type of
the specified JSON_PATH in the target JSON. Special case handling is as follows:
-* If the field specified by json_path does not exist in the JSON, return NULL.
-* If the actual type of the field specified by json_path in the JSON is
inconsistent with the type specified by json_extract_t.
-* if it can be losslessly converted to the specified type, return the
specified type t; if not, return NULL.
+### Required Parameters:
+- `<json_object>`: The JSON type expression to extract from.
+- `<path>`: The JSON path to extract the target element from the target JSON.
+### Optional/Variable Parameters
+- `<path2>` Multiple path values can be extracted from the JSON object.
+## Return Value
+- `Nullable(JSON)`: Returns the JSON element pointed to by `<path>`. If
multiple results are matched, they will be returned as a JSON array.
+## Usage Notes
+- If `<json_object>` is NULL, or `<path>` is NULL, returns NULL.
+- For single `<path>` parameter cases, if the `<path>` does not exist, returns
NULL.
+- For multiple `<path>` parameter cases, non-existent paths are ignored, and
matched elements are returned as a JSON array. If no matches are found, returns
NULL.
+- If `<path>` is not a valid path, an error is reported.
+- If the value corresponding to `<path>` is a string, the returned string will
be surrounded by double quotes (`"`). To get results without double quotes,
please use the function [`JSON_UNQUOTE`](./json-unquote.md).
+- The syntax of `<path>` is as follows:
+ * `$` represents the json root
+ * `.k1` represents the element with key `k1` in the json object
+ * If the key value contains ".", `<path>` needs to use double quotes,
for example `SELECT json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"')`;
+ * `[i]` represents the element at index i in the json array
+ * To get the last element of json_array, you can use `$[last]`, the
second to last element can use `$[last-1]`, and so on.
+ * `*` represents a wildcard, where `$.*` represents all members of the
root object, and `$[*]` represents all elements of the array.
+- If `<path>` contains wildcards (`*`), the matching results will be returned
in array form.
## Examples
-```sql
-SELECT json_extract('{"id": 123, "name": "doris"}', '$.id');
-```
+1. General parameters
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1');
+ ```
+ ```
+ +-----------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1') |
+ +-----------------------------------------------+
+ | "v31" |
+ +-----------------------------------------------+
+ ```
+> Note: The returned result is `"v31"` not `v31`.
+2. NULL parameters
+ ```sql
+ select JSON_EXTRACT(null, '$.k1');
+ ```
+ ```
+ +----------------------------+
+ | JSON_EXTRACT(null, '$.k1') |
+ +----------------------------+
+ | NULL |
+ +----------------------------+
+ ```
+3. `<path>` is NULL
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', NULL);
+ ```
+ ```
+ +---------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":300}', NULL) |
+ +---------------------------------------------+
+ | NULL |
+ +---------------------------------------------+
+ ```
+4. Multi-level path
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}',
'$.k2.sub_key');
+ ```
+ ```
+ +------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}', '$.k2.sub_key') |
+ +------------------------------------------------------------------------+
+ | 1234.56 |
+ +------------------------------------------------------------------------+
+ ```
+5. Array path
+ ```sql
+ SELECT JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$[2]');
+ ```
+ ```
+ +----------------------------------------------------------------------+
+ | JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$.[2]') |
+ +----------------------------------------------------------------------+
+ | "2025-07-16 18:35:25" |
+ +----------------------------------------------------------------------+
+ ```
+6. Non-existent path
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3');
+ ```
+ ```
+ +-----------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3') |
+ +-----------------------------------------------+
+ | NULL |
+ +-----------------------------------------------+
+ ```
+7. Multiple path parameters
+ ```sql
+ select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id',
'$.not_exists');
+ ```
+ ```
+
+--------------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id',
'$.not_exists') |
+
+--------------------------------------------------------------------------------+
+ | ["doris",123]
|
+
+--------------------------------------------------------------------------------+
+ ```
+ > Even if there is only one match, it will be returned in array form
+ ```sql
+ select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2',
'$.not_exists');
+ ```
+ ```
+
+---------------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2',
'$.not_exists') |
+
+---------------------------------------------------------------------------------+
+ | ["doris"]
|
+
+---------------------------------------------------------------------------------+
+ ```
+ > If all paths have no matches, return NULL
+ ```sql
+ select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2',
'$.not_exists');
+ ```
+ ```
+
+------------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2',
'$.not_exists') |
+
+------------------------------------------------------------------------------+
+ | NULL
|
+
+------------------------------------------------------------------------------+
+ ```
-```text
-+------------------------------------------------------+
-| json_extract('{"id": 123, "name": "doris"}', '$.id') |
-+------------------------------------------------------+
-| 123 |
-+------------------------------------------------------+
-```
-```sql
-SELECT json_extract('[1, 2, 3]', '$.[1]');
-```
-```text
-+------------------------------------+
-| json_extract('[1, 2, 3]', '$.[1]') |
-+------------------------------------+
-| 2 |
-+------------------------------------+
-```
-```sql
-SELECT json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }',
'$.k1', '$.k2.k21', '$.k2.k22', '$.k2.k22[1]');
-```
-```text
-+-------------------------------------------------------------------------------------------------------------------+
-| json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }', '$.k1',
'$.k2.k21', '$.k2.k22', '$.k2.k22[1]') |
-+-------------------------------------------------------------------------------------------------------------------+
-| ["v1",6.6,[1,2],2]
|
-+-------------------------------------------------------------------------------------------------------------------+
-```
-```sql
-SELECT json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name');
-```
-```text
-+-----------------------------------------------------------------+
-| json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name') |
-+-----------------------------------------------------------------+
-| [null,"doris"] |
-+-----------------------------------------------------------------+
-```
-```sql
-SELECT json_extract_no_quotes('{"id": 123, "name": "doris"}', '$.name');
-```
-```text
-+------------------------------------------------------------------+
-| json_extract_no_quotes('{"id": 123, "name": "doris"}', '$.name') |
-+------------------------------------------------------------------+
-| doris |
-+------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_ISNULL('{"id": 123, "name": "doris"}', '$.id');
-```
-```text
-+----------------------------------------------------------------------------+
-| jsonb_extract_isnull(cast('{"id": 123, "name": "doris"}' as JSON), '$.id') |
-+----------------------------------------------------------------------------+
-| 0 |
-+----------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_BOOL('{"id": 123, "name": "NULL"}', '$.id');
-```
-```text
-+-------------------------------------------------------------------------+
-| jsonb_extract_bool(cast('{"id": 123, "name": "NULL"}' as JSON), '$.id') |
-+-------------------------------------------------------------------------+
-| NULL |
-+-------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_INT('{"id": 123, "name": "NULL"}', '$.id');
-```
-```text
-+------------------------------------------------------------------------+
-| jsonb_extract_int(cast('{"id": 123, "name": "NULL"}' as JSON), '$.id') |
-+------------------------------------------------------------------------+
-| 123 |
-+------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_INT('{"id": 123, "name": "doris"}', '$.name');
-```
-```text
-+---------------------------------------------------------------------------+
-| jsonb_extract_int(cast('{"id": 123, "name": "doris"}' as JSON), '$.name') |
-+---------------------------------------------------------------------------+
-| NULL |
-+---------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_STRING('{"id": 123, "name": "doris"}', '$.name');
-```
-```text
-+------------------------------------------------------------------------------+
-| jsonb_extract_string(cast('{"id": 123, "name": "doris"}' as JSON), '$.name')
|
-+------------------------------------------------------------------------------+
-| doris
|
-+------------------------------------------------------------------------------+
-```
+8. Wildcard path
+ ```sql
+ select json_extract('{"k": [1,2,3,4,5]}', '$.k[*]');
+ ```
+ ```
+ +----------------------------------------------+
+ | json_extract('{"k": [1,2,3,4,5]}', '$.k[*]') |
+ +----------------------------------------------+
+ | [1,2,3,4,5] |
+ +----------------------------------------------+
+ ```
+ ```sql
+ select json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}',
'$.*', '$.k3.k4');
+ ```
+ ```
+
+---------------------------------------------------------------------------------------+
+ | json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}',
'$.*', '$.k3.k4') |
+
+---------------------------------------------------------------------------------------+
+ | [[1,2,3,4,5],"abc",{"k4":"v4"},"v4"]
|
+
+---------------------------------------------------------------------------------------+
+ ```
+9. Value is NULL case
+ ```sql
+ select JSON_EXTRACT('{"id": 123, "name": null}', '$.name') v,
JSON_EXTRACT('{"id": 123, "name": null}', '$.name') is null v2;
+ ```
+ ```
+ +------+------+
+ | v | v2 |
+ +------+------+
+ | null | 0 |
+ +------+------+
+ ```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
index f599d338945..b60b0f29527 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/json-functions/json-extract.md
@@ -6,181 +6,168 @@
---
## 描述
-JSON_EXTRACT 是一系列函数，从 JSON 类型的数据中提取 json_path 指定的字段，根据要提取的字段类型不同提供不同的系列函数。
-* JSON_EXTRACT 对 VARCHAR 类型的 json string 返回 VARCHAR 类型
-* JSON_EXTRACT_NO_QUOTES 对 VARCHAR 类型的 json string 返回 VARCHAR 类型, 如果 json 字段值为
string，则将去掉双引号。
-* JSON_EXTRACT_ISNULL 返回是否为 json null 的 BOOLEAN 类型
-* JSON_EXTRACT_BOOL 返回 BOOLEAN 类型
-* JSON_EXTRACT_INT 返回 INT 类型
-* JSON_EXTRACT_BIGINT 返回 BIGINT 类型
-* JSON_EXTRACT_LARGEINT 返回 LARGEINT 类型
-* JSON_EXTRACT_DOUBLE 返回 DOUBLE 类型
-* JSON_EXTRACT_STRING 返回 STRING 类型
-
-:::tip
-JSON_EXTRACT_NO_QUOTES 函数自 3.0.6 版本开始支持.
-:::
-
-## 别名
-* JSONB_EXTRACT 同 `JSON_EXTRACT`
-* JSON_EXTRACT_NO_QUOTES 同 `JSON_EXTRACT_NO_QUOTES`
-* JSONB_EXTRACT_ISNULL 同 `JSON_EXTRACT_ISNULL`
-* JSONB_EXTRACT_BOOL 同 `JSON_EXTRACT_BOOL`
-* JSONB_EXTRACT_INT 同 `JSON_EXTRACT_INT`
-* JSONB_EXTRACT_BIGINT 同 `JSON_EXTRACT_BIGINT`
-* JSONB_EXTRACT_LARGEINT 同 `JSON_EXTRACT_LARGEINT`
-* JSONB_EXTRACT_DOUBLE 同 `JSON_EXTRACT_DOUBLE`
-* JSONB_EXTRACT_STRING 同 `JSON_EXTRACT_STRING`
+从 JSON 类型的数据中提取 json_path 指定的字段。
## 语法
```sql
-JSON_EXTRACT (<json_str>, <path>[, path] ...)
-```
-```sql
-JSON_EXTRACT_NO_QUOTES (<json_str>, <path>[, path] ...)
-```
-```sql
-JSON_EXTRACT_ISNULL (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_BOOL (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_INT (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_BIGINT (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_LARGEINT (<json_str>, <path>)
-```
-```sql
-JSON_EXTRACT_DOUBLE (<json_str>, <path>)
+JSON_EXTRACT (<json_object>, <path>[, <path2>, ...])
```
-```sql
-JSON_EXTRACT_STRING (<json_str>, <path>)
-```
-别名函数除函数名称之外，语法和用法与上述函数保持一致。
-
## 参数
-| 参数 | 描述 |
-|--------------|-----------------------------|
-| `<json_str>` | 要提取的 JSON 类型的参数或者字段 |
-| `<path>` | 要从目标 JSON 中提取目标元素的 JSON 路径。 |
-
-`json_path` 的语法如下
-* '$' 代表 json root
-* '.k1' 代表 json object 中 key 为'k1'的元素
- - 如果 key 列值包含 ".", json_path 中需要用双引号，例如 `SELECT
json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"');`
-* '[i]' 代表 json array 中下标为 i 的元素
- - 获取 json_array 的最后一个元素可以用'$[last]'，倒数第二个元素可以用'$[last-1]'，以此类推。
+### 必须参数：
+- `<json_object>`: 要提取的 JSON 类型的表达式。
+- `<path>`: 要从目标 JSON 中提取目标元素的 JSON 路径。
+### 可选/可变参数
+- `<path2>` 可以从 JSON 对象中提取多个路径的值。
## 返回值
-根据要提取的字段类型不同，返回目标 JSON 中 指定 JSON_PATH 的数据类型。特殊情况处理如下：
-* 如果 json_path 指定的字段在 JSON 中不存在，返回 NULL
-* 如果 json_path 指定的字段在 JSON 中的实际类型和 json_extract_t 指定的类型不一致，如果能无损转换成指定类型返回指定类型
t，如果不能则返回 NULL
+- `Nullable(JSON)`: 返回 `<path>` 指向的 JSON 元素，如果匹配了多个结果会以 JSON 数组的形式返回。
+## 使用说明
+- 如果 `<json_object>` 是 NULL，或者 `<path>` 是 NULL，返回 NULL。
+- 对于单个 `<path>` 参数的情况，如果 `<path>` 如果路径不存在，返回 NULL。
+- 对于多个 `<path>` 参数的情况，不存在的路径会被忽略，匹配到的元素组成 JSON 数组返回。如果一个匹配也没有则返回 NULL。
+- 如果 `<path>` 不是一个合法的路径，报错。
+- 如果 `<path>` 对应的值是字符串，返回的字符串会被双引号（`"`）包围，如果要得到没有双引号的结果，请对结果使用函数
[`JSON_UNQUOTE`](./json-unquote.md) 以去掉双引号。
+- `<path>` 的语法如下
+ * `$` 代表 json root
+ * `.k1` 代表 json object 中 key 为`k1`的元素
+ * 如果 key 列值包含 ".", `<path>` 中需要用双引号，例如 `SELECT
json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"')`;
+ * `[i]` 代表 json array 中下标为 i 的元素
+ * 获取 json_array 的最后一个元素可以用`$[last]`，倒数第二个元素可以用`$[last-1]`，以此类推。
+ * `*` 代表通配符，即 `$.*` 代表根对象的所有成员，`$[*]` 代表数组的所有元素。
+- 如果 `<path>` 存在通配符(`*`)，匹配的结果会以数组形式返回。
## 示例
+1. 一般参数
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1');
+ ```
+ ```
+ +-----------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1') |
+ +-----------------------------------------------+
+ | "v31" |
+ +-----------------------------------------------+
+ ```
+> 注意：返回的结果是`"v31"` 不是 `v31`。
+2. NULL 参数
+ ```sql
+ select JSON_EXTRACT(null, '$.k1');
+ ```
+ ```
+ +----------------------------+
+ | JSON_EXTRACT(null, '$.k1') |
+ +----------------------------+
+ | NULL |
+ +----------------------------+
+ ```
+3. `<path>` 为 NULL
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', NULL);
+ ```
+ ```
+ +---------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":300}', NULL) |
+ +---------------------------------------------+
+ | NULL |
+ +---------------------------------------------+
+ ```
+4. 多级路径
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}',
'$.k2.sub_key');
+ ```
+ ```
+ +------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}', '$.k2.sub_key') |
+ +------------------------------------------------------------------------+
+ | 1234.56 |
+ +------------------------------------------------------------------------+
+ ```
+5. 数组路径
+ ```sql
+ SELECT JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$[2]');
+ ```
+ ```
+ +----------------------------------------------------------------------+
+ | JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$.[2]') |
+ +----------------------------------------------------------------------+
+ | "2025-07-16 18:35:25" |
+ +----------------------------------------------------------------------+
+ ```
+6. 不存在的 path
+ ```sql
+ SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3');
+ ```
+ ```
+ +-----------------------------------------------+
+ | JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3') |
+ +-----------------------------------------------+
+ | NULL |
+ +-----------------------------------------------+
+ ```
+7. 多个路径参数
+ ```sql
+ select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id',
'$.not_exists');
+ ```
+ ```
+
+--------------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id',
'$.not_exists') |
+
+--------------------------------------------------------------------------------+
+ | ["doris",123]
|
+
+--------------------------------------------------------------------------------+
+ ```
+ > 即使只有一个匹配也会以数组形式返回
+ ```sql
+ select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2',
'$.not_exists');
+ ```
+ ```
+
+---------------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2',
'$.not_exists') |
+
+---------------------------------------------------------------------------------+
+ | ["doris"]
|
+
+---------------------------------------------------------------------------------+
+ ```
+ ```sql
+ > 如果所有路径都没有匹配则返回 NULL
+ select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2',
'$.not_exists');
+ ```
+ ```
+
+------------------------------------------------------------------------------+
+ | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2',
'$.not_exists') |
+
+------------------------------------------------------------------------------+
+ | NULL
|
+
+------------------------------------------------------------------------------+
+ ```
-```sql
-SELECT json_extract('{"id": 123, "name": "doris"}', '$.id');
-```
-
-```text
-+------------------------------------------------------+
-| json_extract('{"id": 123, "name": "doris"}', '$.id') |
-+------------------------------------------------------+
-| 123 |
-+------------------------------------------------------+
-```
-```sql
-SELECT json_extract('[1, 2, 3]', '$.[1]');
-```
-```text
-+------------------------------------+
-| json_extract('[1, 2, 3]', '$.[1]') |
-+------------------------------------+
-| 2 |
-+------------------------------------+
-```
-```sql
-SELECT json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }',
'$.k1', '$.k2.k21', '$.k2.k22', '$.k2.k22[1]');
-```
-```text
-+-------------------------------------------------------------------------------------------------------------------+
-| json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }', '$.k1',
'$.k2.k21', '$.k2.k22', '$.k2.k22[1]') |
-+-------------------------------------------------------------------------------------------------------------------+
-| ["v1",6.6,[1,2],2]
|
-+-------------------------------------------------------------------------------------------------------------------+
-```
-```sql
-SELECT json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name');
-```
-```text
-+-----------------------------------------------------------------+
-| json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name') |
-+-----------------------------------------------------------------+
-| [null,"doris"] |
-+-----------------------------------------------------------------+
-```
-```sql
-SELECT json_extract_no_quotes('{"id": 123, "name": "doris"}', '$.name');
-```
-```text
-+------------------------------------------------------------------+
-| json_extract_no_quotes('{"id": 123, "name": "doris"}', '$.name') |
-+------------------------------------------------------------------+
-| doris |
-+------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_ISNULL('{"id": 123, "name": "doris"}', '$.id');
-```
-```text
-+----------------------------------------------------------------------------+
-| jsonb_extract_isnull(cast('{"id": 123, "name": "doris"}' as JSON), '$.id') |
-+----------------------------------------------------------------------------+
-| 0 |
-+----------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_BOOL('{"id": 123, "name": "NULL"}', '$.id');
-```
-```text
-+-------------------------------------------------------------------------+
-| jsonb_extract_bool(cast('{"id": 123, "name": "NULL"}' as JSON), '$.id') |
-+-------------------------------------------------------------------------+
-| NULL |
-+-------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_INT('{"id": 123, "name": "NULL"}', '$.id');
-```
-```text
-+------------------------------------------------------------------------+
-| jsonb_extract_int(cast('{"id": 123, "name": "NULL"}' as JSON), '$.id') |
-+------------------------------------------------------------------------+
-| 123 |
-+------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_INT('{"id": 123, "name": "doris"}', '$.name');
-```
-```text
-+---------------------------------------------------------------------------+
-| jsonb_extract_int(cast('{"id": 123, "name": "doris"}' as JSON), '$.name') |
-+---------------------------------------------------------------------------+
-| NULL |
-+---------------------------------------------------------------------------+
-```
-```sql
-SELECT JSON_EXTRACT_STRING('{"id": 123, "name": "doris"}', '$.name');
-```
-```text
-+------------------------------------------------------------------------------+
-| jsonb_extract_string(cast('{"id": 123, "name": "doris"}' as JSON), '$.name')
|
-+------------------------------------------------------------------------------+
-| doris
|
-+------------------------------------------------------------------------------+
-```
+8. 通配符路径
+ ```sql
+ select json_extract('{"k": [1,2,3,4,5]}', '$.k[*]');
+ ```
+ ```
+ +----------------------------------------------+
+ | json_extract('{"k": [1,2,3,4,5]}', '$.k[*]') |
+ +----------------------------------------------+
+ | [1,2,3,4,5] |
+ +----------------------------------------------+
+ ```
+ ```sql
+ select json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}',
'$.*', '$.k3.k4');
+ ```
+ ```
+
+---------------------------------------------------------------------------------------+
+ | json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}',
'$.*', '$.k3.k4') |
+
+---------------------------------------------------------------------------------------+
+ | [[1,2,3,4,5],"abc",{"k4":"v4"},"v4"]
|
+
+---------------------------------------------------------------------------------------+
+ ```
+9. Value 是 NULL 的情况
+ ```sql
+ select JSON_EXTRACT('{"id": 123, "name": null}', '$.name') v,
JSON_EXTRACT('{"id": 123, "name": null}', '$.name') is null v2;
+ ```
+ ```
+ +------+------+
+ | v | v2 |
+ +------+------+
+ | null | 0 |
+ +------+------+
+ ```
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
