Search code examples
javapostgresqlprepared-statementjooqjsonb

Search by JSONB field value. How to prepare query correctly using jooq?

I'm trying to find records by condition from the jsonb field (array). I'm using the jsonb_exists_all function for this. I wrote a working SQL, but I ran into the problem of creating correct Java code (Jooq) with argument binding.

For example, the table structure

CREATE TABLE mytable (
    id int,
    data jsonb
);

INSERT INTO mytable VALUES
(1, '{"roles": ["ONE", "TWO", "FIVE"]}'),
(2, '{"roles": ["ONE", "SIX"]}');

And in my raw working SQL query, I'm searching by array values. This work.

SELECT
    id,
    data
FROM
    mytable
WHERE
    jsonb_exists_all(mytable.data -> 'roles', array['ONE','TWO'])

In the next step, I'm trying to write Java code, and I'm having some issues with bindings (arguments) to implement a secured request.

String args = "'ONE','TWO'";

Flux.from(dsl.selectFrom(MyTable.MYTABLE)
          .where(noCondition().and("jsonb_exists_all(parameters -> 'roles', array[" + args + "])")))
          .map(record -> record.into(MyTableDto.class))
          .collectList();

Is it possible to do binding without concatenation?

I will be grateful!

Solution

  • First off, with jOOQ, you should never concatenate strings. There's almost always a better way.

    In your case, you could use plain SQL templates as follows:

    .where("json_exists_all({0}, {1})",
    jsonGetAttribute(MYTABLE.DATA, inline("roles")),
    val(new String[] { "ONE", "TWO" })
)

    This is using: