PostgreSQL jsonb update multiple nested fields

Question

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 have experience with ScalikeJDBC, but the Postgres query can look like this: https://www.db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1 — Ionuț G. Stan, Nov 22 '18 at 10:41
Or maybe this, to get rid of the double `jsonb_set` call: https://www.db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/2 The problem with `||` is that it's not doing a recursive merge. — Ionuț G. Stan, Nov 22 '18 at 10:44
And a version that updates the nested counter only if an update exists: https://www.db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/3 I realize I can't really answer your question because I don't know how ScalikeJDBC works, but I hope it helps. — Ionuț G. Stan, Nov 22 '18 at 10:56
Actually this was very helpful as I finally managed to write a query that does what I want: https://www.db-fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 Unfortunately I still can't make it work in Scalike, but it's a start. — Maria Livia, Nov 22 '18 at 14:32

Kazuhiro Sera · Accepted Answer · 2018-12-08T05:21:07.120

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"))

PostgreSQL jsonb update multiple nested fields

1 Answers1