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';
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With