0

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?

Jacob Hughes
  • 35
  • 1
  • 6
  • Does this answer your question? [How to escape colon \`:\` within a native SQL query in Hibernate?](https://stackoverflow.com/questions/32076306/how-to-escape-colon-within-a-native-sql-query-in-hibernate) – Giorgi Tsiklauri Sep 03 '20 at 20:23
  • 1
    @GiorgiTsiklauri Presumably not, because all that answer says is to use two backslashes which the OP already tried. – Michael Sep 03 '20 at 20:26
  • Double chech \\ escaping as it shold do the tricke – Antoniossss Sep 03 '20 at 20:29
  • @Michael yes, comment author also states that it didn't work initially, but then they elaborate (in the comments), and finally they have the solution. Maybe OP can, at least, double check the provided snippet, with his code. – Giorgi Tsiklauri Sep 03 '20 at 20:30

1 Answers1

1

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.

Evgenij Ryazanov
  • 6,960
  • 2
  • 10
  • 18