I have a field tags which is a string formed by comma-separated strings.
When I try to get the data from the database I want them to be separated.
jsonObject(
.......
key("tags").value(splitPart(TABLENAME.TAGS, ",", 1))
.......
)
From the above, I can only get one value but I want the entire array.
What should I do to get the entire array after splitting?
Unfortunately, the PostgreSQL SPLIT_PART function doesn't allow for accessing the entire split array, only a "part". You probably want to use string_to_array instead? That will produce a PostgreSQL array, which can be converted to a JSON(B) using the array_to_json function.
jOOQ does not yet support the latter (see #12841), but as always, when jOOQ doesn't know a vendor-specific SQL feature, you can easily resort to using plain SQL templating.