How to write a where clause using JOOQ, filtering a specific JSON attribute in a JSON object inside a JSON array?

Question

I have this struct in a JSONB column named services of my PostgreSQL database:

[
 {
   "status": "ACTIVE",
   <another attributes>
 },
 <another JSON objetcs>
]

I want to make this select:

select
    *
from
    entity en
where
    en.owner_id = '???' and
    en.services->0->>'status' <> 'ACTIVE'
limit 1;

How can I write the second clause(below) using JOOQ?

en.services->0->>'status' <> 'ACTIVE'

I've tried some of the documentations examples but nothing worked for me, as you can see, I need to access the attribute inside the first JSON object of a JSON array, so, its not to simple as the documentation examples shows.

I'm currently using the JOOQ 3.19.1 version.

Answer 1

Since jOOQ 3.18, there's support for these native operators as documented here:

• JSON array element access
• JSON object attribute access

So this SQL fragment:

en.services->0->>'status' <> 'ACTIVE'

... translates to this jOOQ API call chain:

jsonbGetAttributeAsText(jsonbGetElement(en.SERVICES, 0), "status").ne("ACTIVE")