How to use JSONB operations with pg-promise

Question

I'm struggling with pg-promise and getting it to work with JSONB. I'm using prepared statements, i've attempted two variations for this SQL command (i just wanna udpate the jsonb column with a new set of values, basically appending):

  db.none({
    name: 'updateLike',
    text:`UPDATE posts
    SET likes_by = jsonb_set(likes_by, '{$1~}', '$2~'),
    likes_count = likes_count + 1
    WHERE post_id = $3`,
    values: [user_id, username, pid]
  })

or

  db.none({
    name: 'updateLike',
    text:`UPDATE posts
          SET likes_by = likes_by || '{"$1": "$2"}',
          likes_count = likes_count + 1
          WHERE post_id = $3`,
    values: [user_id, username, pid]
  })

It gives me errors like:

error: invalid input syntax for type json

or

error: could not determine data type of parameter $1

Anyone done this before?

Answer 1

This is what I ended up doing:

  db.none(`UPDATE posts
           SET likes_by = likes_by || '{$1~: $2~}',
           likes_count = likes_count + 1
           WHERE post_id = $3#`,
           [user_id, username, pid]
  )

Works. Thanks!