Description
Table functions are used to expand semi-structured data into a relational row set, allowing them to be referenced in SELECT ... FROM statements just like tables.
Common table function entries include:
JSON_TABLE(...): Expands a JSON document into rows and columns.FILES(...): Reads data from an external file and participates in the query as a rowset.
Syntax Features
Usage
Table functions, used as table factors, typically appear in the FROM clause.
Alias requirements
You must specify a table alias for JSON_TABLE.
Syntax of JSON_TABLE
JSON_TABLE(
expr [FORMAT JSON]
[, json_path_literal]
[ERROR | NULL ON ERROR]
COLUMNS (
column_list
)
) [AS] alias
column_list:
column[, column ...]
column:
column_name FOR ORDINALITY
| column_name [json_value_return_type] EXISTS [PATH path_literal]
[json_value_on_error_clause] [json_value_on_empty_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_value_on_error_clause] [json_value_on_empty_clause]
| NESTED PATH path_literal COLUMNS (column_list)
Note
The preceding section provides an overview of the syntax structure for JSON_TABLE. This page only lists the complete syntax skeleton and does not explain the meaning of each clause or all possible combinations.
For the actual support scope, semantics, and examples of each clause in OceanBase AI Database, refer to the dedicated topic JSON_TABLE function.
Common abbreviations (when omitting optional parts) are as follows:
JSON_TABLE(expr, path_literal COLUMNS (column_list)) [AS] alias
Column definition capabilities
The column items in the COLUMNS clause include the following categories (see the syntax block above):
Column definition format |
Purpose |
|---|---|
column_name FOR ORDINALITY |
Generates a serial number column for the current output row. |
column_name ... EXISTS [PATH path_literal] ... |
Determines whether a JSON value exists in the path and returns 0 or 1. |
column_name ... FORMAT JSON ... [PATH path_literal] ... |
Extract values along a path in JSON format (can be used with ALLOW SCALARS / DISALLOW SCALARS(clauses). |
column_name [data_type] [TRUNCATE] [PATH path_literal] ... |
Converts a JSON scalar at the path to a specified SQL type. (Can be used in conjunction with ON EMPTY / ON ERROR). |
NESTED PATH path_literal COLUMNS (...) |
Nested expansion; the inner column_list can repeat the preceding column definition format. |
The syntax and semantics of clauses such as json_value_on_empty_clause and json_value_on_error_clause are described in JSON_TABLE function.
FILES Syntax
FILES(location_expr, format_expr [, pattern_expr])