Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to format PostgreSQL jsonb_set as a prepared statement

Tags:

postgresql

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';

like image 926
D. Walsh Avatar asked Oct 13 '25 12:10

D. Walsh


1 Answers

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));
like image 132
jaisonDavis Avatar answered Oct 15 '25 00:10

jaisonDavis