How to format PostgreSQL jsonb_set as a prepared statement

Question

var query=`UPDATE "public"."vehicle_ads" 
           SET options=
                   jsonb_set(
                    options, 
                    '{$1, ${subgroup}, -1}', 
                    '{"name": "${optionName}"}'::jsonb
                   )
           WHERE "vehicleId"=${id}`;

I'm having the following issues with the above query. If I replace $1 with ${group}, no error. But if I leave $1 and pass group as a prepared statement, I get the error...

bind message supplies 1 parameters, but prepared statement "" requires 0

My goal is to tokenize the entire query, e.g.:

var query=`UPDATE "public"."vehicle_ads" 
       SET options=
               jsonb_set(
                options, 
                '{$1, $2, -1}', 
                '{"name": $3}'::jsonb
               )
       WHERE "vehicleId"=$4`;

Then...

could not determine data type of parameter $1

I know I'm lost in a formatting soup of template strings and Postgres, unsure what needs ticks, single quotes, or double. Any help is greatly appreciated.

EDIT:

Here's what I'm doing with detail.

I have a nested object with vehicle options data stored as a jsonb field. It has the form:

{
  "Group": {
     "SubGroup": [
           {
              "name": string,
              "hasOption": bool
           }
           ...
      ]
      ...
   }
  ...
}

I want to edit the name in a sub group. For instance Powertrain.Drivetrain[0].name='AWD';

Placeholders within strings won't work (at least with prepared statements). Please try jsonb_set(options, ARRAY[$1, $2, '-1', 'name']::text[], $3::jsonb) — pozs
– pozs, Commented Mar 17, 2016 at 8:33
@pozs thanks for giving it a shot... You exact solution throws Error: invalid input syntax for type json. The first argument of jsonb_set is a jsonb object, second is a text path, third is the new value in jsonb. I tried jsonb_set(options, '{' || ARRAY[$1,$2,'-1']::text[] || '}', '{"name": $3}'::jsonb but that returns Error: malformed array literal: "{". — D. Walsh
– D. Walsh, Commented Mar 17, 2016 at 18:20
@DWalsh that error message indicates that what you want to set to the name property, is not a valid json(b). You could try to convert to json(b) with to_json($3) or to_jsonb($3), but it would be helpful to include the parameter types (and perhaps example values for the parameters) in the question. — pozs
– pozs, Commented Mar 17, 2016 at 19:06
@DWalsh my bad, didn't notice that you actually didn't try what I suggested. you need to include the name property to the text path, because placeholders for parameters won't work in any literals (so '{"name": $3}'::jsonb also won't work) — pozs
– pozs, Commented Mar 17, 2016 at 19:10
@pozs thank you! You're right, it wasn't working how I expected because it replaced the entire object with the update. (I didn't catch it because of my React/Redux optimistic update. Oops) Your way worked! I edited my original question. Now that I'm up to speed, I tried this query: jsonb_set(options, ARRAY[$1, $2, $3, $4], to_json($5)::jsonb) and it returned error message could not determine polymorphic type because input has type "unknown". jsonb_set(options, ARRAY[$1, $2, $3, $4], "${value}"'::jsonb) works perfectly — D. Walsh
– D. Walsh, Commented Mar 17, 2016 at 22:39

jaisonDavis · Accepted Answer · 2021-05-25 04:11:59Z

5

You'll have to convert the variable to text before converting it to jsonb using to_jsonb

UPDATE table SET body = jsonb_set(options, '{key}', to_jsonb($1::text));

answered May 25, 2021 at 4:11

jaisonDavis

1,4472 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to format PostgreSQL jsonb_set as a prepared statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related