JSONB Columns in the domain tables

[M-Hoogie]

The current examples for JSONB (yum and apt) are outside the domain of the DVD rental example. It might be more beneficial to add JSONB columns to either the existing view or to create a new (materialized) view that has these json columns. An example below.

-- actor_info view
SELECT a.actor_id,
       a.first_name,
       a.last_name,
       jsonb_object_agg(c.name, (SELECT array_agg(f.title) AS array_agg
                                             FROM film f
                                                      JOIN film_category fc_1 ON f.film_id = fc_1.film_id
                                                      JOIN film_actor fa_1 ON f.film_id = fa_1.film_id
                                             WHERE fc_1.category_id = c.category_id
                                               AND fa_1.actor_id = a.actor_id
                                             GROUP BY fa_1.actor_id)) AS film_info
FROM actor a
         LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
         LEFT JOIN film_category fc ON fa.film_id = fc.film_id
         LEFT JOIN category c ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name;

This would enable users to get more familiar with different jsonb operator when filtering on the view. For instance this simple example below

SELECT actor_id, first_name, last_name, film_info
FROM actor_info
WHERE film_info -> 'Games' ? 'FEATHERS METAL'