So not exactly earth shattering, but I’ve encountered this scenario often enough I thought it worth bringing up. Often when returning data from a query that will be used either for an API, or to pass through to the browser, I’ll use FOR JSON/XML PATH to have the database serialize the return data. There are many times where this is more efficient rather than having to return the query data structure, and then serialize it in Lucee, before returning it to the caller. This results in the query returning a single row, with a single column with a randomized name.
My thought then was to add a new return type to the query called “scalar” (or something to that effect),
and when used, the query variable will simply be the first cell. If the query tries to returns more than one column, or one row, it would throw an error
<cfquery name="json_out" datasource="..." returntype="scalar">
SELECT TOP(2) o.id, o.date
FROM orders o
FOR JSON PATH
</cfquery>
<cfdump var='#json_out#'>
It’s an interesting thought. Let me share some raw reactions that I’m having:
Do all databases support this kind of serialization? Or, would this only work for some DBs. Or, would this be done in the CFML layer (not actually the DB layer)? I’ve never seen FOR JSON PATH, so not sure what the implications are.
Serialization of data feels more like a “concern” of the application itself. That said, if there’s a specialized query for a given URL, then pushing this into the DB isn’t a problem.
I’ve done some JSON-oriented work with MySQL, using JSON_ARRAY() and JSON_OBJECT() calls; so, I can relate to cases in which returning JSON from the DB would be helpful.
Anyway, that’s just random things in my head as I’m reading this.
We use MS SQL; that syntax might be specific just to them. I do know that many other relational databases offer ways of serializing into JSON/XML.
Another example might be if you’re running a query that returns a row count, or just any other situation where you know you’re only returning a single cell. Like I said; nothing drastic, just a small optimization.
I am aware of that workflow; like I said, there are situations where using the database to do it is more efficient. From what I’ve read when the database serializes the data being returned, it works similar to a streaming json serializer, where as the rows are serialized, the final string is returned to the caller in one cell.
Contrast this to returning it as an array, where as I understand it, lucee has to read each cell individually from the database, build each structure, append it to the array, rinse and repeat for each row. Only to then turn around and pass it to the serializer which then has to iterate over everything again.
It’s just a lot of unnecessary memory and time wasted.