Overview of TABLE FUNCTIONS

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 entry points for table functions in MySQL-compatible mode include:

• JSON_TABLE(...) : Expands a JSON document into rows and columns.
• FILES(...) : reads data from an external file location 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)

Common abbreviations (when omitting optional parts) are as follows:

JSON_TABLE(expr, path_literal COLUMNS (column_list)) [AS] alias

Column definition capabilities

The column in the COLUMNS clause mainly includes the following categories (see the syntax block above for details):

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])

References

• JSON_TABLE function
• FILES table function
• SELECT statement
• LOAD DATA statement