Pg-promise: JSONB concatenation query is not parsed correctly

[olegk101]

Expected behavior

This query should concatenate a jsonb array with a new value

const fileName = '200622_filename'
database.none(
 `UPDATE customer
   SET images = images || '["$<fileName>"]'::jsonb
   WHERE id = '$<customerId>'`,
  { fileName, customerId },
)

Actual behavior

Throws an error

 "length": 95,
 "code": "42601",
 "position": "56",
 "file": "scan.l",
 "line": "1150",
 "routine": "scanner_yyerror",
  "error": syntax error at or near "filename"
              at Connection.parseE (.../node_modules/pg/lib/connection.js:581:48)
              at Connection.parseMessage (.../node_modules/pg/lib/connection.js:380:19)
              at Socket.<anonymous> (.../node_modules/pg/lib/connection.js:116:22)

Seems like it can't parse this piece of code '["$<fileName>"]'::jsonb

Steps to reproduce

Send the query to the db

Environment

• Version of pg-promise: 10.5.6
• OS type (Linux/Windows/Mac): Mac
• Version of Node.js: 12.14.0

[vitaly-t]

You are using a lot of manual concatenation, hence the issue. You should never use manual string concatenation.

What is this format? - '["$<fileName>"]'::jsonb? The format looks odd. What is the final sql that works?

And WHERE id = '$<customerId>' should be just WHERE id = $<customerId>.

[boromisp]

I think the intended result is this: images = images || '["200622_filename"]'::jsonb
Technically images = images || '"200622_filename"' should work too.

Use the JSON filter for creating JSON literals: images = images || $<fileName:json>, with either a scalar or an array fileName parameter.

Depending on context you may need to cast the parameter ($<fileName:json>::jsonb) however in this case it is unnecessary.

[vitaly-t]

It is a little confusing why we need first double quotes, then array and then single quotes to wrap it all up. You would have to use a combination of filters to accomplish that, which would be awkward.

I am wondering, if that format is over-complicated, and there is a simpler presentation for that.

[boromisp]

It might be confusing, because the || operator here doesn't meant string concatenation, but jsonb array concatenation.

const fileName = '200622_filename';
database.none(
  `UPDATE customer
SET images = images || $<fileName:json>::jsonb
WHERE id = '$<customerId>'`,
  {
    fileName: [fileName],
    customerId,
  },
);

[vitaly-t]

As I mentioned in the initial reply, '$<customerId>' is also incorrect:

const fileName = '200622_filename';

database.none(`UPDATE customer SET images = images || $<fileName:json>::jsonb
    WHERE id = $<customerId>`,
    {
       fileName: [fileName],
       customerId
    }
);

[boromisp]

Yes, I've completely missed that one.

[vitaly-t]

@olegk101 You've got all the answers now, and even more. Any response?

[z0r1k]

You are using a lot of manual concatenation, hence the issue. You should never use manual string concatenation.

What is this format? - '["$<fileName>"]'::jsonb? The format looks odd. What is the final sql that works?

And WHERE id = '$<customerId>' should be just WHERE id = $<customerId>.

customerId is text value. But you are right, those quotes around it were causing problems. Now query seems to work well.

Thank you!

[olegk101]

@vitaly-t @boromisp Thanks a lot for you help!

P.S. I missed all the comments due to my github notifications not being properly setup😬