insert for each element in jsonb array with 'instead of' trigger on a view

I'm using a view to report related rows as an array. It would be convenient to also insert those rows with a instead of insert trigger on the view. The rows to insert will need to construct a json object with keys from a related table.

My current incomplete implementation needs where task in JSONB_ARRAY. I don't know if that is possible.

in addition to a visit table with primary id colum vid, I have

task table to pull from.

|  task  |       sections          |
+--------+-------------------------+
|task_A  | ["section1","section2"] |
|task_B  | ["part1",   "part2"   ] |

and a visit_task table to populate

| vid |  task  |                   measures                     |
+-----+--------+------------------------------------------------+
| 1   | task_C | ["level1": "value added later","level2": null] |
| 1   | task_E | ["q1": null,"q2": null]                        |

want:

insert into vt_view (vid,tasks) values (1,'["task_A","task_B"]::jsonb)

to actually do:

insert into visit_task (vid,task,measures) 
values (1,'task_A','{"section1": null, "section2": null}'::jsonb);
insert into visit_task (vid,task,measures) 
values (1,'task_B','{"part1": null, "part2": null}'::jsonb);

current incomplete trigger solution excerpt:

insert into visit_task from
 select  
  NEW.vid as vid,
  NEW.task as task,
  -- *MAGIC* to create json object; measures list becomes object with null vals 
  row_to_json(_) from (select json_object(t.measures, 'null')) as measures
  -- /MAGIC
  from task t
  -- *MAGIC* where we only grab the tasks within the array NEW.tasks
  where t.task in (select * from NEW.tasks) 
  -- /MAGIC

Unfortunately, the server doesn't know what to do with/in the MAGIC flags. Neither do I.

Solution

To make the task easier create an auxiliary function to expand sections to desired format:

create or replace function expand_sections(jsonb)
returns jsonb language sql as $$
    select jsonb_object_agg(e, null)
    from jsonb_array_elements_text($1) e
$$;

-- test the function:
select task, expand_sections(sections)
from task;

  task  |           expand_sections            
--------+--------------------------------------
 task_A | {"section1": null, "section2": null}
 task_B | {"part1": null, "part2": null}
(2 rows)

Use the function in the trigger function:

create or replace function trigger_instead_on_vt_view()
returns trigger language plpgsql as $$
begin
    insert into visit_task
    select vid, task, expand_sections(sections)
    from (
        select new.vid, task
        from jsonb_array_elements_text(new.tasks) task
        ) sub
    join task using(task);
    return null;
end $$;

create trigger trigger_instead_on_vt_view
instead of insert on vt_view
for each row execute procedure trigger_instead_on_vt_view();

insert into vt_view (vid,tasks) values (1,'["task_A","task_B"]'::jsonb);

select * from visit_task;

 vid |  task  |               measures               
-----+--------+--------------------------------------
   1 | task_A | {"section1": null, "section2": null}
   1 | task_B | {"part1": null, "part2": null}
(2 rows)

If you do not want to create the auxiliary function, use this variant:

create or replace function trigger_instead_on_vt_view()
returns trigger language plpgsql as $$
begin
    insert into visit_task
    select vid, task, measures
    from (
        select new.vid, task
        from jsonb_array_elements_text(new.tasks) task
        ) s
    join (
        select task, jsonb_object_agg(e, null) measures
        from task, jsonb_array_elements_text(sections) e
        group by 1
        ) t
    using (task);
    return null;
end $$;