how to use json fields at Postgres?

[plandem]

right now i have troubles to update JSON fields at db if even i use plain String. (at PG directly plain string is works fine).

is not working:

postgres.update("INSERT INTO locations (parent_id, longitude, latitude, level, is_point, title, config) VALUES(?,?,?,?,?,?,?)", null, 0.0, 0.0, 0, 0, "Root", "{\"foreign_id\":0,\"foreign_table\":\"folders\"}");

is working:

postgres.update("INSERT INTO locations (parent_id, longitude, latitude, level, is_point, title, config) VALUES(null,0.0,0.0,0,0,'Root','{\"foreign_id\":0,\"foreign_table\":\"folders\"}')");

[plandem]

i tried same way as with enums (TypeConversionRegistry), but still no luck. same error:

rolled back transaction because of exception: org.postgresql.util.PSQLException: ERROR: column "config" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 113

where JsonDB (JSONObject from alibaba) is:

public class JsonDB extends JSONObject {
    public String from() {
        return this.toJSONString();
    }

    static public JsonDB to(Object raw) {
        return  (JsonDB) JSON.parse(raw.toString());
    }
}

and register type as:

            types.registerConversions(Object.class, JsonDB.class, JsonDB::to, JsonDB::from);

this's most critical for me now, because everything else i already did or did workarounds :(

[plandem]

right now i have the only solution that works - use ?::json placeholders. still hope that there will be solution to register custom type for such cases.

[komu]

I guess this is a problem with PostgreSQL's JDBC-driver rather than with Dalesbred. What if you execute a query directly using raw JDBC, e.g.

postgres.withVoidTransaction(tx -> {
    try (PreparedStatement ps = tx.getConnection().prepareStatement("INSERT INTO locations (parent_id, longitude, latitude, level, is_point, title, config) VALUES(?,?,?,?,?,?,?)")) {
        ps.setNull(1, Types.NUMERIC);
        ps.setDouble(2, 0.0);
        ps.setDouble(3, 0.0);
        ps.setInt(4, 0);
        ps.setInt(5, 0);
        ps.setString(6, "Root");
        ps.setString(7, "{\"foreign_id\":0,\"foreign_table\":\"folders\"}");
        ps.executeUpdate();
    }
});

If this works, then the problem is with Dalesbred, but if not, then the problem resides in with PostgreSQL's JDBC-driver.

That said, PostgreSQL will probably allow you to create any native objects if you create PGobjects yourself. Dalesbred's PostgreSQLDialect uses this to create native enums. So if you convert your JSonDB.from method to following it could work:

public PGobject from() {
    PGobject object = new PGobject();
    object.setType("json");
    object.setValue(this.toJSONString();
    return object;
}

You'll need to add PostgreSQL's jdbc-driver as a compile-time dependency, but that's it.

I might add some support for these things directly to Dalesbred at some point, but it's hard to draw the line how much native database functionality I should try to abstract.

[plandem]

tried to write as you suggested, but same result - error. Yes, that's JDBC driver problem, as i said i fixed it by using '?::json' placeholder instead of '?'. Right now i have 'workaround' for json fields, but it's not so nice, some 'typeConversion' would be cool, if possible. Just like enums.

[plandem]

Well, as result i came to next: 1) still custom type via typeRegister 2) added 2 special methods for conversion to/from Postgres (like you suggested for PGobject, same way as enums), in that case i don't need to patch placeholders and still quite flexible, because custom type is not hardcoded for Postgres, just 2 methods.

Your library is very good architected, thanks man :)

[komu]

Right, this is the proper way. I was trying merely to explain the mechanism (register the conversion to/from PGobject) and neglected to explain the proper way for it (doing it outside your data types). So unfortunately I ended up with an oversimplified and misleading example.

But great that you figured out the correct way despite my terseness.

The manual could probably use some more examples, but on the other hand, I'd like to keep it relatively short and focused so that there's any hope of someone actually reading it. But suggestions for improvement and/or pull requests are welcome. :)

[Hepale]

I solved by: Into the sql statement, I added this where I have the json field:

INSERT INTO map_file(type, data) VALUES (?, CAST(? AS json)) RETURNING id