Desire to use jsonb for payload column in JdbcEventStorageEngine, non-spring setup.
I’ve setup the Jackson serializer okay, and a custom EventTableFactory creates the correct jsonb column type for ddl if no table, however it looks like I’m missing a cast as at the time the psql driver submit the query the paramValue is the serialized json and paramtype is string, which gives " ERROR: column “payload” is of type jsonb but expression is of type character varying". I guess I need to change the sql query value part to (?)::jsonb, but I can’t see how to cleanly do this without override appendEvents in JdbcEventStorageEngine. Am I missing a trick?
If you want to do this, subclassing JdbcEventStorageEngine would be the way to go, as you suggest. There’s no trick you’re missing.
Taking one step back, let’s look at why you would do this. There’s no benefit to using jsonb columns for normal Axon Framework operations. But, you will get more options for doing ad-hoc queries against your events. So I’m guessing this may be your reason for wanting this as well.
Since this is a quite common use case, we’ve implemented a great feature for this in AxonDB, which is our built-for-purpose event store that’s an alternative to storing events in an RDBMS. It has an ad-hoc query functionality allowing you to filter and project events, and use jsonpath expressions on the payload in the process. Query results are updated in real time as new events come in.
If the background of your question is the need for such ad-hoc queries, I’d suggest checking out AxonDB.