Skip to content

Escape colon (‘:’) in custom h2 Query

So I am trying to write a custom query for h2 using its JSON_OBJECT function. JSON_OBJECT uses a format of JSON_OBJECT(key:value) so as a simple example in my Spring repository I am writing a query like @Query(value = "SELECT JSON_OBJECT('id':1)", nativeQuery = true)

When executing that same query in the h2-console it operates as expected but in Spring the colon(‘:’) is treated as a special character for variable insertion so when testing it, it tries to map the following value as a variable which of course throws an error.

I’ve tried escaping the colon with \ and \\ and putting a space between the colon and the value but doesnt seem to help.

Any ideas on how to either escape the char or make spring think the colon is an acceptable character?



Actually you can simply use the alternative syntax JSON_OBJECT(KEY 'id' VALUE 1), there is no need to use escaped \:, escape sequences make your query less readable.

6 People found this is helpful