Hot answers tagged

1

$ oerr ora 40573 40573, 00000, "Invalid use of PL/SQL JSON object type." // *Cause: An attempt was made to use a PL/SQL JavaScript Object Notation // (JSON) object type in an invalid context. // *Action: Use PL/SQL JSON object types only inside PL/SQL. Example: set serveroutput on declare l_objects tmp_011040_t2; l_index pls_integer; begin ...


1

Your case is too dynamic for json_populate_record(): it takes a record type which you don't know (nor have) at the time of calling. PROCEDURE calling UPDATE for each JSON key Proof of concept to show what did not work in your attempt. CREATE PROCEDURE record_event ( foo_arg int, name_arg text, data_arg jsonb, occurred_at_arg timestamptz ) AS $...


1

This seems to do what you want: select jsonb_build_object('uid', b.uid, 'nested', jsonb_agg(to_jsonb(m))) from base b join lateral ( select n.uid, n.message from nested n join jsonb_array_elements_text(b.nested) as x(bid) on x.bid = n.uid ) m on true group by b.uid order by b.uid; Online example: https://rextester.com/ASA37564


1

First cte unnest all elements of the array, second one update each element and then simply update the original table building the array again. with ct as ( select id, jsonb_array_elements(data) dt from t ) , ct2 as ( select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2 from ct ) update t set data = (select jsonb_agg(dt2) ...


Only top voted, non community-wiki answers of a minimum length are eligible