Title: JPA query for a JSONB column in Postgres
Tags: java;hibernate;postgresql;jpa
Question: I'm trying to create a query with JPA for a JSONB column in a Postgres DB. The query will pretty much look like this example from the Postgres JSON Documentation:
-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc-&gt;'guid', jdoc-&gt;'name' FROM api WHERE jdoc @&gt; '{"company": "Magnafone"}';
where jdoc is the JSONB column in table api.
So far executing this query is easy and not a problem. However if I try to parameterize the query I'm stuck.
Here are a few queries I've tried:
1) This does not work, because the ? is not recognized between the '-signs:
String name= "Magnafone";
JPA.em().createNativeQuery("SELECT jdoc FROM api
WHERE jdoc @&gt; '{\"name\": ?}'", Record.class)
.setParameter(1, name)
Error:
play.api.Application$$anon$1: Execution exception[[IllegalArgumentException: org.hibernate.QueryParameterException: Position beyond number of declared ordinal p
arameters. Remember that ordinal parameters are 1-based! Position: 2]]
at play.api.Application$class.handleError(Application.scala:293) ~[play_2.10-2.2.3.jar:2.2.3]
at play.api.DefaultApplication.handleError(Application.scala:399) [play_2.10-2.2.3.jar:2.2.3]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:
2.2.3]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:
2.2.3]
at scala.Option.map(Option.scala:145) [scala-library-2.10.3.jar:na]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3.applyOrElse(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:2.2.3]
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordin
al parameters are 1-based! Position: eanderson@example.com(QueryImpl.java:451) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:72) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at models.lol.player.IPlayer.getPlayerByName(IPlayer.java:22) ~[na:na]
at controllers.LoadTeamController.loadTeamData(LoadTeamController.java:23) ~[na:na]
at Routes$$anonfun$routes$1$$anonfun$applyOrElse$2$$anonfun$apply$2.apply(routes_routing.scala:57) ~[na:na]
at Routes$$anonfun$routes$1$$anonfun$applyOrElse$2$$anonfun$apply$2.apply(routes_routing.scala:57) ~[na:na]
Caused by: org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position:
at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:80) ~[hibernate-core-4.1.8.Final.jar:eanderson@example.com(ParameterMetadata.java:86) ~[hibernate-core-4.1.8.Final.jar:eanderson@example.com(AbstractQueryImpl.java:450) ~[hibernate-core-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.internal.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:422) ~[hibernate-core-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:445) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:72) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]</code&gt;
2) I've found this in another SO-Thread. Does not work though, because the || shows up in the query and throws an SQL Exception.
JPA.em().createNativeQuery("SELECT jdoc FROM api
WHERE jdoc @&gt; '{\"name\": ' || :name ||'}'", Record.class)
.setParameter("name", name)
Error:
play.api.Application$$anon$1: Execution exception[[IllegalArgumentException: org.hibernate.QueryParameterException: Position beyond number of declared ordinal p
arameters. Remember that ordinal parameters are 1-based! Position: 2]]
at play.api.Application$class.handleError(Application.scala:293) ~[play_2.10-2.2.3.jar:2.2.3]
at play.api.DefaultApplication.handleError(Application.scala:399) [play_2.10-2.2.3.jar:2.2.3]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:
2.2.3]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:
2.2.3]
at scala.Option.map(Option.scala:145) [scala-library-2.10.3.jar:na]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3.applyOrElse(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:2.2.3]
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordin
al parameters are 1-based! Position: eanderson@example.com(QueryImpl.java:451) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:72) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at models.lol.player.IPlayer.getPlayerByName(IPlayer.java:22) ~[na:na]
at controllers.LoadTeamController.loadTeamData(LoadTeamController.java:23) ~[na:na]
at Routes$$anonfun$routes$1$$anonfun$applyOrElse$2$$anonfun$apply$2.apply(routes_routing.scala:57) ~[na:na]
at Routes$$anonfun$routes$1$$anonfun$applyOrElse$2$$anonfun$apply$2.apply(routes_routing.scala:57) ~[na:na]
Caused by: org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position:
at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:80) ~[hibernate-core-4.1.8.Final.jar:eanderson@example.com(ParameterMetadata.java:86) ~[hibernate-core-4.1.8.Final.jar:eanderson@example.com(AbstractQueryImpl.java:450) ~[hibernate-core-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.internal.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:422) ~[hibernate-core-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:445) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:72) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
I've tried a few other things that made Postgres or JPA throw up in one or another way and didn't manage to get the JSON Parameter while using the parameterization from JPA.
JPA.em().createNativeQuery("SELECT jdoc FROM api
WHERE jdoc @&gt; '{\"name\": ' || ? ||'}'", Record.class)
.setParameter(1", name)
Error:
play.api.Application$$anon$1: Execution exception[[PersistenceException: org.hibernate.exception.DataException: FEHLER: ung³ltige Eingabesyntax f³r Typ json
Detail: Die Eingabezeichenkette endete unerwartet.
Position: 62
Where: JSON-Daten, Zeile 1: {"name": ]]
at play.api.Application$class.handleError(Application.scala:293) ~[play_2.10-2.2.3.jar:2.2.3]
at play.api.DefaultApplication.handleError(Application.scala:399) [play_2.10-2.2.3.jar:2.2.3]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:
2.2.3]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:
2.2.3]
at scala.Option.map(Option.scala:145) [scala-library-2.10.3.jar:na]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3.applyOrElse(PlayDefaultUpstreamHandler.scala:264) [play_2.10-2.2.3.jar:2.2.3]
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: FEHLER: ung³ltige Eingabesyntax f³r Typ json
Detail: Die Eingabezeichenkette endete unerwartet.
Position: 62
Where: JSON-Daten, Zeile 1: {"name":
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:266) ~[hibernate-entitymanager-4.1.8.Final.jar:4.1.8.Final]
at models.lol.player.IPlayer.getPlayerByName(IPlayer.java:24) ~[na:na]
at controllers.LoadTeamController.loadTeamData(LoadTeamController.java:23) ~[na:na]
at Routes$$anonfun$routes$1$$anonfun$applyOrElse$2$$anonfun$apply$2.apply(routes_routing.scala:57) ~[na:na]
Caused by: org.hibernate.exception.DataException: FEHLER: ung³ltige Eingabesyntax f³r Typ json
Detail: Die Eingabezeichenkette endete unerwartet.
Position: 62
Where: JSON-Daten, Zeile 1: {"name":
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:134) ~[hibernate-core-4.1.8.Final.jar:eanderson@example.com(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.1.8.Final.jar:4.1.8.
Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~[hibernate-core-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~[hibernate-core-4.1.8.Final.jar:4.1.8.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) ~[hibernate-core-4.
1.8.Final.jar:4.1.8.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) ~[hibernate-core-4.1.8.Final.jar:4.1.8.Final]
Caused by: org.postgresql.util.PSQLException: FEHLER: ung³ltige Eingabesyntax f³r Typ json
Detail: Die Eingabezeichenkette endete unerwartet.
Position: 62
Where: JSON-Daten, Zeile 1: {"name":
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na]
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) ~[postgresql-9.2-1003-jdbc4.jar:na]
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) ~[postgresql-9.2-1003-jdbc4.jar:na]
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) ~[postgresql-9.2-1003-jdbc4.jar:na]
FYI: This works (don't worry, I'm not using it):
JPA.em().createNativeQuery("SELECT jdoc-&gt;'guid', jdoc-&gt;'name' FROM api
WHERE jdoc @&gt; '{\"company\": \""+company+"\"}'", Record.class)
Also: I'm using the createNativeQuery because the @&gt;-Operator is not recognized by JPA.
Thanks.
Comment: Doesn't work either :/ He complains about the "-symbol. If i try `'{\"company\": ' || ? || '}'` the error says that the input string ends unexpectedly.

Comment: Sorry, copy-paste error. I'll fix it. Not the code exactly I'm using. I'll post the exact error messages soon.

Comment: Added the stracktrace.

Comment: Thanks pozs. That worked! If you'd post this as an answer I could accept it.

Comment: Try `"... WHERE jdoc @> cast('{\"company\":' || to_json(cast(? as text)) || '}' as json)"` (or `jsonb` at the last cast, if that's appropriate).

Comment: Try the `||` approach, but it should be something like `'{\"company\": \"' || ? || '\"}'`. Of course, use `setParameter(1, company)`.

Comment: It would be helpful if you add the actual error messages - with full stack traces if available - to your question under each attempted option. By the way, you don't seem to close the string with `"` in any of your examples above. Is this the actual code you tried?

Here is the accepted answer: pozs posted the Answer as a comment:
Try "... WHERE jdoc @> cast('{\"company\":' || to_json(cast(? as text)) || '}' as json)" (or jsonb at the last cast, if that's appropriate).


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 7, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:02/02/15', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<java><hibernate><postgresql><jpa>', 'Title': 'JPA query for a JSONB column in Postgres', 'ViewCount': 3936, 'id': 28284997, 'user_AboutMe': '<h1>SOreadytohelp</h1>\n', 'user_AccountId': 88601.0, 'user_DisplayName': 'Timo', 'user_Reputation': 433.0, 'user_Views': 43.0, 'user_id': 244946.0}}