Fold implementation idea for SQL Server that does not require post-processing for JSON-representable types
obi1kenobi opened this issue · 0 comments
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/