Exception: set-returning functions are not allowed in WHERE (jsonb_path_query)

[voronovmaksim]

Hi.

I have an entity cls_extension_settings with a PostgreSQL jsonb field settings structured like this:

{
  "attrs":[
    //A lot of other nodes
    { "key": "value1"},
    //A lot of other nodes
  ]
}

I need to build a WHERE clause using CriteriaBuilder.where(<EXPRESSION>) to perform CRUD operations.

Current Query Attempt

I tried building the query like this:

whereExpression = "JSON_GET(settings, '$.attrs[*].key')";
where = criteriaBuilder.where(whereExpression);
where.eq("value1");

However, executing the query results in the following error:

org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL <QUERY> [ERROR: set-returning functions are not allowed in WHERE]

Here’s the generated :

SELECT
    ces1_0.id,
    (
        SELECT
            count(*)
        FROM
            cls_extension_settings ces2_0
        WHERE
            jsonb_path_query(
                    cast(ces2_0.settings as jsonb),
                    cast(
                            '$.attrs[*].key' as jsonpath
                    )
            ) = 'value1'
    )
FROM
    cls_extension_settings ces1_0
WHERE
    jsonb_path_query(
            cast(ces1_0.settings as jsonb),
            cast(
                    '$.attrs[*].key' as jsonpath
            )
    ) = 'value1'

My Understanding of the Issue

I understand that this error is related to a limitation in PostgreSQL.

As discussed here, using JSONPath is one way to perform filtering without hardcoding array indexes, but it appears that JSONPath works only in projections and not in the WHERE clause.

Raw Query That Works

SELECT
    ces1_0.id,
    (
        SELECT
            count(*)
        FROM
            cls_extension_settings ces2_0
        WHERE
            ces2_0.settings::jsonb @> '{ "attrs": [ { "key": "value1" } ] }'
    ) AS count
FROM
    cls_extension_settings ces1_0
WHERE
    ces1_0.settings::jsonb @> '{ "attrs": [ { "key": "value1" } ] }';

However, I am unsure if this can be replicated using Blaze Persistence.

Question

• Is this a known issue with Blaze Persistence, or is it a bug that should be fixed?
• Can you suggest any workarounds for this? I prefer using Blaze for all select queries and want to avoid using Hibernate queries.

Thanks for your help!

[beikov]

Hi and sorry for taking so long to answer, PTO and sickness held me back.
Thanks for reporting this issue. It seems to me that the implementation of JSON path support that @Mobe91 did is incomplete, so let's hope he will fix that soon :)

In the meantime, what Hibernate ORM version do you use? Also, what PostgreSQL version are you using? Are you targeting just PostgreSQL or also other databases?

You could define a custom function that renders the @> operator directly.

[voronovmaksim]

Hi, i hope you are doing well.

I'm working with the following dependencies:

org.hibernate.orm:hibernate-core:jar:6.5.2.Final
org.postgresql:postgresql:jar:42.7.3
postgres:17.2

Yes, postgres is the only database i'm working with.

FYI: All JARs are transitive dependencies of Quarkus: 3.12.2

Thanks for your help!