How to write a query with aggregates of json_array_elements in jOOQ

I have this query:

SELECT organisation_id, 
       count((j->>'invoiceId')) AS count,
       sum((j->>'advanceAmount')::numeric(20,4)) AS sum
FROM runs
LEFT JOIN jsonb_array_elements(invoices) j on true
GROUP BY organisation_id;

How can I write this using jOOQ version 3.15.5 ?

I naturally have jOOQ generated Runs.RUNS.ORGANISATION_ID and Runs.RUNS.INVOICES columns at the ready.

Can consider numeric(20,4) as a BigDecimal type in Java.

I'm assuming this is mainly about the following two operators/functions:

• ->> which translates to jsonGetAttributeAsText
• jsonb_array_elements, which is just a PostgreSQL specific version of the standard SQL JSON_TABLE

If you want to use jOOQ API for these two things, use the above functions.

How can I write this using jOOQ version 3.15.5 ?

If anything isn't supported in jOOQ, or in an older version of jOOQ, or if you prefer to use the simpler jsonb_array_elements function over the more verbose JSON_TABLE function, just use plain SQL templates, as always