I have a simple table in Vercel Postgres:
export const sets = createTable("card_table", {
id: varchar("id").primaryKey(),
info: jsonb("info"),
});
I have populated the table with data and would like to do a bulk update. I figured out how to update one at a time:
const new_data = {
id: "idx",
new_field: {
x: 'test'
}
}
const query =
`
UPDATE card_table
SET
info = jsonb_set(
info, '{new_field}', '${JSON.stringify(new_data.new_field)}'
)
WHERE info ->> 'id' = '${new_data.id}'
`
Now I’m trying to modify the query to do bulk updates:
const data = [
{
"id": "id1",
"new_field": {
"x": "test"
},
{
"id": "id2",
"new_field": {
"x": "test"
}
]
const updateValues = JSON.stringify(
data.map((d) => ({ id: d, new_field: d.new_field }))
);
const query =
`
UPDATE card_table
SET
info = jsonb_set(
info, '{new_field}', (elem->'new_field')::jsonb
)
FROM jsonb_array_elements('${updateValues}')
WHERE info ->> 'id' = (elem->'id')::text
`
but the rows aren’t updating and I’m not getting any error messages. What am I doing wrong and/or is there a better way to go about this?
Both j->>'id' and (j->'id')::text will end up resulting in a text value, but it won’t necessarily be the same because of quoting:
demo at db<>fiddle
with cte(DATA,elem) as (values
('{"id":"1"}'::jsonb,'{"id":"1"}'::jsonb)
)
select DATA ->> 'id'
, (elem->'id')::text
, DATA ->> 'id' = (elem->'id')::text as "are these equal"
from cte;
| ?column? | text | are these equal |
|---|---|---|
| 1 | “1” | False |
Which means you might want to change your WHERE condition:
WHERE DATA ->> 'id' = elem->>'id'
Assuming there’s a 3rd column called DATA and based on contents of which you’re trying to update. Otherwise, I think you wanted
UPDATE card_table
SET
info = jsonb_set(
card_table.info, '{new_field}', (j.elem->'new_field')::jsonb
)
FROM jsonb_array_elements('${updateValues}') as j(elem)
WHERE card_table.id = j.elem->>'id'
1
With the help of the posted answer, I was able to get the query work by adding a CAST to the WHERE clause:
Previous:
WHERE info ->> 'id' = (elem->'id')::text
Fixed:
WHERE (info ->> 'id')::text = (elem->'id')::text
apparently when you cast to ‘text’, quotes are added so previously the comparison would be something like: x = “x” which is False
1