SqlUpdate.setParameter does not support ArrayNode

[PromanSEW]

Expected behavior

Model field:

@DbJsonB
@Column(nullable = false)
JsonNode progress; // actually ArrayNode

Here is example raw SQL:

update quest set progress = ? where id = 1;

-- or

update quest set progress = to_jsob(?) where id = 1;

//ArrayNode arrayNode = [1, 1, 1] // if call toString()
DB.sqlUpdate(sql).setParameter(arrayNode).execute();

Sets progress to new JSON

Actual behavior

Caused by: jakarta.persistence.PersistenceException: Error with property:0 dt:5001 data:[1,1,1] com.fasterxml.jackson.databind.node.ArrayNode
        at io.ebeaninternal.server.persist.Binder.bindSimpleData(Binder.java:341)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:234)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:187)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:133)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:91)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:83)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:75)
        at io.ebeaninternal.server.persist.ExeUpdateSql.bindStmt(ExeUpdateSql.java:98)
        at io.ebeaninternal.server.persist.ExeUpdateSql.execute(ExeUpdateSql.java:36)
        at io.ebeaninternal.server.persist.DefaultPersistExecute.executeSqlUpdate(DefaultPersistExecute.java:91)
Caused by: java.sql.SQLException: Unhandled data type:5001 bind number:0
        at io.ebeaninternal.server.persist.Binder.bindSimpleData(Binder.java:333)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:234)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:187)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:133)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:91)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:83)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:75)
        at io.ebeaninternal.server.persist.ExeUpdateSql.bindStmt(ExeUpdateSql.java:98)
        at io.ebeaninternal.server.persist.ExeUpdateSql.execute(ExeUpdateSql.java:36)
        at io.ebeaninternal.server.persist.DefaultPersistExecute.executeSqlUpdate(DefaultPersistExecute.java:91)

Maybe I does not know right syntax for set json array, which Ebean can understand?
I need to replace the whole column value
Please help

[PromanSEW]

Also question:

@DbJsonB
JsonNode conditions;

public ObjectNode getConditions() {
	return (ObjectNode) conditions;
}
...
Quest quest = finder.query().select("conditions").setId(id).findOne();
quest.getConditions().put("timeUntil", timeUntil);
quest.update();

Will be conditions updated?

[rob-bygrave]

update quest set progress = to_jsob(?) where id = ?

This is a good use case for stateless update.

var bean = new Quest();
bean.setId(1);
bean.setProgress(...);
bean.update(); // stateless update

[PromanSEW]

@rob-bygrave thanks, I already changed to "setProgress"
Please answer my question about put and update()

[rbygrave]

Like:

    String rawJson = ...;// convert progress to String JSON
    PGobject pgo = new PGobject();
    pgo.setType("jsonb");
    pgo.setValue(rawJson);

DB.sqlUpdate(sql).setParameter(pgo).execute();

[PromanSEW]

Thanks, but question was about POJO modification of JsonNode
Or, how to mark field as dirty?

[PromanSEW]

Also, I think that issue is still actual, because PGobject is actually hack, Ebean should correctly handle ArrayNode itself
I found this:

ebean/ebean-core-type/src/main/java/io/ebean/core/type/PostgresHelper.java

Lines 30 to 34 in 8ed3b76

	public static Object asObject(String pgType, String rawJson) throws SQLException {
	PGobject pgo = new PGobject();
	pgo.setType(pgType);
	pgo.setValue(rawJson);
	return pgo;

So why is it not used for ArrayNode?

[PromanSEW]

@rob-bygrave your solution causes exception:

Caused by: jakarta.persistence.PersistenceException: No ScalarType registered for class org.postgresql.util.PGobject
	at io.ebeaninternal.server.persist.Binder.getScalarType(Binder.java:159)
	at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:182)
	at io.ebeaninternal.server.persist.Binder.bind(Binder.java:133)
	at io.ebeaninternal.server.persist.Binder.bind(Binder.java:91)
	at io.ebeaninternal.server.persist.Binder.bind(Binder.java:83)
	at io.ebeaninternal.server.persist.Binder.bind(Binder.java:75)
	at io.ebeaninternal.server.persist.ExeUpdateSql.bindStmt(ExeUpdateSql.java:98)
	at io.ebeaninternal.server.persist.ExeUpdateSql.execute(ExeUpdateSql.java:36)
	at io.ebeaninternal.server.persist.DefaultPersistExecute.executeSqlUpdate(DefaultPersistExecute.java:91)
	at io.ebeaninternal.server.core.PersistRequestUpdateSql.executeNow(PersistRequestUpdateSql.java:83)
	at io.ebeaninternal.server.core.PersistRequest.executeStatement(PersistRequest.java:138)
	at io.ebeaninternal.server.core.PersistRequest.executeStatement(PersistRequest.java:122)
	at io.ebeaninternal.server.core.PersistRequestUpdateSql.executeOrQueue(PersistRequestUpdateSql.java:93)
	at io.ebeaninternal.server.persist.DefaultPersister.executeOrQueue(DefaultPersister.java:90)
	at io.ebeaninternal.server.persist.DefaultPersister.executeSqlUpdate(DefaultPersister.java:138)
	at io.ebeaninternal.server.core.DefaultServer.execute(DefaultServer.java:1922)
	at io.ebeaninternal.server.core.DefaultServer.execute(DefaultServer.java:1945)
	at io.ebeaninternal.server.core.DefaultSqlUpdate.execute(DefaultSqlUpdate.java:143)

Ebean version 13.23.0

[rbygrave]

With:

update quest set progress = to_jsob(?) where id = 1;
``
or using cast:
```sql
update quest set progress = ?::jsonb where id = 1;

then the bind value should be a String, so

ArrayNode arrayNode = ...; //[1, 1, 1] // if call toString()
String arrayNodeAsJsonString = ...;

DB.sqlUpdate(sql).setParameter(arrayNodeAsJsonString).execute();

---

Will be conditions updated?

That depends on the MutationDetection mode. Did you debug that and see that?

[PromanSEW]

I tried setParameter(arrayNode.toString()) with to_jsonb(?) and ?::jsonb, but it writes actually string "[1, 1, 1]" instead of json array.
Finally, I fixed this problem with StringBuilder of SQL explicitly inject json:

sb.append("progress = '").append(arrayNode.toString()).append("'::jsonb");

Note about single quotes around json.

What about mutation, I switched to raw SQL:

sb.append("conditions['timeUntil'] = to_jsonb(").append(node.longValue()).append(')');