CODEWITHSUNDEEP
javajooq
Bharath
Bharath

Reputation: 1807

How do you add jsonb functions in FROM subqueries using JOOQ

How do you build this query using jooq?

select et.id 
from t, et, 
    jsonb_array_elements(t.keys_and_values) as s, jsonb_object_keys(s) as s1
where et.id = t.id
and s1 = 'key'

Upvotes: 2

Views: 531

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221275

Whenever you're missing API support for vendor specific SQL features, you can resort to using the plain SQL templating API. I.e. write

Table<?> s = DSL.table("jsonb_array_elements({0})", T.KEYS_AND_VALUES).as("s");
Table<?> s1 = DSL.table("jsonb_object_keys({0})", s).as("s1");

And then, in order to form a predicate, you can write:

DSL.condition("{0} = {1}", s1, DSL.val("key"));

There are many other approaches, all using the plain SQL templating API, depending on how much you want to reuse some vendor specific function expressions, etc.

Upvotes: 3

Related Questions