Coalesce jsonArrayAgg to empty array in jOOQ

Question

What is the equivalent for coalesce in the new jOOQ 3.14 SQL/JSON supporting version (in PostgreSQL)?

select coalesce(json_agg(t.*), '[]'::json)
from (select 'test' as mycol where 1 = 2) t;

The following unfortunately fails with the error "COALESCE types json and uuid[] cannot be matched".

coalesce(jsonArrayAgg(mycol), emptyArray<type>())

Lukas Eder · Accepted Answer · 2021-06-11 07:07:34Z

2

coalesce(jsonArrayAgg(mycol), inline(json("[]")))
coalesce(jsonbArrayAgg(mycol), inline(jsonb("[]")))

But you can also use the jsonArray() or jsonbArray() constructors:

coalesce(jsonArrayAgg(mycol), jsonArray())
coalesce(jsonbArrayAgg(mycol), jsonbArray())

answered Dec 22, 2020 at 15:22

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1