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.
CodePudding user response:
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.