PostgreSQL JSON types

[Rob Shepherd]

Dear QueryDSL users.

I am experimenting with the PostgreSQL JSON datatypes but I would like to learn if there is a way to bridge into this functionality using QueryDSL, which is my preferred query tool.

Where would I start with custom extensions with JPAQuery/Hibernate as a basis?

I have been able to read/write json & jsonb data-typed columns and have managed to create HQL functions such as this example hql functions

registerFunction("jsonProp", new SQLFunctionTemplate(StandardBasicTypes.STRING, "(?1 -> ?2)"));
registerFunction("jsonPropStr", new SQLFunctionTemplate(StandardBasicTypes.STRING, "(?1 ->> ?2)"));
registerFunction("jsonPropInt", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "(?1 ->> ?2)::numeric"));

......and the following queries in hql

entityManager.createQuery("from Thing thing where jsonPropStr(thing.book, 'title') = :title ")
entityManager.createQuery("from Thing thing where jsonPropInt( jsonProp(thing.book, 'author'), 'age') = :authorAge ")

Is there anything I can do to give QueryDSL the same magic?

Thanks

Rob

[timowest]

Hi Rob.

On Thursday, June 4, 2015 at 1:42:32 AM UTC+3, Rob Shepherd wrote:

Dear QueryDSL users.

I am experimenting with the PostgreSQL JSON datatypes but I would like to learn if there is a way to bridge into this functionality using QueryDSL, which is my preferred query tool.

Where would I start with custom extensions with JPAQuery/Hibernate as a basis?

I have been able to read/write json & jsonb data-typed columns and have managed to create HQL functions such as this example hql functions

registerFunction("jsonProp", new SQLFunctionTemplate(StandardBasicTypes.STRING, "(?1 -> ?2)"));
registerFunction("jsonPropStr", new SQLFunctionTemplate(StandardBasicTypes.STRING, "(?1 ->> ?2)"));
registerFunction("jsonPropInt", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "(?1 ->> ?2)::numeric"));

This part stays the same with Querydsl.

 

......and the following queries in hql

entityManager.createQuery("from Thing thing where jsonPropStr(thing.book, 'title') = :title ")
entityManager.createQuery("from Thing thing where jsonPropInt( jsonProp(thing.book, 'author'), 'age') = :authorAge ")

The jsonProp parts could be described like this

public static StringExpression jsonPropStr(Expression<?> expr, String prop) {

    return Expressions.stringTemplate("jsonProp({0}, {1})", expr, prop);

}

public static NumberExpression<Integer> jsonPropInt(Expression<?> expr, String prop) {

    return Expressions.numberTemplate(Integer.class, "jsonProp({0}, {1})", expr, prop);

}

Timo

[Rob Shepherd]

The jsonProp parts could be described like this

public static StringExpression jsonPropStr(Expression<?> expr, String prop) {

    return Expressions.stringTemplate("jsonProp({0}, {1})", expr, prop);

}

public static NumberExpression<Integer> jsonPropInt(Expression<?> expr, String prop) {

    return Expressions.numberTemplate(Integer.class, "jsonProp({0}, {1})", expr, prop);

}

Thanks very much Timo.

This works great.  

I have now also managed to also create a query delegate around this as follows:

(QueryDelegates are super slick b the way! :) Cheers! )

@QueryDelegate(Thing.class)
public static StringExpression bookTitle(EntityPath<Thing> thing)
{
    return Expressions.stringTemplate("jsonPropStr({0}, {1})", QThing.thing.book, Expressions.constant("title") );
}

Pushing my luck here..... 

Is there a way to make this @QueryDelgate fluent approach even slicker by attaching to the book (which isn't an entity, and has no Q Type etc), and getting it to use the JPA custom function as above?

Thing t1 = new JPAQuery(entityManager)
             .from(QThing.thing)
             .where(QThing.thing.book.prop("title").eq(...))
             .fetchOne();

If there might be a way, please would you point me towards some sourcecode that I can examine.....

Cheers

Rob

 

[timowest]

By using a different Java type for the JSON mapped strings you could provide a query delegate for that type which provides the prop(propertyName) functionality.

[Marvin Froeder]

FWIW, I did this for my company

https://github.com/velo/hibernate-native-json/

It can help you with that.