[FIXED] How can you write a query that checks the value of a JSON column?

Issue

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?

Solution

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.

Answered By - Jens Schauder

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)