Add JSON path navigation support

[beikov]

Splitted off of #841 for the path navigation support.

I'd like to allow dereferencing a json attribute via normal path navigation I.e. jsonAttribute.person.age

In order to make this happen, we need a json metamodel in java that we can query to know what to cast a result to and to decide if a specific navigation operation is allowed. Maybe we could also support that without a model but then the user would have to do casts of the final values. I was thinking about using the colum definition or a custom annotation on the entity attribute to detect JSON typed attributes.

Selecting sub-objects of the JSON type should be possible by making use of a JSON binding provider in an internal ObjecBuilder.

This is blocked by #765 which will enable us to model foreign model structures and allow to easily de-reference it.

[RustamSultansoy]

any updates here, guys? given hibernate 6 introduced built in json type i think it should be a bit easier to rely on that.

[beikov]

What is it exactly that you would like to do? If you use an embeddable with @JdbcTypeCode(SqlTypes.JSON) then this thing is just like any other embeddable. I guess you would rather like to access arbitrary JSON blobs through the json_value() function that Hibernate ORM offers though?

[SShahmuradli]

we are using Blaze-Persistence with JPA/Hibernate (PostgreSQL) and I have an entity that stores a JSONB array of objects. Each element in the JSON array contains a small structure with several fields, including an enum status and an ID.

I’d like to group and count records based on a field inside that JSON array (e.g. status), similarly to what jsonb_array_elements() does in SQL.

However, Blaze currently treats the JSONB column as a single scalar value — there is no way to “iterate” or “join” over elements of a JSON array using CriteriaBuilder.

Example of the SQL I’m trying to achieve:

SELECT elem->>'status' AS filter_value, COUNT(DISTINCT (elem->>'id'))::BIGINT AS count FROM my_table t, jsonb_array_elements(t.json_column) elem WHERE ... GROUP BY filter_value;

is it possible to implement by blaze persistence?

[beikov]

This is not yet supported @SShahmuradli and the issue you need to watch for getting updates is https://github.com/Blazebit/blaze-persistence/issues/181 In the meantime, you could define a @Subselect entity for that and query it e.g.

@Entity
@Subselect("select t.id, elem->>'status' as status, cast(elem->>'id' as bigint) as elem_id from my_table t left join lateral jsonb_array_elements(t.json_column) elem")
public class MyTableArrayElement {
    Long id;
    String status;
    @Column(name = "elem_id")
    Long elementId;
}

Then you can write a HQL query like this or also query through the Blaze-Persistence CriteriaBuilder:

select e.status, count(distinct e.elementId)
from MyTableArrayElement e
where ...
group by e.status