I am using R2dbc jasync driver to generate and execute update query on my mysql db table containing a json column.
I am trying to do a JSON_SET operation on a column using R2dbcEntityTemplate and org.springframework.data.relational.core.query.Update.update and org.springframework.data.relational.core.query.Update.set methods. I am trying to generate a query equivalent to
UPDATE mytable SET json_col = JSON_SET(json_col, '$.path_to_update', CAST('{"a":"b"}' AS JSON)) WHERE id=:id
But I get the following error:
invalid json text: "the document is empty." at position 0 in value for column
From what I understand is the Update.set() operation is not delegating the JSON_SET operation to the DB but the DB is trying to interpret the generated string as json which is not valid json at position 0.
How can I achieve this using Update.set() function to delegate the MySQL function call to the DB before DB interprets the value as JSON ?
I tried using dbClient.sql().bind() and it worked but using dbTemplate.update() this is not working.