--- title: "JSON_TABLE 函数 - OceanBase 数据库 V4.6.0 | OceanBase 文档中心" description: JSON_TABLE 函数 描述 JSON_TABLE() 函数子级路径（path），可以从嵌套的 JSON 数据中提取所需信息。 JSON_TABLE() 函数用于将 JSON 数据从半结构化数据转为结构化数据，即从 JSON 文档中提取数据并返回具有指定列的关系表。通过将 JSON 数据的各个部分映射到一个虚拟表… --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.6.0 # JSON_TABLE 函数 更新时间：2026-07-15 15:16:39 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.6.0/zh-CN/700.reference/500.sql-reference/100.sql-syntax/200.common-tenant-of-mysql-mode/400.functions-of-mysql-mode/700.json-functions-of-mysql-mode/450.json-table-functions-of-mysql-mode.md) ## 描述 `JSON_TABLE()` 函数子级路径（path），可以从嵌套的 JSON 数据中提取所需信息。 `JSON_TABLE()` 函数用于将 JSON 数据从半结构化数据转为结构化数据，即从 JSON 文档中提取数据并返回具有指定列的关系表。通过将 JSON 数据的各个部分映射到一个虚拟表的行和列，它创建了一个清晰的表结构，这不仅便于执行查询操作，还允许用户将这些数据插入到新的表或视图中以进行进一步处理。 `JSON_TABLE()` 为每一个 JSON 值提供了列输出，对于 JSON 内的数组，也提供了多行（列）输出。 ## 语法 ```sql JSON_TABLE(expr, path_literal COLUMNS (column_list)) [AS] alias column_list: column[, column...] column: column_name FOR ORDINALITY | column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause] | column_name data_type EXISTS PATH path_literal | NESTED [PATH] path_literal COLUMNS (column_list) json_value_on_empty_clause: {NULL | DEFAULT json_string | ERROR} ON EMPTY json_value_on_error_clause: {NULL | DEFAULT json_string | ERROR} ON ERROR ``` ## 说明 `JSON_TABLE()` 必须用在 `SELECT` 语句的 `FROM` 子句中。 `JSON_TABLE()` 函数语法说明如下： - `expr`: 指定转换为 JSON 数据的输入表达式，如果表达式不可以转成 JSON 数据则会报错。 - `path_literal`：指定 Path 路径的字符串，输入其他类型数据或者非正确的 Path 路径信息则会报错。 - `column_list`：指定所输入的列定义信息，在表达式中至少包含一个列定义参数，否则会报错。 - `column`：指定具体的列定义信息，具体可以分为如下四类： - `column_name FOR ORDINALITYFOR` 子句用于为当前输出行提供行号，其中 `column_name` 是 `INT` 类型的列。 - `column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]` 子句用于提取 `path_literal` 指定的数据为 JSON 并强制转换为列类型。如果缺失值会触发可选的 `json_value_on_empty_clause` 子句。 - `column_name data_type EXISTS PATH path_literal` 子句：如果 `path_literal` 指定的位置存在任何数据，则此列返回 1，否则返回 0。 - `NESTED [PATH] path_literal COLUMNS (column_list)` 子句表示重复的嵌套结构，可以在其中继续定义 `column` 的四种列类型。 - `data_type`：指定数据类型，当前支持 OceanBase 数据库 MySQL 模式下除 `ENUM` 和 `SET` 之外的所有数据类型。 - `alias`：指定表别名。 - `json_value_on_empty_clause`：如果经过 Path 筛选的数据是空值时用户可选的预期行为，可选行为包含三种：`NULL`、`ERROR` 和默认值 `DEFAULT`。 - `NULL ON EMPTY`：将该列设置为 `NULL`，为默认行为。 - `DEFAULT json_string`：将 `json_string` 解析为默认值取代 JSON 对象或者数组。OceanBase 数据库允许行为默认值为任意类型的常量数据。 - `ERROR ON EMPTY`：抛出错误。 - `json_value_on_error_clause`：如果表达式执行期间发生错误，用户可以使用预期的行为去覆盖该错误，可选择的行为包含三种：`NULL`、`ERROR` 和默认值 `DEFAULT`。可选行为和 `json_value_on_empty_clause` 一致。 以下场景会触发可选的 `json_value_on_error_clause` 子句。 - `expr` 不是格式正确的 JSON 数据 - 使用 JSON 路径表达式计算 JSON 数据时发现非标量值 - 使用 JSON 路径表达式计算 JSON 数据时未找到匹配项。 - 所指定的返回值数据类型不够大，无法容纳返回值。 ## 示例 ## 解析用户输入数据 如下示例为将用户输入的 JSON 数据转为关系表。 ```shell SELECT * FROM JSON_TABLE( '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', '$[*]' COLUMNS (id FOR ORDINALITY, jpath VARCHAR(100) PATH '$.a' DEFAULT '33' ON EMPTY DEFAULT '66' ON ERROR, jsn_path JSON PATH '$.a' DEFAULT '{"x":33}' ON EMPTY, jexst INT EXISTS PATH '$.b') ) AS tt; ``` 示例的查询结果如下： ```shell +------+-------+-----------+-------+ | id | jpath | jsn_path | jexst | +------+-------+-----------+-------+ | 1 | 33 | {"x": 33} | 0 | | 2 | 2 | 2 | 0 | | 3 | 33 | {"x": 33} | 1 | | 4 | 0 | 0 | 0 | | 5 | 66 | [1, 2] | 0 | +------+-------+-----------+-------+ 5 rows in set ``` ## 从表中获取数据 如下示例为从表中获取 JSON 数据，将其展开称为结构化数据。 ```shell DROP TABLE IF EXISTS t1; CREATE TABLE t1(id INT, jd JSON); INSERT INTO t1 VALUES (1, '[1,3,5]'),(2,'[2,4,6]'); SELECT id, jt.* FROM t1, JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY, val INT PATH '$')) AS jt; SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.* FROM t1, JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY, val INT PATH '$')) AS jt; ``` ```shell +------+------+------+ | id | jid | val | +------+------+------+ | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 5 | | 2 | 1 | 2 | | 2 | 2 | 4 | | 2 | 3 | 6 | +------+------+------+ 6 rows in set ``` ## 创建视图 如下示例为通过 `JSON_TABLE()` 函数的返回值生成视图。 ```shell CREATE VIEW v1 AS SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]', '$[*]' COLUMNS ( a INT PATH '$.b')) AS jt; SELECT * FROM v1; ``` ```shell +------+ | a | +------+ | 2 | +------+ 1 row in set ``` 如下示例为查看视图定义。 ```shell SHOW CREATE VIEW v1; DROP VIEW v1; ``` ```shell +------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | v1 | CREATE VIEW `v1` AS select `jt`.`a` AS `a` from JSON_TABLE('[{\"a\": 1, \"b\": 2}]' , '$[*]' columns (a INTEGER path '$.b' )) jt | utf8mb4 | utf8mb4_general_ci | +------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set ``` ## 向表中插入数据 如下示例为将 `JSON_TABLE()` 函数的返回值插入到表中。 ```shell DROP TABLE IF EXISTS t2; CREATE TABLE t2(id INT, a TINYINT, b VARCHAR(5), c DATE, d DECIMAL); INSERT INTO t2 SELECT * FROM JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'), JSON_OBJECT('a', 2, 'b', 'abcd'), JSON_OBJECT('a', 1000, 'b', 'xyz'), JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)), '$[*]' COLUMNS (id FOR ORDINALITY, a TINYINT PATH '$.a' DEFAULT '111' ON ERROR, b VARCHAR(5) PATH '$.b' DEFAULT '"ERR"' ON ERROR, c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR, d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR) ) AS jt; SELECT * FROM t2 ORDER BY id; DROP TABLE t2; ``` ```shell +------+------+------+------------+-------+ | id | a | b | c | d | +------+------+------+------------+-------+ | 1 | 1 | abc | NULL | NULL | | 2 | 2 | abcd | NULL | NULL | | 3 | 111 | xyz | NULL | NULL | | 4 | NULL | NULL | 2001-01-01 | 43200 | +------+------+------+------------+-------+ 4 rows in set ``` ## 相关文档 [JSON 数据类型](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000005688110) 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线