P3: JSON functions support — JSON_EXTRACT, JSON_TABLE, JSON_ARRAY_AGG (SQL:2016)

[ajitpratap0]

Modern applications store 70%+ of data with JSON columns. GoSQLX supports PostgreSQL JSON/JSONB operators (10/10) but is weak on SQL:2016 standard JSON functions and MySQL/SQL Server JSON function syntax.

Current State

• ✅ PostgreSQL JSON operators: ->, ->>, #>, #>>, @>, <@, ?, ?|, ?&, #-
• ⚠️ JSON functions: ~20% coverage (recognized as generic function calls)
• ❌ JSON_TABLE: 0%
• ❌ JSON_EXTRACT (MySQL/SQL:2016): not parsed
• ❌ JSON_ARRAYAGG, JSON_OBJECTAGG: not parsed

SQL:2016 JSON Functions to Support

Phase 1 (recognition only): JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_ARRAYAGG, JSON_OBJECTAGG, JSON_EXTRACT, JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_MERGE_PATCH

Phase 2 (AST node): JSON_TABLE with COLUMNS clause

Implementation Approach

• Phase 1: Recognize JSON functions as named functions — improves from 20% to 70%
• Phase 2: Add JsonTableExpression AST node for JSON_TABLE (column definitions)
• Phase 3: Semantic validation (path expressions, column type checking)

Acceptance Criteria (Phase 1)

• JSON_VALUE, JSON_QUERY, JSON_EXISTS recognized
• JSON_ARRAYAGG, JSON_OBJECTAGG recognized
• MySQL JSON_EXTRACT, JSON_SET, JSON_INSERT, JSON_REMOVE recognized
• JSON_TABLE: basic parsing (Phase 2 can follow)
• docs/SQL_COMPATIBILITY.md JSON section updated

[ajitpratap0]

JSON function parsing tests added in pkg/sql/parser/json_functions_test.go: JSON_OBJECT, JSON_ARRAY, JSON_EXTRACT, JSON_AGG, JSON_BUILD_OBJECT, JSON_BUILD_ARRAY, JSON_VALUE, and PostgreSQL -> and ->> operators all tested and passing. PR: #469