Mass Update JSONB Error

[henryzhang03]

So I am trying to do a query similar to this:

      await db`
        update example
        set
          example_object = v.example_object::jsonb,
        from (values ${db(valuesArray)}) as v (
          example_object
        )
        where example.identifier = v.identifier;`;

example_object in this case is an object in my typescript code. However, if I try to insert it this way, it complains that it is an [object Object] and can't parse it. On the other hand, if I don't include the jsonb cast, it complains that it just text. Can someone please help me resolve this issue? As it is very mission critical.

[henryzhang03]

So I am trying to do a query similar to this:

      await db`
        update example
        set
          example_object = v.example_object::jsonb,
        from (values ${db(valuesArray)}) as v (
          example_object
        )
        where example.identifier = v.identifier;`;

example_object in this case is an object in my typescript code. However, if I try to insert it this way, it complains that it is an [object Object] and can't parse it. On the other hand, if I don't include the jsonb cast, it complains that it just text. Can someone please help me resolve this issue? As it is very mission critical.

hello? Bumping this

[robahtou]

Ran into this issue recently as well. Take into consideration how Postgres handles object serialization. Passing an object gets converted to a string representation [object Object]. You need to explicitly serialize the object to JSON before passing it to the query:

await db`
  update example
  set
    example_object = v.example_object::jsonb
  from (values ${db(valuesArray.map(v => ({ ...v, example_object: JSON.stringify(v.example_object) })))}) as v (
    example_object
  )
  where example.identifier = v.identifier;
`;