Deconstruct json and insert/update it into a different table in an updatable view I have a view that essentially presents a denormalized view onto my data. 

It's roughly like having a blog table and a comments table, and the view then shows a blog post per row, with all the comments belonging to that post in an array in a json field.

The view uses the following code to achieve that:

  (SELECT array_to_json(array_agg(row_to_json(x.*))) AS array_to_json  FROM
     (SELECT foo, bar
      FROM table_b
      WHERE a.id = table_b.abc_id) x) AS baz


I've made the view updatable, which works for everything except this kind of column. I also have some typical many-to-many relationships in that data, and those are automatically updated when you edit them via the view. But I couldn't figure out how to do the same for this pseudo-json column.

Essentially, I need to pass this json to an update/insert statement in my INSTEAD OF trigger. I experimented with json_to_recordset, but I couldn't get that to do what I need. 

Any idea how I could achieve this?
From the docs on Updatable Views


  Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:
  
  
  The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
  The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
  The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
  The view's select list must not contain any aggregates, window functions or set-returning functions.
  


Your view is twice-over not "automatically" updatable. However,


  You can get the effect of an updatable view by creating INSTEAD OF triggers on the view, which must convert attempted inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE TRIGGER. Another possibility is to create rules (see CREATE RULE), but in practice triggers are easier to understand and use correctly.


I strongly suggest you don't go down that route though and you consider fixing your schema and moving away from jsonb. It's not made to be used in that fashion.

Here is how you can think of the problem, JSON isn't for presentation. You're storing your data in a schema. You're rendering it into JSON and obscuring  that schema entirely. Going back to the schema from JSON is going to require you to create a translation layer as complex as the whole database itself. Instead,


make the queries on the underlying data in your application. Make the JSON in the application when you need to send it to the client. And, make the updates on the underlying schema like you would normally do.
or, use something like PostgREST which defaults to sending out JSON when you write the base query using its API. This method does not collapse your schema. Then it provides you the ability to write against that as well (through other API calls).


Shy of all of that, to proceed start by creating your stored proc, and then set it as a trigger..

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON test_view
    FOR EACH ROW
    EXECUTE PROCEDURE test_trigger_function();