qb icon indicating copy to clipboard operation
qb copied to clipboard

First-class grammar support for native JSON marshaling

Open MordantWastrel opened this issue 3 years ago • 1 comments

MSSQL and MySQL both support returning queries natively in JSON (as opposed to JSON column types and filters here: https://github.com/coldbox-modules/qb/issues/95):

MSSQL: https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15 (FOR JSON (PATH|AUTO))

MySQL: https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html (SELECT-based)

PostgreSQL: https://dba.stackexchange.com/questions/90482/export-postgres-table-as-json/90495#90495

QB doesn't currently support using .raw() in the manner that MSSQL (at least) requires inserting a phrase in between WHERE and ORDER BY, so as best I can tell it's not currently possible to invoke this manually. A baby step would seem to be a generalized mechanism of adding SQL in some arbitrary location, e.g. "after SELECT but before WHERE" or (as with MSSQL, in this case) "after WHERE but before ORDER BY". Then we can start playing around with making it do what we want and thinking about the best way to abstract support for this across grammars, which seems likely to be the heavy lift here.

MordantWastrel avatar May 20 '21 15:05 MordantWastrel