JSON_TABLE - OceanBase 数据库 V4.6.0

JSON_TABLE

更新时间：2026-06-19 18:27:42

描述

JSON_TABLE() 函数提供了一种联系关系表和 JSON 结构数据的途径。可以在 SQL 中像使用虚拟关系表一样来使用 JSON_TABLE() 的输出结果。JSON_TABLE() 为每一个 JSON 值提供了列输出，对于 JSON 内的数组，提供了多行（列）输出。 JSON_TABLE() 必须用在 SELECT 语句的 FROM 子句中。

语法

JSON_TABLE(
        expr [FORMAT JSON]
        [, json_path_literal]
        [ERROR | NULL ON ERROR]
        COLUMNS (
          [column_name [json_value_return_type] EXISTS [PATH path_literal] [json_exists_on_error_clause] [json_exists_on_emtpy_clause]]
          [column_name [json_query_return_type] FORMAT JSON [ALLOW|DISALLOW SCALARS] [json_query_wrapper_clause] [PATH path_literal] [json_query_on_error_clause]]
          [column_name [json_value_return_type] [TRUNCATE] [PATH path_literal] [json_exists_on_error_clause] [json_exists_on_emtpy_clause]]
          [column_name FOR ORDINALITY]
          [NESTED PATH path_literal COLUMNS (
            # 嵌套结构，重复父一级语法
            ...
          )]
        )
       )

语法说明

JSON_TABLE() 函数语法说明如下：

json_value_return_type：同 JSON_VALUE()的 RETURNING 子句。
json_query_return_type：同 JSON_QUERY() 的 RETURNING 子句。
FORMAT JSON：可选项，当 expr 的数据类型是 BLOB 时，需要使用该子句。
path_literal：一般的 json-path 字符串。
json_exists_on_emtpy_clause：同 JSON_EXISTS() 的 emtpy 子句。
json_exists_on_error_clause：同 JSON_EXISTS() 的 error 子句。
json_query_on_error_clause：同 JSON_QUERY() 的 error 子句。
json_query_wrapper_clause：同 JSON_QEURY() 的 WRAPPER 子句。
FOR ORDINALITY 子句的作用在于为当前输出行提供行号，是INT 类型的列。这里输出值的类型有 4种，除了 ORDINALIY 列，其他三种和 JSON_VALUE()、JSON_QUERY()、JSON_EXISTS() 的功能以及行为比较相近。
[NESTED PATH ...]子句是重复的嵌套结构。
ERROR 子句：指定初始输入 expr 的出错处理。

示例

# 使用 NESTED COLUMN 嵌套语句
DROP TABLE jsonb_table_test;
CREATE TABLE jsonb_table_test (js VARCHAR2 (4000));

DELETE FROM jsonb_table_test;
INSERT INTO jsonb_table_test VALUES (
  '[
  	{"c": [7], "d": [3], "a": 2,  "b": [3,4]}
  ]'
);

obclient> SELECT jt.* FROM jsonb_table_test jtt,
      json_table (jtt.js, '$[*]'
          columns (
            nested path '$.c[*]' COLUMNS (c int path '$'),
            nested path '$.d[*]' COLUMNS (d int path '$'),
            nested path '$.b[*]' COLUMNS (b int path '$'),
            a int path '$.a'
          )
      ) jt;
+------+------+------+------+
| C    | D    | B    | A    |
+------+------+------+------+
| NULL | NULL |    3 |    2 |
| NULL | NULL |    4 |    2 |
| NULL |    3 | NULL |    2 |
|    7 | NULL | NULL |    2 |
+------+------+------+------+
4 rows in set

# 使用 NESTED COLUMN 嵌套和 ORDINALITY 列
obclient> INSERT INTO jsonb_table_test VALUES (
        '[
        {"a":  1,  "b": [], "c": []},
        {"a":  2,  "b": [1, 2, 3], "c": [10, null, 20]},
        {"a":  3,  "b": [1, 2], "c": []},
        {"a":  4, "b": [1, 2], "c": [123]}
        ]'
      );
Query OK, 1 row affected

obclient> SELECT jt.* FROM jsonb_table_test jtt,
        json_table (jtt.js, '$[*]'
          columns (
            nested path '$.c[*]' COLUMNS (c int path '$'),
            nested path '$.b[*]' COLUMNS (b int path '$'),
            n FOR ORDINALITY,
            a int path '$.a'
          )
        ) jt;
+------+------+------+------+
| C    | B    | N    | A    |
+------+------+------+------+
| NULL | NULL |    1 |    1 |
| NULL |    1 |    2 |    2 |
| NULL |    2 |    2 |    2 |
| NULL |    3 |    2 |    2 |
|   10 | NULL |    2 |    2 |
| NULL | NULL |    2 |    2 |
|   20 | NULL |    2 |    2 |
| NULL |    1 |    3 |    3 |
| NULL |    2 |    3 |    3 |
| NULL |    1 |    4 |    4 |
| NULL |    2 |    4 |    4 |
|  123 | NULL |    4 |    4 |
+------+------+------+------+
12 rows in set

# 查询 json_table 的 json_value 列，以及使用 EMTPY 子句
obclient> SELECT jt.* FROM json_table (
        '{"data": 123.45}',
        '$'
         columns (a CLOB path '$.nokey' default 'abc' on EMPTY)
        ) jt;
+------+
| A    |
+------+
| abc  |
+------+
1 row in set

# 查询 json_table 的 json_value 列，以及使用 EMTPY 子句
obclient> SELECT jt.* FROM json_table (
        '{"data": 123.45}',
        '$'
         columns (a CLOB path '$.nokey' NULL on EMPTY)
        ) jt;
+------+
| A    |
+------+
| NULL |
+------+
1 row in set

obclient> SELECT jt.* FROM
   json_table(
   '{"details":
     [ {"branch_code": "3020300", "products": [
                                           "23D0980000000000",
                                           "23E2980000000000",
                                           "23E3980000000004"]},
      {"branch_code": "3090100", "products": [
                                           "23D0980000000000",
                                           "23E2980000000000"]}],
      "extras": ["AWUX1201293111", "AWUX1201293112"]
     }',
     '$.details[*]'
     columns (
       branch_code varchar2(100) path '$.branch_code',
       nested path '$.products[*]' columns (product varchar2(100) path '$[*]')
       )
     ) jt;

+-------------+------------------+
| BRANCH_CODE | PRODUCT          |
+-------------+------------------+
| 3020300     | 23D0980000000000 |
| 3020300     | 23E2980000000000 |
| 3020300     | 23E3980000000004 |
| 3090100     | 23D0980000000000 |
| 3090100     | 23E2980000000000 |
+-------------+------------------+
5 rows in set

# 查询 json_table 的 JSON_EXISTS 列
 obclient> SELECT jt.* FROM json_table (
        '{
          "a": "1997-8-14 11:11:11",
          "b" : 123,
          "c": "varchar2"
         }',
        '$' columns (
            a date EXISTS path '$.a',
            b int EXISTS path '$.b' ERROR ON EMPTY,
            c varchar2(32) format json path '$.c' NULL ON EMPTY
          )
        ) jt;
+---------------------+------+----------+
| A                   | B    | C        |
+---------------------+------+----------+
| 1997-08-14 11:11:11 |  123 | varchar2 |
+---------------------+------+----------+
1 row in set

# 查询 json_table 的 json_query 列
obclient> SELECT jt.* FROM json_table (
        '{"data": [1, 2, 3, 3]}',
        '$'
         columns (a varchar2(1024) FORMAT JSON path '$.data[*]')
        ) jt;
+--------------+
| A            |
+--------------+
| [1, 2, 3, 3] |
+--------------+
1 row in set