Querydsl: Support for PostgreSQL JSON and Hstore types

[palexis]

It would be great to have support of these two awesome features provided by PostgreSQL. Supporting 'where' clause on JSON and Hstore field would be even more useful.

[magnuskvalheim]

Yes, second that.
We have quite a few json serialized strings in postgresql db and recently upgraded to 9.3 - so would be great to leverage that with querydsl.

[jplehmann]

I third that. I need to access JSONB via QueryDSL right now.

Can someone please suggest how one would get started in adding a custom data type like this and/or point to an appropriate example?

Perhaps this? http://www.querydsl.com/static/querydsl/3.6.3/reference/html_single/#d0e1331

[robshep]

I'm fiddling this (with help from timo) by using custom HQL functions:
https://groups.google.com/forum/#!topic/querydsl/0dvvE4gWGB0

[jplehmann]

Until we get a more formal solution, here's some example code that might help someone, which uses Jackson.

final ObjectMapper mapper = new ObjectMapper();

public static JsonNode bytesToJson(ObjectMapper o, byte[] bytes) {
  try {
    return o.readTree(bytes);
  } catch (IOException e) {
    throw Throwables.propagate(e);
  }
}

// ucm.info here is my JSONB field
final String someValue = bytesToJson(mapper, t.get(ucm.info)).get("some_jsonb_key").asText(),

I also had to force the type of the JSONB column to bytes in the pom.xml that builds my QueryDSL classes.

                        <typeMapping>
                            <!--
                                 No current support for JSONB.
                            -->
                            <table>my_table</table>
                            <column>my_jsonb_column</column>
                            <type>com.mysema.query.sql.types.BytesType</type>
                        </typeMapping>

[robshep]

I'm actually using a Hibernate type to perform the CRUD operations on JSON-types columns ( https://gist.github.com/robshep/b0946559dcf7eedfb9bb ) but would be really interested into contributing to a module for querying both JSON and HSTORE typed columns.

[crorodriguezro]

x5

[mix-ologist]

I would also like to see this.

[LuboVarga]

Having jsonb and index support in postgresql it is must to have feature.

[wenerme]

This is my temporary solution https://github.com/wenerme/postjava I do hope querydsl support jsonpath officially.

[voliveira89]

Any news on this?

[alexliesenfeld]

@wenerme I extended your temporary solution: https://github.com/alexliesenfeld/querydsl-jpa-postgres-json

[skuzzle]

In case anyone needs to insert json data into a column, this one did the trick for me:

    // Helper method to create a SQL type cast expression
    public static Expression<String> castToJson(String jsonString) {
        final Expression<String> jsonTypeName = com.querydsl.core.types.dsl.Expressions.constant("JSON");
        return Expressions.operation(String.class,
                SQLOps.CAST, Expressions.constant(jsonString), jsonTypeName);
    }

sqlQueryFactory
        .insert(...)
        .columns(yourJsonColumn)
        .values(castToJson(yourJsonString))
        .execute();