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.

Basic syntax of JSON_TABLE

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

FILES syntax

FILES(location_expr, format_expr [, pattern_expr])

Column definition capabilities

column_list supports the following types:

• column_name FOR ORDINALITY
• column_name data_type PATH path_literal [ON EMPTY] [ON ERROR]
• column_name data_type EXISTS PATH path_literal
• NESTED [PATH] path_literal COLUMNS (column_list)

Functions for returning table schemas

References

• JSON_TABLE function
• FILESTABLE function
• SELECT statement
• LOAD DATA statement