Doing a bulk update in Vercel Postgres by dynamically building query

Question

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.id, 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?

Zegarek · Accepted Answer · 2024-07-18 17:24:14Z

1

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'

edited Jul 18, 2024 at 17:24

answered Jul 18, 2024 at 17:04

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

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

1 Comment

tehawtness Over a year ago

Hi, apologies for my example. I updated it to more mirror my actual data. In the update, the 'id' field is no longer a number but it's a text value and I updated the WHERE clause as 'DATA' is now 'info'

tehawtness · Accepted Answer · 2024-07-19 16:45:36Z

0

With the help of the posted answer, I was able to get the query work by adding a CAST to the WHERE clause:

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

answered Jul 19, 2024 at 16:45

tehawtness

3084 silver badges15 bronze badges

1 Comment

Zegarek Over a year ago

->> already returns text, so this changes nothing. Are you sure you didn't change it to WHERE (info -> 'id')::text = (elem->'id')::text, getting quotes on both sides, or that you tested this on a non-problematic sample? See this demo.

Collectives™ on Stack Overflow

Doing a bulk update in Vercel Postgres by dynamically building query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related