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 <QUERY>:

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!

[Mobe91]

@voronovmaksim @beikov I think the underlying issue here is that the return type of JSON_GET with JSON Path is underspecified and incoherent across databases. This is especially true for JSON Path queries that can yield multi-valued results as is the case with the issue at hand. E.g. given the json document:

["elem1","elem2"]

The JSON Path query $.[0] will return elem1 for MySQL, but it will return setof jsonb in case of postgresql (with only a single set member). The JSON Path query $.[*] will return ["elem1","elem2"] for MySQL, but it will again return setof jsonb in case of postgresql. However, this time the postgresql set will contain 2 elements which will actually increase the result set size of the query. Now, we could of course array_agg the setof jsonb, but this would also not help to align return types.

@beikov AFAIK, you have implemented support for JSON functions in Hibernate. I would be curious to know how the return type mismatch is unified there, or how you deal with this problem in general. Not sure what version of Hibernate these functions go into, I could not find any reference documentation about them.

@voronovmaksim An alternative workaround to using the @> operator could be using the jsonb_path_exists function in PostgreSQL. You should be able to use it using https://docs.jboss.org/hibernate/orm/6.5/querylanguage/html_single/Hibernate_Query_Language.html#embedding-sql. The benefit of this would be that you don't need to implement a custom function for rendering the @> operator correctly.

[beikov]

It's part of ORM 7.0 and the json_value function is the SQL standard which I opted to implement there. The general behavior of that function is to return a String and fail if the return value is a JSON object or array, but whether a SQL error is raised is database dependent. I added some additional syntax that the SQL standard also offers to control errors, yet, by default it behaves in a database dependent way e.g. MySQL won't throw an error.

I think it might be best if we deprecate the json_get function in Blaze-Persistence and introduce json_value/json_query as well, just like I added to Hibernate ORM. The user then has to choose one or the other, depending on what return value is expected.

[voronovmaksim]

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

Hi @beikov,

I'm having trouble understanding how to implement this.

Let's simplify it with the following query:

SELECT
   *
FROM documents
WHERE data -> 'consolidatedLocationOrderIds' @> '["cvijdvnaun7nk3chdi5g"]'::jsonb;

Here's the hardcoded JpqlFunction:

public class JsonContainsFunction implements JpqlFunction {
    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Class<?> getReturnType(Class<?> firstArgumentType) {
        return firstArgumentType;
    }

    @Override
    public void render(FunctionRenderContext context) {
        context.addArgument(0);
        context.addChunk("->");
        context.addChunk(JpqlFunctionUtil.unquoteSingleQuotes(context.getArgument(1)));
        context.addChunk("@>");
        context.addChunk("'[\"cvijdvnaun7nk3chdi5g\"]'::jsonb");
    }
}

How can I use this function in a WHERE clause?

1. criteriaBuilder.where(JSON_CONTAINS("data", "consolidatedLocationOrderIds")) fails with
   BuilderChainingException: A builder was not ended properly.

2. Should I move

   context.addChunk("@>");
   context.addChunk("'[\"cvijdvnaun7nk3chdi5g\"]'::jsonb");
   

   from JsonContainsFunction to the client code, e.g.,

   criteriaBuilder.where(JSON_CONTAINS("data", "consolidatedLocationOrderIds")).<METHOD>
   

   If so, what method should I use? None of the available methods seem suitable.

3. Should I use

   criteriaBuilder.whereExpression(JSON_CONTAINS("data", "consolidatedLocationOrderIds"))
   

   I don't think this is the correct approach.

Any guidance would be appreciated.

[beikov]

The easiest way to make this work is to define the function like

public class JsonContainsFunction implements JpqlFunction {
    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Class<?> getReturnType(Class<?> firstArgumentType) {
        return Integer.class;
    }

    @Override
    public void render(FunctionRenderContext context) {
        context.addArgument(0);
        context.addChunk("->");
        context.addChunk(JpqlFunctionUtil.unquoteSingleQuotes(context.getArgument(1)));
        context.addChunk("@>");
        context.addChunk("'[\"cvijdvnaun7nk3chdi5g\"]'::jsonb");
        context.addChunk(" and 1");
    }
}

and then use it like

criteriaBuilder.where("JSON_CONTAINS(data, consolidatedLocationOrderIds)").eqLiteral(1);

[voronovmaksim]

@beikov, it works, thanks. Is there a more elegant way to implement this? Right now, it feels like a workaround.

[beikov]

Sorry, but currently this is the easiest/best way that we can offer.