PostgreSQL jsonb обновляет несколько вложенных полей

I таблица с полем id и полем jsonb в базе данных postgresql. jsonb имеет структуру, которая выглядит примерно так:

{
    "id": "some-id",
    "lastUpdated": "2018-10-24T10:36:29.174Z",
    "counters": {
        "counter1": 100,
        "counter2": 200
    }
}

Что мне нужно сделать, так это обновить lastModified и один из счетчиков:

def update(id: String, counter: Option[String])

Так, например, если я делаю update("some-id", Some("counter2")), мне нужно, чтобы lastUpdated было текущей датой и временем, а counter2 увеличивалось до 201.

Я использую ScalikeJDBC, и вот где я до сих пор:

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()
}

Но я получаю следующую ошибку:

org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3

Я пробовал другие подходы, но мне не удалось заставить его работать. Можно ли написать это как один запрос?

Вот сломанная скрипка, чтобы помочь с тестированием https://www.db-fiddle.com/f/bsteTUMXDGDSHp32fw2Zop/1


postgresql sql scala scalikejdbc
person Maria Livia    schedule 22.11.2018    source источник
comment
У меня нет опыта работы с ScalikeJDBC, но запрос Postgres может выглядеть так: db- fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1   -  person Ionuț G. Stan    schedule 22.11.2018
comment
Или, может быть, это, чтобы избавиться от двойного вызова jsonb_set: db-fiddle.com/f /mSugD6cNPc5EiujCtRjZjd/2 Проблема с || заключается в том, что он не выполняет рекурсивное слияние.   -  person Ionuț G. Stan    schedule 22.11.2018
comment
Логично, попробую, спасибо!   -  person Maria Livia    schedule 22.11.2018
comment
И версия, которая обновляет вложенный счетчик, только если существует обновление: db-fiddle.com/ f/mSugD6cNPc5EiujCtRjZjd/3 Я понимаю, что не могу ответить на ваш вопрос, потому что я не знаю, как работает ScalikeJDBC, но я надеюсь, что это поможет.   -  person Ionuț G. Stan    schedule 22.11.2018
comment
На самом деле это было очень полезно, так как мне наконец удалось написать запрос, который делает то, что я хочу: db -fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 К сожалению, я до сих пор не могу заставить его работать в Scalike, но это только начало.   -  person Maria Livia    schedule 22.11.2018

Ответы (1)


arrow_upward
1
arrow_downward

Я мало что знаю о типе jsonb в PostgreSQL, но кажется невозможным передать все в качестве параметров привязки в PreparedStatement JDBC. Я должен сказать, что вам, возможно, придется использовать SQLSyntax.createUnsafely для обхода PreparedStatement, как показано ниже:

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"))
person Kazuhiro Sera    schedule 22.11.2018