Suppose I have a table with three columns: id, name, state. The state column is of type jsonb and the structure would always have (as a minimum) a key called active e.g.
{"name": "some_name", active: true, ...}
{"name": "one_two", active: false, ...}
I've got everything to work with serializing and deserializing by writing my own @WritingConverter and @ReadingConverter (to a PGobject) and so I could write a query which then at the Java level performs the filter so only those which are 'active' remain.
But how can I, in Spring Data JDBC, write a query that would allow me to interrogate the json column at the level of the DB?
You'd use use the @Query annotation and whatever facilities your database offers to query json.
Just as an example this tutorial for working with JSON in Postgres suggests that something like should work.
@Query("select * from mytable where state ->> 'active' = :active ")
List<MyTable> findAllActive(String active)
You probably can convert state ->> 'active' to something suitable so you can use a boolean as the method argument, but I'd start with something simple.