Incorrect emulation of T-SQL WITHOUT_ARRAY_WRAPPER directive, when used with TYPE in a nested JSON context

[lukaseder]

When using the T-SQL WITHOUT_ARRAY_WRAPPER directive in a nested query, using TYPE in order to nest the JSON document, then the current behaviour is likely wrong as can be seen here:

• https://www.jooq.org/doc/3.20/manual/sql-building/sql-statements/select-statement/for-json-clause/for-json-without-array-wrapper-directive/

E.g. the emulation of:

select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).forJSON().auto().withoutArrayWrapper()

Is this, in PostgreSQL:

SELECT regexp_replace(CAST(json_agg(json_strip_nulls(json_build_object('ID', ID))) AS varchar), '^\[(.*)\]$', '\1', 'g')
FROM (
  SELECT BOOK.ID
  FROM BOOK
  ORDER BY BOOK.ID
) t

We're creating a varchar typed expression, which is unlikely to be correctly embedded in another JSON document, in case we nest the above query.

This is likely mostly a translation problem, not an ordinary jOOQ API usage problem, as I suspect hardly anyone uses the T-SQL syntax over the SQL/JSON one.