Q:

could not determine data type of parameter $1 in knex.js and Postgres

I am using Postgres 9.6.6-alpine and Knex.json on node.
I am attempting to append to a json column.
This works.
  const alertBody = encodeURIComponent(JSON.stringify(req.body.alert));
  updateMal = await db
    .raw(
      `UPDATE malware_submissions
      SET results_children = COALESCE(results_children, '[]'::jsonb) || '["${alertBody}"]'::jsonb
      WHERE request_id=:id
      `,
      { id: parentId },
    )
    .then(() => {
      console.log('help');
    });

I would like to have alertBody be another parameter but this does not work for me.
  const alertBody = encodeURIComponent(JSON.stringify(req.body.alert));
  updateMal = await db
    .raw(
      `UPDATE malware_submissions
      SET results_children = COALESCE(results_children, '[]'::jsonb) || '[":body"]'::jsonb
      WHERE request_id=:id
      `,
      { body: alertBody, id: parentId },
    )
    .then(() => {
      console.log('help');
    });

uh oh undefined
db_1              | ERROR:  could not determine data type of parameter $1
db_1              | STATEMENT:  UPDATE malware_submissions
db_1              |               SET results_children = COALESCE(results_children, '[]'::jsonb) || 
'["$1"]'::jsonb
db_1              |               WHERE request_id=$2
db_1              |
api_1             | parent error

I believe this is related to the quotations but I am not sure what to escape.

A:

Maybe you are trying to do this (note how string concat is done for alertBody variable string in a sql injection safe manner):
await db('malware_submissions')
  .update({
    results_children: db.raw(
      `COALESCE(??, '[]'::jsonb) || ('["' || ? || '"]')::jsonb`, 
      ['results_children', alertBody]
    )
  })
  .where('request_id', parentId);

Other way would be this:
await db('malware_submissions')
  .update({
    results_children: db.raw(
      `COALESCE(??, '[]'::jsonb) || ?::jsonb`, 
      ['results_children', `["${alertBody}]"]`]
    )
  })
  .where('request_id', parentId);

