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
