Fold implementation idea for SQL Server that does not require post-processing for JSON-representable types

[obi1kenobi]

Only works on SQL Server 2017+, I believe.

Here's one approach:

• Construct an entire sub-query, with all the relevant outputs (with output conversion applied as necessary, e.g. for date/datetimes/decimals), traversals, and filters.
• Use STRING_ESCAPE to escape any existing commas and escape characters that may already exist in the outputs, if the produced outputs are of string type.
• Use STRING_AGG and concatenate some square brackets on either side to get each output result formatted into a JSON string representing a list of values.
• Finally, make SQL Server interpret the result as a JSON value, and return each output as a list of values using JSON_QUERY(<json string>, "strict $").

For data types directly representable in JSON (e.g. strings and floats), this approach would not require any post-processing. For other data types, we'd return a list of strings where each element is a string representation of the value we want (datetime, decimal, etc.), so the post-processing would be relatively easy and lightweight.

I'm not sure if there's a way to avoid the ugly manual JSON formatting, more research would be a good idea before implementing this. A cursory search didn't seem to point out anything obvious.

References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver15
https://database.guide/json_query-examples-in-sql-server-t-sql/
https://bertwagner.com/2017/12/19/how-to-create-json-multi-object-arrays-in-sql-server/