JOOQ JsonbValue in where()

Question

Working on trying to convert the following Postgres Query into Jooq. I would love to implement this with JOOQ's features instead of just copying the SQL in.

Ultimately im trying to make this query in jooq.

SELECT * from content_block cb 
JOIN content_block_data cbd on cbd.block_id  = cb.id 
WHERE cbd."data" @? '$.** ? (@.blockId == 120)';

Another instance of a similar query

SELECT *
FROM content_block_data
WHERE jsonb_path_query_first(data, '$.**.blockId') @> '120';

Another instance of a similar query

SELECT *
FROM content_block_data
WHERE jsonb_path_exists(data, '$.** ? (@.blockId == 120)');

What I have in java

    @NotNull 
Result<Record> parentBlockRecords =
        dslContext.select(asterisk()).from(CONTENT_BLOCK_DATA
            .join(CONTENT_BLOCK).on(CONTENT_BLOCK.ID.eq(CONTENT_BLOCK_DATA.BLOCK_ID)))
            //.where(jsonbValue(CONTENT_BLOCK_DATA.DATA,"$.**.blockId").toString()
             //   .contains(String.valueOf(blockId)))
            .fetch();

The where on this im having a hard time getting to work. The query can grab data from the DB, but just having a fair bit of trouble with this comparison.

And idea of the data in CONTENT_BLOCK_DATA.DATA

{
  "blocks": [
    {
      "blockId": 120,
      "__source": "block"
    },
    {
      "blockId": 122,
      "__source": "block"
    }
  ]
}

Thanks for the help.

Answer 1

You're looking for Jooq's jsonValue and jsonExists, which correspond to PostgreSQL's jsonb_path_query_first amd jsonb_path_exists.

Also note that the @? operator is equivalent to the jsonb_path_exists function with the silent flag set. See the note after the JSON operator table in the PostgreSQL documentation.

---

For your first example, you would use the following:

  .join(/* */)
  .where(jsonExists(CONTENT_BLOCK_DATA.DATA, "$.** ? (@.blockId == 120)"))
  .fetch();

For the second example, I couldn't find a Jooq DSL symbol that corresponds to the @> jsonb containment operator, so you might have to fallback to plain SQL in JOOQ, like so:

// Second example
  .from(/* */)
  .where(condition("{0} @> {1}",
    jsonValue(CONTENT_BLOCK_DATA.DATA, "$.**.blockId"),
    JSONB.valueOf(120)));
  .fetch();

Based on the solution presented here: https://github.com/jOOQ/jOOQ/issues/13125.