0

I want to adjust a JSONB column by adding a new array prop in an existing record, but I struggle with getting this updated record.

Something like this:

DO $$ 
DECLARE
  mt table1%ROWTYPE;
  arr TEXT[];
BEGIN
    FOR mt IN 
        SELECT * FROM table1
    LOOP
        arr := ARRAY(SELECT col1 FROM table2 WHERE id_ext=mt.id);
        
        raise notice 'arr=%', arr;   -- nice array printed here

        mt.column.arr_prop=arr;   -- !!!!! This does not work

        UPDATE table1 SET column=mt.column WHERE id=mt.id;

    END LOOP;
END $$;
Aleks
  • 5,674
  • 1
  • 28
  • 54
  • `JSONB` and Postgres `ARRAY`(TEXT[]) are two different types. You need to add the table definition for `table1` to your question. Though I suspect this `mt.column.arr_prop=arr` does not work because you are mixing types. – Adrian Klaver Dec 11 '22 at 21:45
  • 1
    try `array_to_json` – nbk Dec 11 '22 at 22:01

0 Answers0