PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    Try the following, make sure you replace ownerId with the actual owner ID you’re querying for. This query will run the JSON operations directly in PostgreSQL, utilizing jOOQ to form the SQL statement in a type-safe and dynamic way.

    import static org.jooq.impl.DSL.*;

// Assuming you have a reference to the DSLContext
DSLContext create = DSL.using(configuration);

Field<String> statusField = field("en.services -> 0 ->> 'status'", String.class);

Result<Record> result = create.select()
                              .from(table("entity").as("en"))
                              .where(field("en.owner_id").eq(ownerId))
                              .and(statusField.ne("ACTIVE"))
                              .limit(1)
                              .fetch();
    Login or Signup to reply.
  2. 0

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

    So this SQL fragment:

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

    … translates to this jOOQ API call chain:

    jsonbGetAttributeAsText(jsonbGetElement(en.SERVICES, 0), "status").ne("ACTIVE")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search