--rational data
select
	film.film_id,
	film.title,
	film.description,
	film.release_year,
	film.rating,
	language.name,
	actor.first_name,
	actor.last_name
from
	film
join language on
	film.language_id = language.language_id
join film_actor on
	film_actor.film_id = film.film_id
join actor on
	actor.actor_id = film_actor.actor_id
where film.title = 'Academy Dinosaur'
;

--row_to_json
--con: field as "fn" which is not meaningful
select
	row_to_json(row(film.film_id,
	film.title,
	film.description,
	film.release_year,
	film.rating,
	language.name,
	actor.first_name,
	actor.last_name))
from
	film
join language on
	film.language_id = language.language_id
join film_actor on
	film_actor.film_id = film.film_id
join actor on
	actor.actor_id = film_actor.actor_id
where film.title = 'Academy Dinosaur';

--row_to_json with "named rowset", to generate meaningful field name
select row_to_json(rowset,true) from 
(select
	film.film_id,
	film.title,
	film.description,
	film.release_year,
	film.rating,
	language.name as language,
	actor.first_name,
	actor.last_name
from
	film
join language on
	film.language_id = language.language_id
join film_actor on
	film_actor.film_id = film.film_id
join actor on
	actor.actor_id = film_actor.actor_id
where film.title = 'Academy Dinosaur'
) rowset;

--use json_agg to build sub arrary which is more compact
select row_to_json(rowset,true) movies_list from
(
	select film.film_id, title, description, release_year, language.name as language, rating,
	(select json_agg(row_to_json(cast_list.*,true)) from
		(
		select actor.first_name, actor.last_name from
		film_actor join actor on actor.actor_id = film_actor.actor_id
		where film_actor.film_id = film.film_id
		) cast_list
	) as film_cast
	from film join language on film.language_id = language.language_id 
	where film.title = 'Academy Dinosaur'
order by film.film_id) rowset;


--build sub-object thru sub-query
select json_strip_nulls(row_to_json(staff_list.*)) from 
(select staff.first_name, staff.last_name,staff.email,staff.username,
	(select json_strip_nulls(row_to_json(address_value.*,true)) from
		(select address.address, address.address2, address.district,city.city,country.country 
			from address join city on city.city_id = address.city_id 
			join country on country.country_id = city.country_id 
			where staff.address_id =address.address_id 
		) address_value
	) as address
from staff
) staff_list;

--build sub-object thru json_build_object()
select json_strip_nulls(row_to_json(staff_list.*,true)) from
(select
	staff.first_name,
	staff.last_name,
	staff.email,
	staff.username,
	json_strip_nulls(json_build_object(
	 'address', address.address,
	 'address2', address.address2,
	 'district', address.district,
	 'city', city.city,
	 'country', country.country
	 )) as address
from staff
join address on staff.address_id = address.address_id
join city on city.city_id = address.city_id
join country on country.country_id = city.country_id) staff_list;

--build sub-array thru json_build_array()
select json_strip_nulls(row_to_json(store_list.*,true)) from
(select
	store.store_id,
	json_strip_nulls(json_build_object(
	 'address', address.address,
	 'address2', address.address2,
	 'district', address.district,
	 'city', city.city,
	 'country', country.country
	 )) as address,json_build_array(country,city) as location_tag
from store
join address on store.address_id = address.address_id
join city on city.city_id = address.city_id
join country on country.country_id = city.country_id) store_list;