Add Support for JSON_TABLE Function

[LyzSg]

Problem Description

Currently, node-sql-parser does not support the JSON_TABLE function, which is a critical SQL feature for transforming JSON data into relational format. This function is widely used in modern database systems (MySQL 8.0+, Oracle 12c+, PostgreSQL 12+).

Use Cases

1. JSON Data Unnesting

-- Extract array elements as rows
SELECT jt.*
FROM orders,
  JSON_TABLE(
    order_items,
    '$[*]' COLUMNS (
      product_id INT PATH '$.id',
      product_name VARCHAR(100) PATH '$.name',
      quantity INT PATH '$.qty',
      price DECIMAL(10,2) PATH '$.price'
    )
  ) AS jt
WHERE order_id = 1001;

2. Complex JSON Structure Parsing

-- Parse nested JSON structures
SELECT 
  dept.department_name,
  emp.*
FROM departments dept,
  JSON_TABLE(
    dept.employees,
    '$[*]'
    COLUMNS (
      emp_id INT PATH '$.id',
      emp_name VARCHAR(50) PATH '$.name',
      emp_salary DECIMAL(10,2) PATH '$.salary',
      NESTED PATH '$.skills[*]' COLUMNS (
        skill VARCHAR(50) PATH '$'
      ),
      NESTED PATH '$.projects[*]' COLUMNS (
        project_id INT PATH '$.id',
        project_name VARCHAR(100) PATH '$.name'
      )
    )
  ) AS emp;

Expected Syntax Support

Basic Syntax

JSON_TABLE(
  json_doc, 
  path COLUMNS (column_list)
) [AS] alias

Column Definitions

COLUMNS (
  -- Simple column
  column_name data_type PATH json_path,
  
  -- Column with default value
  column_name data_type PATH json_path 
    DEFAULT 'default' ON EMPTY 
    DEFAULT 'default' ON ERROR,
  
  -- Generated column
  column_name data_type FORMAT JSON 
    EXISTS|TRUNCATE|ERROR PATH json_path,
  
  -- Nested paths
  NESTED PATH path COLUMNS (column_list),
  
  -- Ordinality column
  column_name FOR ORDINALITY
)