Menu
How can I pass a JsObject into a json data type field in a PostgreSQL 9.3 database with Anorm without having to cast it as a string?
Given a PostgreSQL 9.3 table such as:
create table profiles
(
id serial primary key,
profile json null
);
With Play 2.2, this test succeeds:
package helpers
import anorm._
import org.specs2.mutable._
import org.specs2.runner._
import org.junit.runner._
import play.api.db.DB
import play.api.libs.json._
import play.api.test._
@RunWith(classOf[JUnitRunner])
class AnormTest extends Specification {
"AnormTest" should {
"insert a JSON object" in new WithApplication {
val profile = Json.obj("language" -> "en")
val sql = SQL("insert into profiles (profile) values (CAST({profile} AS json))")
.on("profile" -> profile.toString)
DB.withConnection { implicit c => sql.execute() }
}
}
}
But with these lines changed:
val sql = SQL("insert into profiles (profile) values ({profile})")
.on("profile" -> profile)
It produces this error:
org.postgresql.util.PSQLException:
Can't infer the SQL type to use for an instance of play.api.libs.json.JsObject.
Use setObject() with an explicit Types value to specify the type to use.
Since with Anorm we usually pass the appropriate data types instead of text (for instance, an UUID object for a column of uuid data type), it's not optimal having to convert the JsObject to a string and cast it back to the json data type in the SQL statement.
For an example of this issue and its workaround, please refer to Using PostgreSQL's native JSON support in Play Framework 2.1-RC1.
How can this be avoided with Anorm in order to pass the JsObject directly as a json data type?
433
Some of the popular Operators useful for inserting JSON into PostgreSQL are: -> Operator: It enables you to select an element from your table based on its name. Moreover, you can even select an element from an array using this operator based on its index.
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.
Postgres offers a jsonb_set function for updating JSON fields. The second parameter path defines, which property you want to update. To update items in an array, you can use an index-based approach. To update the first entry in the items array in the example above, a path woud look like this: {items, 0, customerId} .
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
For the Play 2.4 and above use the anorm.Object(value: org.postgresql.util.PGobject) class instead of value directly:
val pgObject = new org.postgresql.util.PGobject();
pgObject.setType("json");
pgObject.setValue(profile);
val sql = SQL("insert into profiles (profile) values ({profile})")
.on("profile" -> anorm.Object(pgObject))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
