I table that has an id
field and a jsonb
field in a postgresql db. The jsonb has a structure that looks something like this:
{
"id": "some-id",
"lastUpdated": "2018-10-24T10:36:29.174Z",
"counters": {
"counter1": 100,
"counter2": 200
}
}
What I need to do is update the lastModified
and one of the counters:
def update(id: String, counter: Option[String])
So for example if I do update("some-id", Some("counter2"))
I need the lastUpdated
to be the current date time and counter2
to be incremented to 201
.
I'm using ScalikeJDBC and this is where I got so far:
def update(id: String, counter: Option[String]): Option[ApiKey] = DB localTx { implicit session =>
val update =
if(counter.isDefined)
sqls"""'{"lastUpdated": ${DateTime.now()}, "counters": {'${counter.get}: COALESCE('counters'->>${counter.get},'0')::int'}'"""
else
sqls"""'{"lastUpdated": ${DateTime.now()}}'"""
sql"UPDATE apiKey SET content = content || $update WHERE id = $key".update().apply()
}
But I get the following error:
org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3
I've tried other approaches, but I wasn't able to make it work. Is it possible to write this as a single query?
Here's a broken fiddle to help with testing https://www.db-fiddle.com/f/bsteTUMXDGDSHp32fw2Zop/1
I don't know a lot about PostgreSQL's jsonb
type, but it seems impossible to pass everything as bind parameters in a JDBC PreparedStatement. I have to say that you may have to use SQLSyntax.createUnsafely to bypass PreparedStatement as below:
def update(id: String, counter: Option[String]): Unit = DB localTx { implicit session =>
val now = java.time.ZonedDateTime.now.toOffsetDateTime.toString
val q: SQLSyntax = counter match {
case Some(c) =>
val content: String =
s"""
jsonb_set(
content || '{"lastUsed": "${now}"}',
'{counters, $c}',
(COALESCE(content->'counters'->>'$c','0')::int + 1)::text::jsonb
)
"""
SQLSyntax.createUnsafely(s"""
UPDATE
example
SET
content = ${content}
WHERE
id = '$id';
""")
case _ =>
throw new RuntimeException
}
sql"$q".update.apply()
}
update("73c1fa11-bf2f-42c9-80fd-c70ac123fca9", Some("counter2"))