Bug #120201 JSON_OBJECT(*) as a namespacing primitive for subqueries and joins aka. wildcard for columns

• Submitted: 1 Apr 10:08
• Reporter: David Maro
• Status: Open
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[1 Apr 10:08] David Maro

Description:
Enable Generic API Abstractions (inspired by PostgREST API)

This feature enables building lightweight API query layers such as:

GET users/select=*,session!last(*)
GET item/select=*,stock!last(*)

Where:

* maps to base table columns
session!last(*) maps to a joined subquery using window functions
JSON_OBJECT(t.*) ensures clean nesting without collisions

How to repeat:
Key Use Case: Column Conflict Resolution in Joins

When joining tables with common column names (e.g., id, date), developers must manually alias or enumerate fields.

Example:

SELECT i.*, JSON_OBJECT(s.*),
       ROW_NUMBER() OVER (PARTITION BY i.id ORDER BY s.date DESC) AS rn
FROM item i
LEFT JOIN home s ON i.id = s.what;

This pattern:

Encapsulates s as a JSON object
Avoids naming conflicts
Preserves clean structure for downstream use (e.g., APIs)

Suggested fix:
Feature Request: Support JSON_OBJECT(t.*) for column namespacing and conflict-free joins
Summary

Add support for implicit column expansion in JSON_OBJECT, including qualified usage:

JSON_OBJECT(*)
JSON_OBJECT(t.*)

This allows automatic mapping of all columns into a JSON object, preserving structure and avoiding column name conflicts.