Sync Engines Are the Future(instantdb.com)

    CREATE JSON RELATIONAL DUALITY VIEW dept_w_employees_dv AS
    SELECT JSON {'_id'            : d.deptno,
                 'departmentName' : d.dname,
                 'location'       : d.loc,
                 'employees'      :
                     [ SELECT JSON {'employeeNumber' :e.empno,
                                    'name' : e.ename}
                       FROM employee e
                       WHERE e.deptno = d.deptno ]
                }
    FROM department d WITH UPDATE INSERT DELETE;

  SELECT g.*, 
         COALESCE(t.todos, '[]'::json) as todos
  FROM goals g
  LEFT JOIN LATERAL (
    SELECT json_agg(t.*) as todos
    FROM todos t
    WHERE t.goal_id = g.id
  ) t ON true