Oracle 的 JSON_TABLE 函数

Oracle 的 JSON_TABLE 函数用于将 JSON 数据转换为关系表结构使得可以像查询普通 SQL 表一样查询 JSON 数据。它将 JSON 数组中的每个对象映射为一行JSON 对象中的字段映射为列‌。基本语法SELECT * FROM JSON_TABLE(json_document,json_pathCOLUMNS (column_definition_list)) AS alias;参数说明‌json_document‌要解析的 JSON 数据可以是字符串、VARCHAR2、CLOB 或 BLOB 类型的列。如果该列是 BLOB 类型则必须使用 FORMAT JSON 子句。‌json_path‌指定 JSON 数据中要处理的部分的路径表达式。例如$ 表示整个 JSON 文档$.employees[*] 表示数组中的所有元素。‌COLUMNS‌定义输出列的列表。每列可以指定名称、数据类型、路径表达式等‌。列定义语法在 COLUMNS 子句中每列可以使用以下几种形式之一定义‌普通列‌column_name data_type PATH path_literal示例name VARCHAR2(50) PATH $.name‌返回 JSON 格式的数据‌column_name data_type FORMAT JSON PATH path_literal示例phones VARCHAR2(100) FORMAT JSON PATH $.phones‌使用 EXISTS 检查是否存在某个字段‌column_name data_type EXISTS PATH path_literal示例has_address BOOLEAN EXISTS PATH $.address‌使用 FOR ORDINALITY‌生成一个序号列用于标识每行的顺序。示例row_num FOR ORDINALITY示例假设有一个包含 JSON 数据的表 emp其结构如下CREATE TABLE emp (jsondoc VARCHAR2(32000));插入如下数据{id: 901,name: {first: John,last: Doe},office: E-334,phones: [{type: home,number: 555-3762},{type: work,number: 555-7242}]}可以使用 JSON_TABLE 来提取这些数据SELECT t.first, t.last, t.officeFROM emp,JSON_TABLE(emp.jsondoc,lax $COLUMNS (first VARCHAR2(10) PATH lax $.name.first,last VARCHAR2(10) PATH lax $.name.last,office VARCHAR2(10) PATH lax $.office)) AS t;此查询会返回如下结果表格FIRST LAST OFFICEJohn Doe E-334错误处理默认情况下如果 JSON 数据不符合预期格式JSON_TABLE 会返回空值。可以通过添加 ON ERROR 或 NULL ON ERROR 子句来控制错误处理行为。性能优化在某些情况下JSON_TABLE 可以与物化视图结合使用以提高查询性能。此外Oracle 数据库优化器可能会自动将多个 JSON_EXISTS、JSON_VALUE 或 JSON_QUERY 调用重写为单个 JSON_TABLE 调用。